Unexpected View Performance

  • Jeff Moden

    SSC Guru

    Points: 994648

    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)?

    What would you have expected? Do the experiment. Report your findings. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994648

    pandeharsh (1/11/2011)


    Hi,

    Can you draw the conclusion in other words ?

    I think a better question would be, after reading the article and doing all of the tests, what is YOUR conclusion? 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994648

    @Thom Bolin,

    I might be the only one but I like the title of "Unexpected View Performance" because use of the UNION operator seems to be much more prevalent in views than in most other places. It's also where you discovered the problem and so I think the title is very appropriate. Thanks for taking the time write it and nice job.

    Just a suggestion, though... I'd recommend staying away from 3rd party data generators to build test data for whatever article you write simply because not everyone will have it and that will inherently limit participants. When you did provide the T-SQL to build test data, it only built one value which, again, will tend to limit participants. The only things that kept this from being a 5 star article is the absence of a decent T-SQL test data generator and that one statement that had no code to go with it. 🙂

    With those thoughts in mind, here's one way to generate a shedload of the data you needed in a short time without explicit loops.

    SELECT TOP 1000000

    AccountNumber =

    CAST(

    + CHAR(ABS(CHECKSUM(NEWID()))%26+65)

    + CHAR(ABS(CHECKSUM(NEWID()))%26+65)

    + CHAR(ABS(CHECKSUM(NEWID()))%26+65)

    + RIGHT(ABS(CHECKSUM(NEWID()))%1000000+1000000,6)

    AS CHAR(9))

    INTO dbo.Agents2009

    FROM master.sys.all_columns ac1

    CROSS JOIN master.sys.all_columns ac2

    ;

    SELECT TOP 1000000

    AccountNumber =

    CAST(

    + CHAR(ABS(CHECKSUM(NEWID()))%26+65)

    + CHAR(ABS(CHECKSUM(NEWID()))%26+65)

    + CHAR(ABS(CHECKSUM(NEWID()))%26+65)

    + RIGHT(ABS(CHECKSUM(NEWID()))%1000000+1000000,6)

    AS CHAR(10))

    INTO dbo.Agents

    FROM master.sys.all_columns ac1

    CROSS JOIN master.sys.all_columns ac2

    ;

    Like I said, nice job and thanks for sharing your find with us.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • N_Muller

    SSCarpal Tunnel

    Points: 4035

    Jeff,

    I don't disagree with you that I should and will do my own testing should I need to. Unfortunately I don't have the time to run tests on every article I come across, so I asked.

    Back in the time of SQL Server 6.5 (still Sybase) the performance of char was better than varchar as there was no need to check length. The problem with char was that if the string was actually shorter than the length it was required to right-trim the string. That is, if you had a string defined as char(10) and the value was 'abc', when you query for the length of string, it wouldn't return three as the length of the string, but 10 as the string definition. In order to return len(string) as three, you needed to right-trim the string.

    I believe the performance issue went away with SQL Server 7 (I may be wrong on my versions), and since then I have switched to use varchar for all strings. Since varchar was null-terminated, whether the two variables were defined as varchar(9) and varchar(10) was not an issue.

    With SQL Server 2012 if you define a string as char(10), populate it with 'abc' and query for the length, it returns three and not the length of the string definition. I had the impression that defining a string as char(10) or varchar(10) would yield the same results. With that said, I was surprised to see that performance of char was still a problem. I'm confident that varchar doesn't pose a problem as I'm sure the issues with different length definitions for varchar is prevalent in many of the databases I have come across over the last 10+ years, even though I have not checked that specifically. And, since I have not experienced the performance issues I wondered if the there was any experience in defining the strings as varchar.

  • Jeff Moden

    SSC Guru

    Points: 994648

    N_Muller (4/21/2014)


    Jeff,

    I don't disagree with you that I should and will do my own testing should I need to. Unfortunately I don't have the time to run tests on every article I come across, so I asked.

    Heh... now that you've put it that way, I totally agree, especially since there wasn't a convenient generator for readily-consumable test data included in the article.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • GP Van Eron

    SSCarpal Tunnel

    Points: 4626

    Thanks for posting the article. It's a small point but one that can make a big difference in query compilation.

    - GP

Viewing 6 posts - 16 through 21 (of 21 total)

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