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

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!