July 25, 2012 at 10:28 pm
How do I optimize a query with several nested joins? An index scan has the highest cost in the execution plan and a related nested join has the second highest cost. The query includes Case statements -- but the execution plan doesn't report these as an issue. I see no obvious way to improve the query.
How do you know when a query cannot be further optimized?
When does it make sense to redesign tables in a database and there are stored procedures that are written for the current design?
July 26, 2012 at 4:08 am
pls check any inner query take more time that time you will use temporary tables.
and also use with(nolock) option in that query.
July 26, 2012 at 5:55 am
subbareddy542 (7/26/2012)
and also use with(nolock) option in that query.
Recommending a bad idea with no discussion of the side effects?
See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 26, 2012 at 5:58 am
Post the query so people can have a look at it:-)
July 26, 2012 at 6:06 am
Golfer22 (7/25/2012)
How do I optimize a query with several nested joins? An index scan has the highest cost in the execution plan and a related nested join has the second highest cost. The query includes Case statements -- but the execution plan doesn't report these as an issue. I see no obvious way to improve the query.How do you know when a query cannot be further optimized?
When does it make sense to redesign tables in a database and there are stored procedures that are written for the current design?
Post the execution plan as a .sqlplan file attachment so folks can have a look. CASE expressions in the output of a query rarely have any significant impact on performance; elsewhere in a query the impact can be very significant.
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
July 26, 2012 at 6:07 am
Golfer22 (7/25/2012)
How do you know when a query cannot be further optimized?
That's very hard. When you can find no way to improve it, then you could say that, but it's possible that someone else may know a trick you don't.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 26, 2012 at 10:18 pm
Thanks for the responses.
I have a question about this response:
"pls check any inner query take more time that time you will use temporary tables."
Can you rephrase this? Could temporary tables help? Or table-valued variables?
July 27, 2012 at 12:26 am
Golfer22 (7/26/2012)
Could temporary tables help? Or table-valued variables?
If the query is relatively simple (small number of tables), probably not.
If you post the query, it would help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply