Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unexpected View Performance


Unexpected View Performance

Author
Message
Thom_Bolin
Thom_Bolin
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 291
Comments posted to this topic are about the item Unexpected View Performance

Thom Bolin - MCITP SQLServer 2005
NTXDBA Blog[url=http://ntxdba.blogspot.com/][/url]
sebastocrator
sebastocrator
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 30
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

The normal form of a database is inversely proportional to that of the designer
(This message was generated with 100% recycled electrons)
pandeharsh
pandeharsh
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 338
Hi,

Can you draw the conclusion in other words ?

Regards
Kumar Harsh

Unsure
Dan Guzman-481633
Dan Guzman-481633
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 394
"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 agents2009
WHERE accountnumber = 'QOT039365'
UNION ALL
SELECT accountnumber FROM agents
WHERE accountnumber = 'QOT039365';
Brian Brenner-395976
Brian Brenner-395976
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 117
Hi All

The 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 table

if 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
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7300 Visits: 8404
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!! :-)

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
thisisfutile
thisisfutile
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 978
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
Thom_Bolin
Thom_Bolin
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 291
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 ALL
SELECT accountnumber FROM agents WHERE accountnumber = ''QOT039365'

Hope this help clears up the question.

Thom Bolin - MCITP SQLServer 2005
NTXDBA Blog[url=http://ntxdba.blogspot.com/][/url]
Hardy21
Hardy21
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1314 Visits: 1399
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.

Thanks
SQLCharger
SQLCharger
SSC Veteran
SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)

Group: General Forum Members
Points: 200 Visits: 1403
Thanks for sharing your observation.
Now we know that 'near-enough' may be a long way from 'identical' when it comes to datatypes:-)

Cheers,

JohnA

MCM: SQL2008
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search