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 ««12

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: 2 days ago @ 5:57 PM
Points: 535, Visits: 729
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 2 days ago @ 5:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 10:05 AM
Points: 3, Visits: 216
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 2 days ago @ 7:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 11:24 AM
Points: 93, Visits: 244
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 2 days ago @ 9:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 10:34 AM
Points: 36, Visits: 133
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 2 days ago @ 10:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 10:05 AM
Points: 3, Visits: 216
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
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse