April 15, 2016 at 10:16 am
Hi all,
I have attached a query that is taking some 1 min to return 30 records. Earlier it was taking even more time, but after I introduced CTE, its performance increased from earlier 2-3 mins to now 1 min. This execution time is still not good and I need to optimize it further. There are no index or execution plan issues with this query.
I put this query here to see if any of you can suggest some rewriting or anything else to optimize this query.
Any help will be highly appreciated.
Regards
April 15, 2016 at 10:24 am
can you post the actual execution plan please.
quick scan thro your code....
case when
1 = 1
and 1 = 0
and 0 = 0
and wo.status in (0, 1, 31, 41, 62, 54, 62, 5, 36)
and (wo.sequence - 1) = 0
and j.job_seeker_type != 2
not sure what this means????
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 15, 2016 at 10:28 am
This is generated output. Actual script is like
case when
:condition = 1
and :flag = 0
and :count = 0
Don't have execution plan due to some limitations.
April 15, 2016 at 10:47 am
T.Ashish (4/15/2016)
This is generated output. Actual script is likecase when
:condition = 1
and :flag = 0
and :count = 0
Don't have execution plan due to some limitations.
You're seriously asking people to look at that long query, with no query plan, and offer guesses about ways to improve the query?
Even when you have admitted that the query is system-generated?
If it's system generated, presumably you would be unable to change it?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 15, 2016 at 11:01 am
The file for that single query is 6.5KILOBYTES!! I have spent a dozen or more hours many times the past trying to decipher and fix stuff like that at clients.
Sorry, but I think this request goes way beyond what you should expect from a free forum.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 15, 2016 at 11:05 am
T.Ashish (4/15/2016)
There are no index or execution plan issues with this query.
How have you decided your statement above is correct?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 15, 2016 at 11:12 am
What the heck is this doing in the JOIN criteria:
0 = 1
and pbu.bu_id is not null
I gave up after that. I'm not paid enough.
Instead of NOLOCK on umpti-gazillion tables, why not just SET READUNCOMMITTED? It'll do the same thing (you'll get just as many duplicate and missing rows in other words).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 15, 2016 at 11:35 am
T.Ashish (4/15/2016)
Don't have execution plan due to some limitations.
Then how do you know that there are no "execution plan issues"?
April 15, 2016 at 11:40 am
Just one thing I saw:
case
when 1 = 1 and -- Always true
1 = 0 and -- Always false
0 = 0 and -- Always true
wo.status in (0, 1, 31, 41, 62, 54, 62, 5, 36) and -- Depends on data
(wo.sequence - 1) = 0 and -- Depends on data
j.job_seeker_type != 2 -- depends on data
then '-' -- will never be returned due to 1 = 0 above
else cn.name -- will always be returned due to 1 = 0 above
end as supplier_name
cn.name as supplier_name -- equivalent to above CASE
April 15, 2016 at 11:48 am
Lynn Pettis (4/15/2016)
Just one thing I saw:
case
when 1 = 1 and -- Always true
1 = 0 and -- Always false
0 = 0 and -- Always true
wo.status in (0, 1, 31, 41, 62, 54, 62, 5, 36) and -- Depends on data
(wo.sequence - 1) = 0 and -- Depends on data
j.job_seeker_type != 2 -- depends on data
then '-' -- will never be returned due to 1 = 0 above
else cn.name -- will always be returned due to 1 = 0 above
end as supplier_name
cn.name as supplier_name -- equivalent to above CASE
Just last month I made a first onsite visit with a new client for a performance review and one of my major findings was that a very similar "template-driven-query-system" was causing them extreme pain and it simply must be eliminated.
Once more I get to relay one of my most important Guruism's: Anything that allows developers to slap together code more quickly is inversely proportional to the performance you will get from said code. :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 15, 2016 at 12:07 pm
With the liberal use of NOLOCK, it is quite likely that this query will return incorrect results. Make sure the query does what it should do first, then worry about performance.
Now perhaps you value speed over correctness. In that case, try this rewrite:
SELECT 42 AS TheAnswer;
(Probably just as incorrect as your current query but a lot faster!)
Oh, and if you wanted serious help, then give us what we need to help you. Simplify the nonsense out of the query before posting. Test the simplified query as well, who knows it might help. Definitely include the actual execution plan. "Don't have execution plan due to some limitations" does not cut it - in that case the only honest answer we can give you is "Don't have a clue how to optimize this due to some limitations".
This is a forum for help. Unpaid help. Nobody here is paid to help you. We do so because we like to educate people, but you have to put in an effort.
Perhaps that is not something you want to do. Perhaps you just want someone to tune your query for you. Nothing wrong with that - but that is called consultancy, starts with negotiotions and a contract, and ends with an invoice. The good thing about that is that, as part of the contract, you can have the consultant sign an NDA, which might make it easier to share details. The bad thing is that you hire just a single consultant, and you cannot judge whether (s)he is good enough - to judge that, you'd have to be better. Over here, many people look at your problem. But you still have to put in most of the work. TANSTAAFL.
April 15, 2016 at 7:36 pm
T.Ashish (4/15/2016)
This execution time is still not good and I need to optimize it further. There are no index or execution plan issues with this query.
I'm thinking that's a serious contradiction. 😉
If you want performance help, you need to provide performance related information. Please read and heed the article and the second link in my signature line under "Helpful Links".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply