April 22, 2010 at 11:31 am
gregory.anderson (4/22/2010)
How can the elapsed time be shorter than the CPU time?
Having multiple CPU and shared processing (as a short answer).
Regarding your prev. post: I'll have a look at it after dinner (7:30pm over here...)
April 22, 2010 at 12:28 pm
Ok, let's try the following:
- after loading the PWDPermitConversionData2 table, add a clustered index on PWDPermitConversionDataID.
- after inserting into PWDPermitConversionErrors2, add a clusterd index on PWDPermitConversionDataID,RecordIndicator
- after inserting into PermitsInserted, add a clusterd index on PWDPermitConversionDataID
Repost the statistics as well as the execution plan for insert_succesful and update. I don't think we should look at insert_failure for the moment since this step only takes 3sec...
April 22, 2010 at 12:33 pm
lmu92 (4/22/2010)
Ok, let's try the following:- after loading the PWDPermitConversionData2 table, add a clustered index on PWDPermitConversionDataID.
- after inserting into PWDPermitConversionErrors2, add a clusterd index on PWDPermitConversionDataID,RecordIndicator
- after inserting into PermitsInserted, add a clusterd index on PWDPermitConversionDataID
Repost the statistics as well as the execution plan for insert_succesful and update. I don't think we should look at insert_failure for the moment since this step only takes 3sec...
On which table for the 3rd item? You already said to add the PWDPermitConversionDataID clustered index on the PWDPermitConversionData2 table in the 1st suggestion....
Edit: Oh, nevermind, you meant to add it on the PermitsInserted table...I'll do those now.
April 22, 2010 at 2:31 pm
Ran in 1:55 on first try.
Here are the timings:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'PWDPermitType'. Scan count 1, logical reads 1, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'PWDPermitType'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'PWDPermitType'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'PWDPermitType'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'PWDPermitStatus'. Scan count 1, logical reads 1, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'PWDPermitDeviceType'. Scan count 1, logical reads 1, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'PWDPermitDeviceType'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'PWDPermitDeviceType'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'sysschobjs'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 46 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 16 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysidxstats'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'Worktable'. Scan count 2, logical reads 1323444, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'spt_values'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PWDPermitConversionData2'. Scan count 3, logical reads 50987, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PermitsInserted'. Scan count 0, logical reads 1079, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PWDPermitConversionErrors2'. Scan count 0, logical reads 1079, physical reads 0, read-ahead reads 0, lob logical reads 1073, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 5829 ms, elapsed time = 3100 ms.
(1073 row(s) affected)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 3 ms.
Table 'PWDPermitConversionErrors2'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 5 ms.
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 11 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'PermitsInserted'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 52 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'PWDPermitConversionErrors2'. Scan count 3, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 1323444, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'spt_values'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PWDPermitConversionData2'. Scan count 3, logical reads 50987, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PermitsInserted'. Scan count 0, logical reads 2823987, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PWDPermit2'. Scan count 0, logical reads 978755, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 33578 ms, elapsed time = 21297 ms.
(966672 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'PWDPermitConversionData2'. Scan count 562995, logical reads 3595027, physical reads 0, read-ahead reads 0, lob logical reads 12385890, lob physical reads 75482, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 1665915, physical reads 3149, read-ahead reads 0, lob logical reads 1073, lob physical reads 9, lob read-ahead reads 0.
Table 'PermitsInserted'. Scan count 1, logical reads 4867, physical reads 0, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 32234 ms, elapsed time = 79509 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
(562995 row(s) affected)
Attached are the two execution plans.
April 22, 2010 at 2:46 pm
April 23, 2010 at 7:36 am
lmu92 (4/22/2010)
Would you add the update sqlplan please? 😉
Woops, sorry about that...
April 24, 2010 at 4:52 pm
Hmmmm, .....
It seems like there is not much more room for speeding the current query up...
The question is: is it still required to store the successful import into PermitsInserted?
Basically, we know which permits failed. So all the other ones would have passed. If not, we'd have that 'Insert failed' message.
Then we would only update the error status for rows where at least one set failed using the current logic.
How long does the sproc take if you take out the
Output
Inserted.PWDPermitConversionDataID,
Inserted.RecordIndicator,
1,
Null
Into PermitsInserted
from the "Insert the permits inside a try/catch block"?
April 26, 2010 at 7:30 am
The question is: is it still required to store the successful import into PermitsInserted?
It is still required because not ALL of the records are getting converted. There were some where clauses where the status cannot be a 6, and also when permit type = 'D', it must be no more than 1 year old, etc.
Lutz, I can't thank you enough for the time, effort and help you have given me with this 1 stored procedure. I don't think I've ever seen a thread with this many replies on this site. You've definitely shown me some tips and methods/styles that will greatly help me in my future work, set-based queries, CTE's, the cross apply. Is there some way I can nominate you for an MVP or something like that? Seriously, THANK YOU!
I think our only hope of speeding this up even a little bit more is going to be just by running this from our production server when I go live with the project next month. Release date is May 26th, so the day after I should have some more timings of how long the conversion took in the real environment. That server is much beefier so maybe it will come down to 1:15 or so...
April 26, 2010 at 10:14 am
gregory.anderson (4/26/2010)
The question is: is it still required to store the successful import into PermitsInserted?
It is still required because not ALL of the records are getting converted. There were some where clauses where the status cannot be a 6, and also when permit type = 'D', it must be no more than 1 year old, etc.
Lutz, I can't thank you enough for the time, effort and help you have given me with this 1 stored procedure. I don't think I've ever seen a thread with this many replies on this site. You've definitely shown me some tips and methods/styles that will greatly help me in my future work, set-based queries, CTE's, the cross apply. Is there some way I can nominate you for an MVP or something like that? Seriously, THANK YOU!
I think our only hope of speeding this up even a little bit more is going to be just by running this from our production server when I go live with the project next month. Release date is May 26th, so the day after I should have some more timings of how long the conversion took in the real environment. That server is much beefier so maybe it will come down to 1:15 or so...
My pleasure. 😀
Acually, YOU did a great job! I hope you don't consider a c.u.r.s.o.r. as a good performing solution. Always remember: think about what you want to do with the set, not with a single row. 😉
Regarding the MVP: thanx for the flowers :blush:
But there is (and should be) a lot more to it than "just" help to tune a single proc. Even though it took more than a few posts. And compared to the MVP's hanging around here as well as some other non-MVP-folks providing excellent help to the community I still have to learn. A lot.
Please post back if you know how long the proc did on the production server. Would be interesting to know...
June 6, 2010 at 4:28 pm
Hi Gregory,
any news regarding the performance on the production hardware?
June 6, 2010 at 9:05 pm
WayneS (4/15/2010)
lmu92 (4/15/2010)
blah blah blah.... Right, Wayne?uh, sure, jeff :w00t:
Heh... now what the hell do you mean by that? 😉
--Jeff Moden
June 7, 2010 at 6:17 am
Funny you just asked, Lutz. Had the conversion on Saturday. Ran in 1:48. Then entire process KILLED my previous times since I was running on some really beefy servers. What I anticipated to take 4-5 hours, ran in 2:05.
Thanks you you and everyone that helped. What would have taken around 30 hours I was able to accomplish in around 2 hours. Excellent work!
:-):-):-)
June 7, 2010 at 11:58 am
gregory.anderson (6/7/2010)
Funny you just asked, Lutz. Had the conversion on Saturday. Ran in 1:48. Then entire process KILLED my previous times since I was running on some really beefy servers. What I anticipated to take 4-5 hours, ran in 2:05.Thanks you you and everyone that helped. What would have taken around 30 hours I was able to accomplish in around 2 hours. Excellent work!
:-):-):-)
Very Strange...
IIRC, we've reduced it to less than two minutes. What happened that it finally needed two hours??
June 7, 2010 at 12:00 pm
lmu92 (6/7/2010)
gregory.anderson (6/7/2010)
Funny you just asked, Lutz. Had the conversion on Saturday. Ran in 1:48. Then entire process KILLED my previous times since I was running on some really beefy servers. What I anticipated to take 4-5 hours, ran in 2:05.Thanks you you and everyone that helped. What would have taken around 30 hours I was able to accomplish in around 2 hours. Excellent work!
:-):-):-)
Very Strange...
IIRC, we've reduced it to less than two minutes. What happened that it finally needed two hours??
Lot's of other stuff needed to happen besides just the conversion of the data...
June 7, 2010 at 12:12 pm
Thank you for clarification, Gregory! (I shuddered for a moment or two... :hehe: )
Glad it did save "a minute or two" 😀
It was a real pleasure working with you. Come back here whenever you think we might be of any help!!
Viewing 15 posts - 91 through 105 (of 112 total)
You must be logged in to reply to this topic. Login to reply