Cast Command | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Cast Command

Hi , Do "Cast" command perform slow on sql 2005 ? I have the same query running on sql 2000 and 2005. But the performance on 2000 is better then 2005 which the server is lower spec compare to 2005. Thanks ,
Travis
No. It depends on other factors Madhivanan Failing to plan is Planning to fail
I doubt that the CAST command itself is the root cause. However, the engine has become much more sensible to implicite conversion of data types. This could slow down things. Can you post the code you’re using? —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Hi FrankKalis , Here you are. Please help. Really have serious problem.
SELECT DISTINCT
DT_ID,
AGN_ID,
AGN_CUR_ID,
AGN_SCH_BCYC_ID,
AGN_STS_ID,
AGN_CR_BAL_AMT,
AGN_CR_AMT,
AGN_CR_USED_AMT,
AGN_DIR_COMM,
AGN_GDS_COMM,
AGN_ONL_COMM,
AGN_ADD_ON_COMM,
AGN_NOTIFY_AMT
FROM F_AGN_TMP
WHERE CAST (DT_ID AS VARCHAR) + AGN_ID NOT IN
(SELECT CAST (DT_ID AS VARCHAR) + AGN_ID
FROM AAWH.dbo.F_AGN)
Better to use a correlated subquery with NOT EXISTS: WHERE NOT EXISTS
(SELECT X.* FROM AAWH.dbo.F_AGN X
WHERE X.DT_ID = F_AGN_TMP.DT_ID
AND X.AGN_ID = F_AGN_TMP.AGN_ID) You’ll still have to look at indexes, but this should already improve performance. *** Please post in the appropriate section – this has nothing to do with hardware.
You could also use a LEFT JOIN with NULL criteria: FROM F_AGN_TMP
LEFT JOIN AAWH.dbo.F_AGN X ON F_AGN_TMP.DT_ID = X.DT_ID AND F_AGN_TMP.AGN_ID = X.AGN_ID
WHERE X.DT_ID IS NULL

Finally, the reason why CAST is bad for performance: Once you start applying functions to column values in your WHERE clause, SQL Server cannot use an index for the column and has to scan the entire table.
For more details… http://www.sql-server-performance.com/transact_sql.asp
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

try to avoid as much as possible the use of funtion in field in the where cause, it causes full table scan!
]]>