Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Unexpected View Performance Expand / Collapse
Author
Message
Posted Wednesday, January 12, 2011 12:43 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 536, Visits: 754
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.


Post #1046737
Posted Friday, April 18, 2014 5:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:24 AM
Points: 3, Visits: 234
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".
Post #1562980
Posted Friday, April 18, 2014 7:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 9:43 AM
Points: 98, Visits: 264
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



Post #1563030
Posted Friday, April 18, 2014 9:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:29 AM
Points: 64, Visits: 191
What would be the expected behavior had the tables been defined with varchar(9) and varchar(10), instead of char(9) and char(10)?
Post #1563080
Posted Friday, April 18, 2014 10:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:24 AM
Points: 3, Visits: 234
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).
Post #1563095
Posted Sunday, April 20, 2014 4:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 36,938, Visits: 31,441
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1563361
Posted Sunday, April 20, 2014 4:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 36,938, Visits: 31,441
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1563362
Posted Sunday, April 20, 2014 5:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 36,938, Visits: 31,441
@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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1563367
Posted Monday, April 21, 2014 8:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:29 AM
Points: 64, Visits: 191
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.
Post #1563492
Posted Monday, April 21, 2014 8:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 36,938, Visits: 31,441
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1563503
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse