SQL Server Performance

Cast Command

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by travistan, May 3, 2007.

  1. travistan New Member

    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
  2. Madhivanan Moderator

    No. It depends on other factors

    Madhivanan

    Failing to plan is Planning to fail
  3. FrankKalis Moderator

    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
  4. travistan New Member

    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)
  5. Adriaan New Member

    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.
  6. Adriaan New Member

    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
  7. Adriaan New Member

    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.
  8. MohammedU New Member

  9. yoavm New Member

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

Share This Page