April 12, 2012 at 7:57 am
In one interview the guy asked me a question regarding query performance as below,
Suppose i have one big query which is taking 20 minutes for execution, how could i reduce the time of query execution.. and i want to execute that query in 20 seconds. what steps will you follow?
In my opinion there must be a wrong join written in it. but still not getting what steps will i have to follow during performance tuning?
please reply if anybody know.
Thanks & Regards,
Pallavi
April 12, 2012 at 8:05 am
That is entirely too vague to give a concrete answer. It is however a good interview question because it should immediately start you asking questions. What is this query? Are there loops? Indexes? Stale statistics? Is this in a proc? If so, could it be parameter sniffing? The interviewer was asking it intentionally vague to see how deep your knowledge of this stuff is.
_______________________________________________________________
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/
April 12, 2012 at 8:25 am
Sean Lange (4/12/2012)
That is entirely too vague to give a concrete answer. It is however a good interview question because it should immediately start you asking questions. What is this query? Are there loops? Indexes? Stale statistics? Is this in a proc? If so, could it be parameter sniffing? The interviewer was asking it intentionally vague to see how deep your knowledge of this stuff is.
And to expand on Sean's comments, the interviewer was looking at how you approached problem solving in general. He (or she) wasn't necessarily looking for a correct answer.
April 12, 2012 at 8:29 am
First step should always be to use explain plan to look for table scans, among other things. That would usually indicate a missing index, a bad join, etc...
You've can add a missing index OR changed the query to use indexes which already exist.
*****
Give a man a fish, and you've given him a meal. Teach a man to fish and he'll ask to borrow your boat.:-P
April 12, 2012 at 9:32 am
pallavi.unde (4/12/2012)
In one interview the guy asked me a question regarding query performance as below,Suppose i have one big query which is taking 20 minutes for execution, how could i reduce the time of query execution.. and i want to execute that query in 20 seconds. what steps will you follow?
In my opinion there must be a wrong join written in it. but still not getting what steps will i have to follow during performance tuning?
please reply if anybody know.
Ooooh, I love this question. I could answer it, at length. It's all about tuning the query. Identifying potential bottlenecks within the structure or flaws within the code that lead to poor performance. You'll have to talk about how to identify these things, where they can be found, how to fix them... Great question! Then, you can even get into discussing the politics of the query, meaning, it returns umpty-million rows so you can only tune it within the limits of the hardware. Now what? You have to talke with the developers & project managers to arrive at a compromise... Oh yes, this is a great test. It's the type of open-ended question I would ask.
"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 12, 2012 at 9:55 am
eric.lyons (4/12/2012)
First step should always be to use explain plan to look for table scans, among other things. That would usually indicate a missing index, a bad join, etc...You've can add a missing index OR changed the query to use indexes which already exist.
Even before looking at an execution plan, I would look at the code itself. How is it written, can it be broken down into smaller steps? Looking at the joins and filter conditions, do the tables have proper indexing to support the query? Do the filter conditions themselves force table scans (non-sargable comparisions)?
You could deduce quite a bit from a code review.
April 12, 2012 at 10:44 pm
Can you please tell me what query execution plan does actually to help us in performance tuning? How it could i use?
Thanks & Regards,
Pallavi
April 12, 2012 at 11:22 pm
pallavi.unde (4/12/2012)
Can you please tell me what query execution plan does actually to help us in performance tuning? How it could i use?
Check out Grant Fritchey's signature block a few posts up. Several links to good resources on that subject there.
April 13, 2012 at 8:36 am
Use display execution plans to check for
1) table scans -- which means missing index, bad sql join, or bad where clause criteria.
2) index scans -- it's using an index, but not as effectively as an index seek.
3) RID lookups -- if indexes have included columns and yet there is an RID lookup, possibly another column could be included to eliminate the RID lookup. (But don't go crazy adding all columns to the index).
A recent example of this was a query which took 19 seconds to run. Checking the plan, it was doing a table scan on 1 table. I determined an index was missing and added it.
The query went from 19 seconds to .010 seconds.
*****
Give a man a fish, and you've given him a meal. Teach a man to fish and he'll ask to borrow your boat.:-P
April 13, 2012 at 10:18 am
pallavi.unde (4/12/2012)
Can you please tell me what query execution plan does actually to help us in performance tuning? How it could i use?
Execution plans show you the decisions made by the query optimizer to execute your query. They let you know where you may have structural or code problems and can direct you towards fixes. All three books in my signature are wound around this.
"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 13, 2012 at 11:55 am
If the request is reduce time of execution from 20 min to 20 sec the only one possibility is checking indexes (missing indexes or it could be right index but with big fragmentation...). So look at the execution plan for this stuffs.
If the request is create best possible query then (in my opinion) is first step look at the code:
look at the joins, and filter conditions in them, is there any function in where cond. is there anything for calculate in where cond.... and so on
After that you can look at execution plan and start tune indexes ...
April 13, 2012 at 11:59 am
jzoran (4/13/2012)
If the request is reduce time of execution from 20 min to 20 sec the only one possibility is checking indexes (missing indexes or it could be right index but with big fragmentation...). So look at the execution plan for this stuffs.If the request is create best possible query then (in my opinion) is first step look at the code:
look at the joins, and filter conditions in them, is there any function in where cond. is there anything for calculate in where cond.... and so on
After that you can look at execution plan and start tune indexes ...
There are SO many other things to consider besides indexes. To blindly say the only one possibility is indexes is way too short sighted. Maybe there is a varbinary(max) column in the query but is not used. No indexing at all but the execution time would cut dramatically. There simply is no magic button for how to make a query faster.
_______________________________________________________________
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/
April 13, 2012 at 12:06 pm
Sean Lange (4/13/2012)
jzoran (4/13/2012)
If the request is reduce time of execution from 20 min to 20 sec the only one possibility is checking indexes (missing indexes or it could be right index but with big fragmentation...). So look at the execution plan for this stuffs.If the request is create best possible query then (in my opinion) is first step look at the code:
look at the joins, and filter conditions in them, is there any function in where cond. is there anything for calculate in where cond.... and so on
After that you can look at execution plan and start tune indexes ...
There are SO many other things to consider besides indexes. To blindly say the only one possibility is indexes is way too short sighted. Maybe there is a varbinary(max) column in the query but is not used. No indexing at all but the execution time would cut dramatically. There simply is no magic button for how to make a query faster.
Oh yeah, I agree with you , it could be a lots of things, but I understand there is an interview question(not a real situation). The answer must be simple and most common option. And I think there is something with index (if request is drastically reduce time of execution).
April 13, 2012 at 12:27 pm
zojan (4/13/2012)
Sean Lange (4/13/2012)
jzoran (4/13/2012)
If the request is reduce time of execution from 20 min to 20 sec the only one possibility is checking indexes (missing indexes or it could be right index but with big fragmentation...). So look at the execution plan for this stuffs.If the request is create best possible query then (in my opinion) is first step look at the code:
look at the joins, and filter conditions in them, is there any function in where cond. is there anything for calculate in where cond.... and so on
After that you can look at execution plan and start tune indexes ...
There are SO many other things to consider besides indexes. To blindly say the only one possibility is indexes is way too short sighted. Maybe there is a varbinary(max) column in the query but is not used. No indexing at all but the execution time would cut dramatically. There simply is no magic button for how to make a query faster.
Oh yeah, I agree with you , it could be a lots of things, but I understand there is an interview question(not a real situation). The answer must be simple and most common option. And I think there is something with index (if request is drastically reduce time of execution).
Of course, the interviewer is trying to figure out how you would deal with a real situation. Honestly in an interview this should not generate an answer from the interviewee. It should generate a few questions. If I were asking that question and the response I got was that it has to be indexes, my response would be "No the indexes are all properly covered and defragmented". And then I would sit quietly looking at the interviewee and wait for an answer...
_______________________________________________________________
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/
April 13, 2012 at 12:44 pm
Sean Lange (4/13/2012)
zojan (4/13/2012)
Sean Lange (4/13/2012)
jzoran (4/13/2012)
If the request is reduce time of execution from 20 min to 20 sec the only one possibility is checking indexes (missing indexes or it could be right index but with big fragmentation...). So look at the execution plan for this stuffs.If the request is create best possible query then (in my opinion) is first step look at the code:
look at the joins, and filter conditions in them, is there any function in where cond. is there anything for calculate in where cond.... and so on
After that you can look at execution plan and start tune indexes ...
There are SO many other things to consider besides indexes. To blindly say the only one possibility is indexes is way too short sighted. Maybe there is a varbinary(max) column in the query but is not used. No indexing at all but the execution time would cut dramatically. There simply is no magic button for how to make a query faster.
Oh yeah, I agree with you , it could be a lots of things, but I understand there is an interview question(not a real situation). The answer must be simple and most common option. And I think there is something with index (if request is drastically reduce time of execution).
Of course, the interviewer is trying to figure out how you would deal with a real situation. Honestly in an interview this should not generate an answer from the interviewee. It should generate a few questions. If I were asking that question and the response I got was that it has to be indexes, my response would be "No the indexes are all properly covered and defragmented". And then I would sit quietly looking at the interviewee and wait for an answer...
I helped a developer improve the performance of stored procedure at a previous employer. The biggest improvements were in the code. Went fron several minutes to 35 seconds. Added an appropriate index at the end, only cut processing by another 3 seconds.
Probably should have looked at the execution plan then to see if anything else could be done, but the developer was happy with what I helped him get.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply