June 4, 2008 at 1:09 am
Hello every Body,
I have given a task to optimize a view, which is taking too long 30 to 35 seconds to fetch the records, it search view, use for search all.
Strange thing is none of the component is taking too much cost if i see the exceution plan, but the View is getting around 57 columns form more then 12 tables having left outer join and getiing more then 17k records.
I wonder how one can optimize this view to bring data withing 5 to 10 seconds once you cannot identify any particluar compomnent taking much cost.
i cannot re-write the code, as its on prodcution server but the users are screaming for so much wait.
Any help to minimize this time, i have already implemented indexes wherever there are joins involve and there isnt any order by clauses in the query, however query is kinda of co-related query.
Any help would highly be appreacited.
June 4, 2008 at 1:42 am
1) Defragment the indexes.
2) INCLUDE the SELECTed columns in the nonclustured indexes.
June 4, 2008 at 2:30 am
Please post the code of the view, the table and index definitions and the execution plan (in xml formal, zipped and attached to a post)
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
June 4, 2008 at 6:50 am
Any time you're asked to tune a performance problem but told you can't touch the code (unless it's a third party app), you need to push back. More often than not the problem is in the code or the index design. Usually those are the only two places where you can achieve real performance gains.
Can you post the execution plan for the query?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 4, 2008 at 11:26 pm
Thanks for u guys feedback i am attaching the execution plan, code and table and index defination, kindky see these and propsed your solutions.
Looking fwd for ur replies
June 5, 2008 at 6:16 am
Do you normally run the query against the view with no WHERE clause? Because it's returning everything, you're getting nothing but table and index scans. It's rather difficult to drill down on the precise issue using the execution plan because of that.
However, from the query itself, I can several things. First, you're including correlated queries in the SELECT statement. This makes your query into a gigantic cursor. You should be able to retrieve those columns by joining the tables as part of the regular piece of the query. This will work better even if you're joining to the person table over and over again as it appears you'll need to. The IN clauses that you're using over and over again in the code can be rewritten as JOINS and should also get a serious performance boost. Once again, an IN statement used in this manner is basically running the second query over and over again for every row returned in the calling query. This is a major performance bottleneck.
That's probably enough to get started on. There may be other issues with some of the indexes, but they might not be readily apparent until you clean up the other parts of the query.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 6, 2008 at 12:30 am
Thanks fritchy for ur time and support, yes users of applciations are using view as wild earsh..,means no where clause, i just cannot understand why they are doing so, i have already suggested not to allow wild search, rather restrict or they must give some search criteria.
As per query yes co-related query is the bottleneck, i never used this co-related query in my code and dont have much idea, but since this code is written by some 3rd party we are giving support on this application we need to suggest solution.
Your comments are really helpful in this connection. Yes too many IN clauses and the co-related query is doing all the mess, so re-writing code and limited columns is the sloution,but that definitely require some time, coz i cannot even figure out any other solution.
Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply