Unexpected View Performance

Question

Unexpected View Performance

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

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

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

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

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 2

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.

Change is inevitable... Change for the better is not.

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

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 3

@thom-2 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)

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

+ 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.

Change is inevitable... Change for the better is not.

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

N_Muller SSCarpal Tunnel Points: 4037 More actions · Answer 4

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: 1004419 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

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

GP Van Eron SSCarpal Tunnel Points: 4626 More actions · Answer 6

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

- GP