SQLServerCentral / Article Discussions / Discuss content posted by Thom / Article Discussions by Author / Unexpected View Performance / Latest Posts

RE: Unexpected View Performance

mishaluba — Wed, 12 Jan 2011 12:43:42 GMT

I found this absolutely fascinating. Little things like this can sometimes cost days of extra work. Thank you for sharing. I am pretty sure it will save me some headaches down the road.

RE: Unexpected View Performance

SQLCharger — Wed, 12 Jan 2011 05:43:12 GMT

Thanks for sharing your observation.Now we know that 'near-enough' may be a long way from 'identical' when it comes to datatypes:-)

RE: Unexpected View Performance

Hardy21 — Tue, 11 Jan 2011 12:06:18 GMT

Nice article. This is a good example of just a single typo or miss in declaring the column data type/length can harm your performance and you need to spend a week to resolve the issue.

RE: Unexpected View Performance

Thom_Bolin — Tue, 11 Jan 2011 08:30:47 GMT

The statement executed outside of the view is the one below, filter added to both of the select statements.SELECT accountnumber FROM agents2009 WHERE accountnumber = ''QOT039365'UNION ALLSELECT accountnumber FROM agents WHERE accountnumber = ''QOT039365'Hope this help clears up the question.

RE: Unexpected View Performance

thisisfutile — Tue, 11 Jan 2011 08:02:20 GMT

Thanks for posting this. I found it very insightful and relieving because I thought I was the only person that missed things like this. It took you a long time to figure this one out (it would have taken me longer) and it's so close to the surface I bet you even gave it a thought in the beginning. I can almost see the process..."Hmmm...it's gotta be an index. Perhaps this Varchar(9) and Varchar(10) thing would be an issue....NAH, you can't get any closer to matching datatypes than that!"At least, that's how my brain would have seen it. Then, it would have been the absolute LAST thing I tried, because, "there's NO WAY a varchar(9) and varchar(10) would make a difference".Again, thanks for posting and making your findings clear and concise (nice code and screenshots).Gabe

RE: Unexpected View Performance

TheSQLGuru — Tue, 11 Jan 2011 07:52:14 GMT

There are sooooo many ways using inconsistent datatypes can harm performance! I have been preaching this to clients (and cleaning up messes related to it) for over a decade now!! :-)

RE: Unexpected View Performance

Brian Brenner-395976 — Tue, 11 Jan 2011 07:14:35 GMT

Hi AllThe main reason for degradation in performance here is that the query filter was not supplied in the exact format that index was created in.To be honest this doesnt really have all that much to do with the fact that you are querying data from a view. If you want sql to use an index in its query plan the filter must be supplied in exactly the same format as the column indexed on the tableif the index is built on a column of type smallint and you sent in an int as a filter the plan will use a full table scan instead of an index seek

RE: Unexpected View Performance

Dan Guzman-481633 — Tue, 11 Jan 2011 05:47:22 GMT

"This same statement executed outside of the view uses an Index Seek on both tables."Like you, I get the same plan as the view with a select from a derived table. However, seeks do occur against both tables if separate WHERE clauses are specified with each SELECT of the UNION ALL. So I am guessing that it wasn't really the same statement that Thom ran from SSMS:SELECT accountnumber FROM agents2009WHERE accountnumber = 'QOT039365'UNION ALLSELECT accountnumber FROM agentsWHERE accountnumber = 'QOT039365';

RE: Unexpected View Performance

pandeharsh — Tue, 11 Jan 2011 04:40:45 GMT

Hi,Can you draw the conclusion in other words ?

RE: Unexpected View Performance

sebastocrator — Tue, 11 Jan 2011 00:32:53 GMT

Hello,Thanks for writing this article, in my search for a solution of a performance problem I ran into your post and it has helped me partly so far. I do have a question though:You're saying "This same statement executed outside of the view uses an Index Seek on both tables.", can you show what statement that is? If I try:SELECT accountnumber FROM ( SELECT accountnumber FROM agents2009 UNION ALL SELECT accountnumber FROM agents)vwInline WHERE accountnumber = 'QOT039365' I don't see any difference in the queryplan from replacing the subquery with the view.regards,Sebastiaan

Unexpected View Performance

Thom_Bolin — Mon, 10 Jan 2011 20:11:19 GMT

Comments posted to this topic are about the item [B]Unexpected View Performance[/B]