Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Unanswered

Parallel SQL queries on same table

Posted on by 557

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

  • Rudi Hansen Profile Picture
    Rudi Hansen 3,983 on at
    RE: Parallel SQL queries on same table

    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.

  • CRSW Profile Picture
    CRSW 557 on at
    RE: Parallel SQL queries on same table

    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 3,983 on at
    RE: Parallel SQL queries on same table

    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 557 on at
    RE: Parallel SQL queries on same table

    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 3,983 on at
    RE: Parallel SQL queries on same table

    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 557 on at
    RE: Parallel SQL queries on same table

    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 557 on at
    RE: Parallel SQL queries on same table

    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 3,983 on at
    RE: Parallel SQL queries on same table

    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?

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,696 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,490 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans