SQL Server Performance Forum – Threads Archive
Cast CommandHi , 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 ,
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? —
Microsoft SQL Server MVP
Hi FrankKalis , Here you are. Please help. Really have serious problem.
WHERE CAST (DT_ID AS VARCHAR) + AGN_ID NOT IN
(SELECT CAST (DT_ID AS VARCHAR) + AGN_ID
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
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!