February 24, 2008 at 8:30 am
Hi,
I have a performance issue that I am trying to troubleshoot. The problematic sql does the following:
SELECT SiteIdx FROM v_Network WHERE SiteIdx In (Select NodeID from v_LatestRelease)
Please note I also had a proper join of the above but for the purposes of troubleshooting am working with the above statement. The thing that is bugging me is that the statement runs at 1:15 seconds. Running
SELECT SiteIdx FROM v_Network takes about 1 second as does the (Select NodeID from v_LatestRelease).
A collegue suggested that SQL is trying to do some view optimizations (combining the views) but recalls there being some hints that would prevent that from happening. Is there a hint that would do that? All other ideas or suggestions much appreciated!
Thanks!
Olja
February 24, 2008 at 9:57 am
I strongly suggest NOT using hints in views !
That may prevent optimal paths when used with other queries !
Rewrite your query to e.g.:
SELECT SiteIdx
FROM v_Network N
WHERE exists (Select 1
from v_LatestRelease V where V.NodeId = N.SiteIdx )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 25, 2008 at 12:31 am
Your colleague may be thinking about the NOEXPAND hint. That's onlty useful if you have indexed views and a version of SQL other than enterprise.
I would not recommend the use of hints, except as a last resort. Run your query and have a look at the execution plan, see what SQL's doing. That should give you some clues as to how to fix it.
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
February 25, 2008 at 1:15 pm
I never understand why it's assummed that a simple query from a view ( which is probably complex ) will work well. Forget your views and expand the query into the actual real tables and queries to see where you can improve the query. as suggested have a good look at the query plan.
It may have been a suggestion of using indexed views, as thes ematerialise the data then your queries will improve , usually.
I can't see what hints you could give your query, however you can hint views with plan guides.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 28, 2008 at 12:05 pm
This has been an very insightful problem to have. Next time we'll keep in mind the performance penalty of combining the views. As it turns out the culprit was a function in a where clause which tested for a column value. Rather than saying StatusID <> 30, a function was used to encapsulate this test: dbo.IsFinalizedStatus(StatusID)=0!
That added over a minute to the whole operation! Changing it back to a simple test solved the problem. More info on this can be found at: http://qa.sqlservercentral.com/articles/T-SQL+Optimization/61809/
Thanks a lot!
Olja
February 28, 2008 at 12:53 pm
Olja (2/28/2008)
This has been an very insightful problem to have. Next time we'll keep in mind the performance penalty of combining the views. As it turns out the culprit was a function in a where clause which tested for a column value. Rather than saying StatusID <> 30, a function was used to encapsulate this test: dbo.IsFinalizedStatus(StatusID)=0!That added over a minute to the whole operation! Changing it back to a simple test solved the problem. More info on this can be found at: http://qa.sqlservercentral.com/articles/T-SQL+Optimization/61809/
Thanks a lot!
Olja
When post here asking for help, you should post the actual code you are having trouble with.
How could you expect any meaningful help when you posted a completely different piece of code?
February 28, 2008 at 4:03 pm
it's not easy to always post code - there is client/employer confidentiality and do you really want to read a couple of hundred lines of code anyway?
Inline functions are a killer and can sometimes be difficult to track down - I'd personally like to shoot some of the people who have posted implementations of lookups using functions instead of a table or implementing constants using functions who turn set based queries into REBAR ! And I wish I'd been paid a $ or £ for each I've found as I'd be quite a bit richer.
But hey we've given some help, without the code, and that's what is important.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 28, 2008 at 5:20 pm
colin Leversuch-Roberts (2/25/2008)
I never understand why it's assummed that a simple query from a view ( which is probably complex ) will work well. Forget your views and expand the query into the actual real tables and queries to see where you can improve the query. as suggested have a good look at the query plan.
Well said, Colin... and good advice to boot!
--Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply