web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Parallel SQL queries o...
Finance | Project Operations, Human Resources, ...
Unanswered

Parallel SQL queries on same table

(0) ShareShare
ReportReport
Posted on by CRSW 625

Hi all

We have some code to export data executing SQL Statements. In the code we start about 60 threads which are executing the same statements. Is it somehow possible to execute the statements on DB with getting a big delay? Currently the execution takes about 18 hours.

In the beginning we loop through InventTrans (joining inventdim and inventtable) and than we do some calculation. The loop is restricted by a daterange of about 1 week.

BR Chris

I have the same question (0)
  • Rudi Hansen Profile Picture
    Rudi Hansen 4,075 on at

    It would seem to me that you are perhaps missing an index, if it takes more than 18 hours to export one weeks of records from InventTrans.

    So perhaps that is where you should start?

  • CRSW Profile Picture
    CRSW 625 on at

    Maybe yes but I have no clue which index would help.

    The query looks like this:

    while select ItemId from inventTrans

           group by ItemId

           order by ItemId

           where (inventTrans.StatusReceipt    == StatusReceipt::Purchased ||

                  inventTrans.StatusReceipt    == StatusReceipt::Received  ||

                  inventTrans.StatusIssue      == StatusIssue::Deducted    ||

                  inventTrans.StatusIssue      == StatusIssue::Sold)

              && (inventTrans.DatePhysical && inventTrans.DatePhysical <= reviewDate)

       exists join inventDim

           where inventDim.inventDimId         == inventTrans.inventDimId

              && inventDim.InventSiteId        == _inventSiteId

       exists join inventTable

           where inventTable.ItemId            == inventTrans.ItemId

              && inventTable.ItemType          != ItemType::Service

    and there is an Index for idx1(ItemId, StatusReceipt, StatusIssue) as well an Index for  datePhysical

  • CRSW Profile Picture
    CRSW 625 on at

    Maybe yes but I have no clue which index would help.

    The query looks like this:

    while select ItemId from inventTrans

           group by ItemId

           order by ItemId

           where (inventTrans.StatusReceipt    == StatusReceipt::Purchased ||

                  inventTrans.StatusReceipt    == StatusReceipt::Received  ||

                  inventTrans.StatusIssue      == StatusIssue::Deducted    ||

                  inventTrans.StatusIssue      == StatusIssue::Sold)

              && (inventTrans.DatePhysical && inventTrans.DatePhysical <= reviewDate)

       exists join inventDim

           where inventDim.inventDimId         == inventTrans.inventDimId

              && inventDim.InventSiteId        == _inventSiteId

       exists join inventTable

           where inventTable.ItemId            == inventTrans.ItemId

              && inventTable.ItemType          != ItemType::Service

    and there is an Index for idx1(ItemId, StatusReceipt, StatusIssue) as well an Index for  datePhysical

  • Rudi Hansen Profile Picture
    Rudi Hansen 4,075 on at

    Yes well that does seem like that should not take that long, but it does kind of depend on your data, so hard for me to know what the problem could be.

    I have tried to convert your statement to the SQL that should be running, so perhaps running that on the SQL server can give you a hint.

    You will have to change the values I use in DataPhysical and InventSiteId

    This statement runs on about 6 seconds on the DB i tested it on, but it only has about 1.3 mio records in InventTrans, so its rather small.

    SELECT INVENTTRANS.ITEMID,DATEPHYSICAL FROM INVENTTRANS
	join INVENTDIM
		ON INVENTDIM.INVENTDIMID = INVENTTRANS.INVENTDIMID
		JOIN INVENTTABLE
			ON INVENTTABLE.ITEMID = INVENTTRANS.ITEMID
	WHERE INVENTTRANS.STATUSRECEIPT = 1 
	   OR INVENTTRANS.STATUSRECEIPT = 2
	   OR INVENTTRANS.STATUSISSUE   = 1
	   OR INVENTTRANS.STATUSISSUE   = 2
	  AND INVENTTRANS.DATEPHYSICAL <= '2020-01-01'
	  AND INVENTTABLE.ITEMTYPE     != 2
	  AND INVENTDIM.INVENTSITEID    = 'DK'

  • CRSW Profile Picture
    CRSW 625 on at

    oh I think found the problem. I didn´t realize that inside the loop of the query there are calls to class which calculates some data. This calculation class loops threw inventtrans (about 70mio entries in our DB) multiple times as well to calculate sums and fills records into a tmp Table.

    so I have to increase the speed of the calculation class to get it running faster.

    No clou how to get this done but at least I know why it is this slow ;-)

  • Rudi Hansen Profile Picture
    Rudi Hansen 4,075 on at

    Ok so the good old, I found the problem while looking at it again

    Do check if the calculation class is perhaps calculating values for the same record more than one time, had that problem one time, where just caching the calculation results helped a lot with the speed.

    Or perhaps the calculation should be run after the actual query has been run, so it runs through less records.

  • CRSW Profile Picture
    CRSW 625 on at

    Maybe there is an idea how I can speedup the following sql statements?

    Statement 1 somtimes takes long time and inside the loop the second one is called (which sometimes takes long as well)

    1Statement:

    while select ItemId from inventTrans
        group by ItemId
        order by ItemId
        where (inventTrans.StatusReceipt    == StatusReceipt::Purchased ||
               inventTrans.StatusReceipt    == StatusReceipt::Received  ||
               inventTrans.StatusIssue      == StatusIssue::Deducted    ||
               inventTrans.StatusIssue      == StatusIssue::Sold)
           && (inventTrans.DatePhysical && inventTrans.DatePhysical <= reviewDate)
    exists join inventDim
        where inventDim.inventDimId         == inventTrans.inventDimId
           && inventDim.InventSiteId        == _inventSiteId
    exists join inventTable
        where inventTable.ItemId            == inventTrans.ItemId
           && inventTable.ItemType          != ItemType::Service
    {
    ...
    ...
    ...

    Inside the loop of the first statement the is a method-call in a different class which has the following 2. Statement included (which takes long as well

     while select sum(Qty) from inventTrans
        where inventTrans.ItemId            == itemId
           && (inventTrans.StatusReceipt    == StatusReceipt::Purchased ||
               inventTrans.StatusReceipt    == StatusReceipt::Received  ||
               inventTrans.StatusIssue      == StatusIssue::Deducted    ||
               inventTrans.StatusIssue      == StatusIssue::Sold)
           && (inventTrans.DatePhysical && inventTrans.DatePhysical <= reviewDate)
    join InventBatchId, InventLocationId from inventDim
        group by InventBatchId, InventLocationId
        order by InventBatchId, InventLocationId
        where inventDim.inventDimId         == inventTrans.inventDimId
           && inventDim.InventSiteId        == inventSiteId
    {
        if (inventTrans.Qty)
        {
            ...
        }
    }

  • Rudi Hansen Profile Picture
    Rudi Hansen 4,075 on at

    Well i tested this again on my database, and it's really not that slow for me.

    The longest time the second query took was 142ms, so not really that bad.

    But again it is really possible that my database is simply not big enough for it to be a problem.

    From what you said to far your database is about 50 times bigger than mine, but still I can't explain why the second query is slow.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 660 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 549 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 307 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans