Query performance problem after sql 2005 migration

  • Thanks for the reply.

    It still does not explain the following...

    select * from Table1 where

    BIBNAC not in

    (

    SELECT

    ACCOUNTNUM

    FROM

    Table2

    )

    completes in a like 3 seconds

    where as

    select * from Table1 where

    REPLACE(LTRIM(REPLACE(BIBNAC, '0', ' ')), ' ', '0')

    not in

    (

    SELECT

    REPLACE(LTRIM(REPLACE(ACCOUNTNUM, '0', ' ')), ' ', '0')

    FROM

    Table2

    )

    takes forever....

    Surely 'replace' and 'ltrim' and using it in this manor has not been removed in sql 2005?

  • This is double post :

    http://www.sqlservercentral.com/Forums/Topic696401-149-1.aspx?Highlight=BIBNAC

    And to be honest those two queries are completely different.

    In that in the first one the optimizer could if it wanted use indexes.

    In the second query no indexes can be used as you are using functions around you where clauses fields.

    In the other topic you said that the query was exactly the same , please could you confirm weather these topics are related or not?

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply