Have you checked your datatypes lately?
Earlier this week I was working through an issue that I’ve seen one too many times lately and thought Id write about it. For various reasons *cough* Legacy *cough* I often seem to run across columns in tables that are defined with what would appear to be the wrong data type or worse, multiple data elements stuffed into one column!
In this case the table held various account codes in a char(06) column. These codes were things like ‘001’, ‘002’,’A’, ‘B’ etc
Each account code was for use in a different system. so, on system X it was an integer and system Y the codes were character based, etc
This data was used to join many different tables. Initially these joins were small and straightforward with predicates restricting the code table results to only include the proper data for the system being used.
pseudo code looks roughly like this
SET DATADESC = CODE.MORE_DATADESC
ON DATA.CODE_KEY = CODE.CODE_KEY
ON META_KEY = CODE.CODE_KEY
AND DATESTAMP > = ’9999-12-31′
WHERE DATA IS NULL
AND CODE.DATATYPE = ‘ACCT’
This is the execution plan from a typical update of 150Million rows
Notice the Metadata table and Codes table are joined before the data table so the resulting codes data is only going to include the 16 rows of smallint data which passes the implicit convert to smallint
As the rowcounts increased and joins changed the implicit data type conversions continued to work until one day they didn’t. Instead they started returning this error message
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value ‘B ‘ to data type smallint”
After some digging it turns out the optimizer had started using a different plan where it joined the tables together in a different order.
Heres that execution plan of the same update
Notice the Metadata table join is reordered and happens after the codes table is joined to the data table. When the data table is joined to the code table the error above occurs as character data in codes cant be converted to smallint during the join
To overcome this problem there are a couple of choices (admittedly there are more than a couple so this list isnt inclusive)
Best –> Change the source table to account for having different source systems account code types in different columns with the correct datatypes. Benefits are straightforward, the BIG drawback is the extra work for system redesign
Better –> Change the queries to use an explicit conversion on the joined columns changing the datatype on the data table from smallint to char(06). The benefit is the small amount of change needed, the drawback is the extra bytes involved per row joined
Good OK –> Add a query hint to force the join order. The benefit is the small amount of change needed, the drawback is in my mind we’re now forcing the optimizer to take a different path than it would if left to its own. I try to avoid hints unless there arent other options and in this case there are.
Other things that could be considered as solutions include temp tables, CTE’s, etc. all of which were more change then we wanted to pursue testing.
|Print article||This entry was posted by Allen Kinsel on June 27, 2012 at 10:45 am, and is filed under DBA, SQL Server, Syndicated. Follow any responses to this post through RSS 2.0. You can leave a response or trackback from your own site.|
No comments yet.
No trackbacks yet.
about 4 months ago - No comments
Came across a strange permission issue today that I thought I’d blog about since it is apparently somewhat common judging by the number of unanswered forum posts about it.
Adding/editing or Testing SSRS Datasources in Report Manager generates a permission error
A developer was trying to add a datasource and use “test connection” in the report manager web interface instead of BIDS which, incidentally
about 1 year ago - 40 comments
Its the first week of June and for those of us living along the Gulf and Atlantic coasts of the US, that brings the beginning of hurricane season. It also means its time for this months installment of T-SQL Tuesday.
This Months Topic
Disaster Recovery. This topic is very near and dear to me based on the fact that
about 2 years ago - 11 comments
After last years Summit we launched a feedback site http://feedback.sqlpass.org in hopes of gathering all of the feedback about the event in one place. The number one thing people have asked for on that site is for there to be a track of sessions in the 400-500 level range. The problems with the community desire
about 2 years ago - 2 comments
Occasionally I’m asked what the most important thing for a DBA to know is. It seems some people want to know what 1 thing to focus on in order to be successful long term as a DBA. I’ve pondered this question for quite a long time and I’ve given various answers over the years.
about 2 years ago - 5 comments
This months TSQL Tuesday is hosted by a good friend Pat right over at SQL Asylum
For this months entry I decided to keep it short and sweet, following in my Bits N Bytes theme.
The Meta Script
In the true sense of the word automation, this really doesn’t fit but, in the terms of quickly getting something
about 2 years ago - 4 comments
This corruption story begins like many. Somebody in a server room far far away decided to make a change to a VMware guest machine and that little change rippled through our poor server like a lady Gaga Meat Dress through the VMA’s. Needless to say, it wasnt pretty. The full set of events may never
about 2 years ago - 1 comment
The other day we were going through some old scripts and had a huge discussion about changing object ownership in SQL. Forever the only answers were to recreate the objects or use the stored procedures: sp_changeobjectowner or sp_changedbowner. Alternatively at times there were those of us who used some other undisclosed hack of system tables
about 2 years ago - No comments
For the new year I have decided to try something different with a series of small regular posts.
Dealing with our large team of developers and our DBA team as well, I often run across interesting discussions about small things that are easily overlooked. Often times these discussions end in a exclamation of “I knew that!!” Of
about 2 years ago - 1 comment
And no, I dont mean in my mothers eyes, thats a given.
I won the Ipad/SQL Monitor Contest that Redgate was sponsoring over on Grant Fritcheys Blog
Im thrilled to have won!! Ive been eyeing Ipads for quite some time but, the novelty & cost has kept me at arms length.
Im always in the market for DBA tools that can make
about 2 years ago - No comments
Had a strange error on a SQL 2008 cluster the other day,
The OS was Windows 2008 R2
We kept getting messages that the cluster node was offline because the Quorum was unavailable. This made little sense as both nodes in this cluster were online and the Quorum disk was available. We could ping across the heartbeat,