Unexpected View Performance

  • Thom_Bolin

    SSC Veteran

    Points: 242

    Comments posted to this topic are about the item Unexpected View Performance

    Thom Bolin - MCITP SQLServer 2005
    NTXDBA Blog[/url]

  • sebastocrator

    SSC Rookie

    Points: 32

    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

    Ten Centuries

    Points: 1046

    Hi,

    Can you draw the conclusion in other words ?

    [font="Verdana"]Regards
    Kumar Harsh[/font]

    :unsure:

  • Dan Guzman

    Default port

    Points: 1469

    "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

    SSC Rookie

    Points: 36

    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

    SSC Guru

    Points: 134017

    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 on googles mail service

  • thisisfutile

    Hall of Fame

    Points: 3486

    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

    SSC Veteran

    Points: 242

    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]

  • Hardy21

    SSCrazy Eights

    Points: 9708

    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

    SSCommitted

    Points: 1908

    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

  • Misha_SQL

    SSCertifiable

    Points: 5385

    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.

  • Ye-Chan

    Valued Member

    Points: 73

    Thanks for posting this article. Very informative. I have performed the same test except changed the columns types to VARCHAR(9) for agents2009 and to VARCHAR(10) to dbo.agents and what I find interesting is that it does an index seek on both tables. Somehow using VARCHAR seems to be a little more "forgiving".

  • chudman

    SSCrazy

    Points: 2420

    Very nice article about an issue that every SQL DBA will encounter eventually. I have seen this type of problem crop up in 3rd-party solutions that are supposed to be very high-performing OLTP systems. When the problem is compounded by creating views that join multiple views together it can be very hard to track down the culprit. Creating the same query without the use of the view is a great way to set performance expectations.

    Jeff Bennett

    St. Louis, MO

  • N_Muller

    SSCarpal Tunnel

    Points: 4035

    What would be the expected behavior had the tables been defined with varchar(9) and varchar(10), instead of char(9) and char(10)?

  • Ye-Chan

    Valued Member

    Points: 73

    N_Muller (4/18/2014)


    What would be the expected behavior had the tables been defined with varchar(9) and varchar(10), instead of char(9) and char(10)?

    Hi N_Muller,

    If I understood the article correctly, the reason why a table scan has been performed is because one table has been declare as char(9) and the other on with char(10). I expected a table scan as well with varchar(9) and varchar(10) until both columns have been declared as varchar(10).

Viewing 15 posts - 1 through 15 (of 21 total)

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