THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Match those types!

This blog has moved! You can find this content at the following new location:

http://sqlblog.karaszi.com/match-those-types/

Published Tuesday, April 28, 2009 1:30 PM by TiborKaraszi

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Venkat said:

Very Informative post.   When developers are in a hurry, it is difficult to hammer in the point about matching the types, they usually think SQL is a no brainer.

April 28, 2009 10:03 AM
 

Alexander Kuznetsov said:

An interesting post, Tibor!

I recently described a similar case, but I was concerned about correctness, not performance:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/11/19/defensive-database-programming-fun-with-changing-column-widths.aspx

April 28, 2009 11:25 AM
 

TiborKaraszi said:

Thanks!

Alex, yes, those truncations drive me mad. I never seem to remember when I get truncation, rounding or error when I work in TSQL. Whenever I'm asked, I always have to fire up a query window and test.

April 28, 2009 11:46 AM
 

Jonathan Kehayias said:

Tibor,

The best you can get with XEvents is the plan_handle as an action on the sql_statement_completed event, which doesn't get you anywhere really.  You would be better served just querying against the DMV's to find the plans with implicit conversions occuring in them.  I have a script I am working on right now that seems to be doing a decent job, but could still use some tweaking.  I'll post it when I get it a bit more usable.

April 28, 2009 8:39 PM
 

TiborKaraszi said:

Hi Jonathan,

My point with X/Events was, though, that plan information itself isn't enough here. I know I get a conversion, but why do the value I search for cause such difference (N'10000009' vs N'10000008') - the conversion is the same and the plans are 100% the same. This is where I hopes that X/Events could give some "deep insight". I doubt that plan cache would help since the very same plan is re-used.

April 29, 2009 2:03 AM
 

ALZDBA said:

You pinpointed one of the top issues when exploring query performance.

One remark:

you mention:

... since SQL Server nowadays potentially can use an index even if you end up with an implicit conversion at the column side....

On my SQL2008 SP1 Dev Edtn I still get :

Clustered Index Scan(OBJECT:([DSSCTest].[dbo].[t_ImplConversions2].[x]), WHERE:(CONVERT_IMPLICIT(nchar(8),[dbo].[t_ImplConversions2].[c2],0)=[@1]))  1           4           2           Clustered Index Scan           Clustered Index Scan           OBJECT:([dbo].[t_ImplConversions2].[x]), WHERE:(CONVERT_IMPLICIT(nchar(8),[dbo].[t_ImplConversions2].[c2],0)=[@1])  [dbo].[t_ImplConversions2].[c1], [dbo].[t_ImplConversions2].[c2], [dbo].[t_ImplConversions2].[c3]

April 29, 2009 5:36 AM
 

TiborKaraszi said:

Interesting, ALZDBA... I notice you have different table and db name from my example. What behavior do you get if you run my code as it is? Also what compatibility level do you havefor yout database?

April 29, 2009 5:42 AM
 

ALZDBA said:

Never mind the remark: I misread "use an index seek " for "use an index even"

Off course the good thing is it can use an index scan (might be less overhead than a full table scan), but the index scan is not interrupted if the value(s) are "passed" in the operation.

So the cost is still bigger than an index seek.

April 29, 2009 5:45 AM
 

ALZDBA said:

I even overlooked the fact I should add another index to see if it would be picked.

I redid the test.

DBLevel = 100

Here's my script.

IF OBJECT_ID('t_ImplConversions') IS NOT NULL DROP TABLE t_ImplConversions

GO

CREATE TABLE t_ImplConversions

(

c1 int IDENTITY(10000001,1) NOT NULL

,c2 char(8) NULL

,c2_1  AS (convert(char(8),c1)) PERSISTED NOT NULL

,c3 datetime not null

);

CREATE UNIQUE CLUSTERED INDEX x ON t_ImplConversions (c2_1);

GO

set nocount on ;

INSERT INTO t_ImplConversions(c3)

SELECT TOP 3000000 getdate()

FROM master..spt_values t1

 CROSS JOIN master..spt_values t2

 CROSS JOIN master..spt_values t3

----UPDATE t_ImplConversions SET c2 = CAST(c1 AS char(8))

---- CREATE UNIQUE CLUSTERED INDEX x ON t_ImplConversions (c2)

set nocount off

/*

Select c1, CAST(c1 AS char(8)), c2_1, c3

into t_ImplConversions2

from t_ImplConversions

CREATE UNIQUE CLUSTERED INDEX x ON t_ImplConversions2 (c2)

create nonclustered index y on t_ImplConversions2 (c2)

*/

GO

dbcc freeproccache

go

SET STATISTICS PROFILE ON

set statistics io on

set statistics time on

go

SELECT * FROM t_ImplConversions WHERE c2_1 = N'10000009'

SELECT * FROM t_ImplConversions WHERE c2_1 = N'10000008'

go

SELECT * FROM t_ImplConversions WHERE c2_1 = '10000009'

SELECT * FROM t_ImplConversions WHERE c2_1 = '10000008'

go

/*

since SQL Server nowadays potentially can use an index even if you end up with an implicit conversion at the column side.

*/

SELECT * FROM t_ImplConversions2 WHERE c2 = N'10000009'

SELECT * FROM t_ImplConversions2 WHERE c2 = N'10000008'

go

SELECT * FROM t_ImplConversions2 WHERE c2 = '10000009'

SELECT * FROM t_ImplConversions2 WHERE c2 = '10000008'

go

And indeed .... now it uses the Y index :-)

Table 't_ImplConversions2'. Scan count 5, logical reads 5275, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Rows                 Executes             StmtText                                                                                                                                                                                                      StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                                                 DefinedValues                                                                                                                         EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                                                                                                                                                                        Warnings Type                                                             Parallel EstimateExecutions

-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------

1                    1                    SELECT * FROM [t_ImplConversions2] WHERE [c2]=@1                                                                                                                                                              1           1           0           NULL                           NULL                           NULL                                                                                                                                                                     NULL                                                                                                                                  1             NULL          NULL          NULL        6,400179         NULL                                                                                                                                                                              NULL     SELECT                                                           0        NULL

1                    1                      |--Parallelism(Gather Streams)                                                                                                                                                                              1           3           1           Parallelism                    Gather Streams                 NULL                                                                                                                                                                     NULL                                                                                                                                  1             0             0,02850498    35          6,400179         [DSSCTest].[dbo].[t_ImplConversions2].[c1], [DSSCTest].[dbo].[t_ImplConversions2].[c2], [DSSCTest].[dbo].[t_ImplConversions2].[c2_1], [DSSCTest].[dbo].[t_ImplConversions2].[c3]  NULL     PLAN_ROW                                                         1        1

1                    4                           |--Nested Loops(Inner Join, OUTER REFERENCES:([DSSCTest].[dbo].[t_ImplConversions2].[c2]))                                                                                                             1           4           3           Nested Loops                   Inner Join                     OUTER REFERENCES:([DSSCTest].[dbo].[t_ImplConversions2].[c2])                                                                                                            NULL                                                                                                                                  1             0             2,09E-06      35          6,371674         [DSSCTest].[dbo].[t_ImplConversions2].[c1], [DSSCTest].[dbo].[t_ImplConversions2].[c2], [DSSCTest].[dbo].[t_ImplConversions2].[c2_1], [DSSCTest].[dbo].[t_ImplConversions2].[c3]  NULL     PLAN_ROW                                                         1        1

1                    4                                |--Parallelism(Repartition Streams, RoundRobin Partitioning)                                                                                                                                      1           5           4           Parallelism                    Repartition Streams            NULL                                                                                                                                                                     NULL                                                                                                                                  1             0             0,02850215    15          6,396891         [DSSCTest].[dbo].[t_ImplConversions2].[c2]                                                                                                                                        NULL     PLAN_ROW                                                         1        1

1                    4                                |    |--Index Scan(OBJECT:([DSSCTest].[dbo].[t_ImplConversions2].[y]),  WHERE:(CONVERT_IMPLICIT(nchar(8),[DSSCTest].[dbo].[t_ImplConversions2].[c2],0)=N'10000009'))                              1           6           5           Index Scan                     Index Scan                     OBJECT:([DSSCTest].[dbo].[t_ImplConversions2].[y]),  WHERE:(CONVERT_IMPLICIT(nchar(8),[DSSCTest].[dbo].[t_ImplConversions2].[c2],0)=N'10000009')                         [DSSCTest].[dbo].[t_ImplConversions2].[c2]                                                                                            1             3,84831       1,650079      15          5,498389         [DSSCTest].[dbo].[t_ImplConversions2].[c2]                                                                                                                                        NULL     PLAN_ROW                                                         1        1

1                    1                                |--Clustered Index Seek(OBJECT:([DSSCTest].[dbo].[t_ImplConversions2].[x]), SEEK:([DSSCTest].[dbo].[t_ImplConversions2].[c2]=[DSSCTest].[dbo].[t_ImplConversions2].[c2]) LOOKUP ORDERED FORWARD)  1           8           4           Clustered Index Seek           Clustered Index Seek           OBJECT:([DSSCTest].[dbo].[t_ImplConversions2].[x]), SEEK:([DSSCTest].[dbo].[t_ImplConversions2].[c2]=[DSSCTest].[dbo].[t_ImplConversions2].[c2]) LOOKUP ORDERED FORWARD  [DSSCTest].[dbo].[t_ImplConversions2].[c1], [DSSCTest].[dbo].[t_ImplConversions2].[c2_1], [DSSCTest].[dbo].[t_ImplConversions2].[c3]  1             0,003125      0,0001581     27          0,0032831        [DSSCTest].[dbo].[t_ImplConversions2].[c1], [DSSCTest].[dbo].[t_ImplConversions2].[c2_1], [DSSCTest].[dbo].[t_ImplConversions2].[c3]                                              NULL     PLAN_ROW                                                         1        1

But it still took the engine ( 1 quad core proc )   :

SQL Server Execution Times:

  CPU time = 3765 ms,  elapsed time = 962 ms.

April 29, 2009 5:57 AM
 

Scott R. said:

Tibor,

Great post!  I agree we should all be more aware of data types used in comparisons and functions, especially where they may introduce unintended behaviors with unfavorable consequences.

Wouldn't it be great if SSMS IntelliSense could flag T-SQL clauses with potential type conversion issues (from feedback in the execution plan), and possibly provide supplemental messages for further description?  Some compilers do this for traditional programs - why not SSMS for T-SQL via the query optimizer (a query plan is a type of program).  Maybe it could be an option in SSMS (like setting a compiler flagging option).  Leverage our experience with meaningful feedback from the query optimizer that people of all experience levels can use and learn from.

It's nice to dream.  Perhaps a new Connect item?

Scott R.

April 29, 2009 7:13 AM
 

Paul White said:

Spot on.  Especially the GetRangeThroughConvert stuff - I admit I do try to avoid that turning up in plans - it makes me nervous somehow ;c)

The only implicit conversions I grudgingly accept are those done for correctness when dividing decimals in an intermediate step of a computation.  To illustrate:

declare @a dec(5,2), @b dec(5,2)

select top (1) @a = 123.45, @b = 123.34

select top (1) @a / @b as c into dbo.t

...column c has datatype decimal (13,8) - who knew?!

Paul

April 29, 2009 7:40 AM
 

TiborKaraszi said:

Interesting idea with the Intellisense thing, Scott. The information is available to the SQL parser so why not help us with this as well! Enter a connect item and I will vote. :-)

April 29, 2009 12:29 PM
 

Alexander Kuznetsov said:

To avoid such problems it would be nice to declare a variable of the same type as a column, like we do in Oracle. Something like this:

DECLARE @c TYPEOF(t.c2)

-- conversion or error here

SET @c=N'10000009'

-- types always match

SELECT * FROM t WHERE c2 = @c

April 29, 2009 1:32 PM
 

Martin Schmidt said:

@Paul White

Here is a link from Microsoft that explains the outcome of your little test - msdn.microsoft.com/en-us/library/ms190476.aspx

So the mystery is not that big anymore

May 4, 2009 9:31 AM
 

Paul White said:

Martin,

Thanks for the link - I had seen something like that before, maybe even in BOL - I forget.  

If you can keep all those rules for scale and precision in your head, my hat comes off to you :c)

I'm as big a fan as anyone of getting the types right - but those rules are just way too much effort!

But thanks again for the link - appreciated.

Paul

May 4, 2009 5:48 PM
 

Ian Stirk said:

By coincidence, the following article tackles the problem at its source:

http://www.sqlservercentral.com/articles/Admin/65138/

The utility described in the article will allow you to quickly identify which columns have mismatched datatypes across tables in a database, correcting these will improve performance, integrity and maintainability

Thanks

Ian

May 5, 2009 3:28 AM
 

The Rambling DBA: Jonathan Kehayias said:

Last year Tibor Karaszi posted a blog post titled Match Those Types that talked about implicit data type

January 8, 2010 12:54 PM

Leave a Comment

(required) 
(required) 
Submit
Privacy Statement