How to optimize a View if no componenet is taking much time but over all view is taking too long

Question

Post reply

How to optimize a View if no componenet is taking much time but over all view is taking too long

rashid nizam

SSCommitted

Points: 1605
More actions
June 4, 2008 at 1:09 am

#380910

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

1) Defragment the indexes.

2) INCLUDE the SELECTed columns in the nonclustured indexes.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

rashid nizam SSCommitted Points: 1605 More actions · Answer 4

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 5

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

rashid nizam SSCommitted Points: 1605 More actions · Answer 6

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