593
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
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.
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:
Share
Report
All responses (
Answers (
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)
{
...
}
}
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.
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 ;-)
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'
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
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
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?
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156