May 31, 2016 at 10:38 am
TheSQLGuru (5/31/2016)
Sean Lange (5/31/2016)
Maybe a bit off topic but nobody else mentioned all those NOLOCK hints. That hint may make your queries a little bit faster but they do come with some very serious ramifications. Might be worth reading up about that hint before you litter your entire database with it. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/url]Kinda silly to point those out for a query that runs for a day, right? What system out there can accept zero DML for that long? :hehe:
Not if the OP doesn't understand that hint and they simply threw it in the ring because they heard it makes thing faster. As I said, it is a bit off topic. That doesn't make it silly. Once the performance is improved to an acceptable level tossing that hint everywhere may cause some issues that are challenging to figure out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 31, 2016 at 1:19 pm
Has anyone asked for an estimated execution plan yet? What about table create scripts (complete with all keys and indexes)? Perhaps there is an unintended cartesian to be found, or some ugly index scans.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 1, 2016 at 1:39 am
TheSQLGuru (5/31/2016)
Has anyone asked for an estimated execution plan yet? What about table create scripts (complete with all keys and indexes)? Perhaps there is an unintended cartesian to be found, or some ugly index scans.
Yes, but only the repeated (and probably most expensive) part of the query. With numerous code smells to address, a plan of the original query is kinda moot.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 3, 2016 at 12:26 am
That's some query. It appears to have two sub-queries which look quite similar, without studying it too far I'm sure that there is some redundancy there that can be removed. But in the very least I would suggest storing the second sub-query into a temporary table, defining an index on the temporary table, and replacing that 'in' with an 'exists' making use of an index.
August 8, 2016 at 5:38 am
Hi ,
I have implemented this logic at client side it helps to improve the performance
Before Logic (Time Taken) 18 Hr to 24 Hrfor 66 Rows
After change in logic (Time Taken) 10 sec66 Rows
August 8, 2016 at 5:49 am
vijay.vesanekar (8/8/2016)
Hi ,I have implemented this logic at client side it helps to improve the performance
Before Logic (Time Taken) 18 Hr to 24 Hrfor 66 Rows
After change in logic (Time Taken) 10 sec66 Rows
That's an excellent improvement, well done!
Can you share with us the modified query?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply