SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unexpected View Performance


Unexpected View Performance

Author
Message
Misha_SQL
Misha_SQL
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2212 Visits: 1023
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
Ye-Chan
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 321
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
chudman
SSC Eights!
SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)

Group: General Forum Members
Points: 942 Visits: 426
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
N_Muller
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1444 Visits: 466
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
Ye-Chan
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 321
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).
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)

Group: General Forum Members
Points: 338463 Visits: 42604
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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)

Group: General Forum Members
Points: 338463 Visits: 42604
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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)

Group: General Forum Members
Points: 338463 Visits: 42604
@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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
N_Muller
N_Muller
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1444 Visits: 466
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
Jeff Moden
SSC Guru
SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)

Group: General Forum Members
Points: 338463 Visits: 42604
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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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