Implicit conversion doesn't add cost
-
I came across a scenario where a query was doing implicit conversion and when removing that conversion the cost of the plan doesn't change. I would have expected there to be a slight difference due to the extra operation.
here is example
CREATE TABLE #temp(id INT, dec1 DECIMAL(4,2))INSERT INTO #temp(id,dec1)
VALUES(1,10.00),
(2,20.00),
(3,33.45),
(4,45.70),
(5,59.00)
CREATE CLUSTERED INDEX CI_id ON #temp(id)
--implicit conversion
SELECT id,LEFT(CAST(dec1 AS VARCHAR), LEN(dec1)-3)
FROM #temp;
--no implicit conversion
SELECT id,CAST(dec1 AS INT)
FROM #temp;
the first query does an explicit conversion for the CAST function and an implicit conversion for the LEN function. The second query only does the one explicit conversion.
The estimated cpu cost of both queries is 0.0000005. Shouldn't the first query have a higher cost due to the extra conversion?
-
Robert klimes (10/16/2013)
The estimated cpu cost of both queries is 0.0000005. Shouldn't the first query have a higher cost due to the extra conversion?With such a small amount of data you can see the difference .. test it with millions or even thousands..
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-) -
Bhuvnesh (10/16/2013)
With such a small amount of data you can see the difference .. test it with millions or even thousands..amount of data is irrelevent. with 1 million rows the CPU cost for both queries goes up to 0.1 but it is the same for both queries.
-
Cost isn't a real performance indicator. It's just an estimated value used by the query optimizer to choose an execution plan.
-
Luis Cazares (10/16/2013)
Cost isn't a real performance indicator. It's just an estimated value used by the query optimizer to choose an execution plan.I am not overly concerned with performance in this case I just am trying to gain insight why the query optimizer would estimate the same cost while one query clearly has more work to do. From a performance perspective both of these example queries ( and the actual query where I discovered this) the elapsed time and IO are identical.
Even if it is just an estimation, shouldn't it be different if there is more work is being done in one query?
-
Both plans are doing a conversion and I don't think that converting twice is extra work for the CPU.
[Expr1004] = Scalar Operator(substring(CONVERT(varchar(30),[tempdb].[dbo].[#temp].[dec1],0),(1),len(CONVERT_IMPLICIT(varchar(41),[tempdb].[dbo].[#temp].[dec1],0))-(3)))
[Expr1004] = Scalar Operator(CONVERT(int,[tempdb].[dbo].[#temp].[dec1],0))
-
I would expect that behind the scenes the LEN function is implemented as a set of overloaded function prototypes and there is no implicit conversion occurring.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply