Select query runs forever in sql 2005

  • OK. That's very complete.

    Get the execution plans. Especially the one where the 2005 ran well, but the others too. That way we can tell what's happening between the two servers.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • See if you can use this code to strip leading zeros and if it helps your query.

    declare @varstr nvarchar(50);

    set @varstr = '000045691823';

    select substring(@varstr,patindex('%[A-Za-z1-9]%', @varstr),len(@varstr) - patindex('%[A-Za-z1-9]%', @varstr) + 1)

    set @varstr = '000Z045691823';

    select substring(@varstr,patindex('%[A-Za-Z1-9]%', @varstr),len(@varstr) - patindex('%[A-Za-z1-9]%', @varstr) + 1)

  • Thanks Grant

    I will look at the excecution plans but I might need your help with analyzing them

  • No worries. Try the stuff Lynn is suggesting. He's looking over the queries. I just glanced at them. I'm interested in how the execution plans vary.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I came up with another option, and I think it is easier. It uses patindex to find the first non-zero character.

    select substring(@varstr,patindex('%[^0]%', @varstr),len(@varstr) - patindex('%[^0]%', @varstr) + 1)

  • thanks Lynn, i will try that

  • Hi Grant

    How can I send you the screen prints of the execution plans?

  • Lynn Pettis (4/14/2009)


    I came up with another option, and I think it is easier. It uses patindex to find the first non-zero character.

    select substring(@varstr,patindex('%[^0]%', @varstr),len(@varstr) - patindex('%[^0]%', @varstr) + 1)

    Hi Lynn

    I tried your method and it works nicely, but when I run it on my sql 2005 instance it runs for about 9 minutes and when I run it on my sql 2000 instance over the sa data it runs for 2 seconds.

    I looked at the diffenrent execution plans for 2000 and 2005 and they are completely different.

    Why why why?

    Thanks

  • could you save the execution plans as .zip files and attach them.

    ----------------------------------------------
    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
  • Here's a video on exactly how to capture the execution plans & post them to SSC.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • While you are capturing the Execution Plan, could you post the complete query code you are running and the DDL (Including indexes) for the two tables.

    From what I can tell, you are looking for all records in Table1 that does not have a corresponding record in Table2 based on Table1.BIBNAC = Table2.ACCOUNTNUM, correct?

  • Had an idea for SQL Server 2005. This WON"T work on SQL Server 2000. Please give it a try, and let's see what happens.

    select

    *

    from

    Table1 T1

    where

    substring(T1.BIBNAC,patindex('%[^0]%', T1.BIBNAC),len(T1.BIBNAC) - patindex('%[^0]%', T1.BIBNAC) + 1) in

    (select

    substring(T1A.BIBNAC,patindex('%[^0]%', T1A.BIBNAC),len(T1.BIBNAC) - patindex('%[^0]%', T1A.BIBNAC) + 1)

    from

    Table1 T1A

    except

    select

    substring(T2.ACCOUNTNUM,patindex('%[^0]%', T2.ACCOUNTNUM),len(T2.ACCOUNTNUM) - patindex('%[^0]%', T2.ACCOUNTNUM) + 1)

    from

    Table2 T2)

  • mmm

    Please confirm if this is a double post or not?

    http://www.sqlservercentral.com/Forums/Topic694547-146-2.aspx?Update=1

    If it is then could the reason not be that your code is different.

    The one removes 0's and the other doesn't

    as Lynn and I asked before please pot your code...

    ----------------------------------------------
    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
  • Here is the execution plans for the same query

    I used the method that Lynn posted

    select * from KRI_ASP_AccFACSNtEqAccConsole_DATA_C where

    substring(BIBNAC,patindex('%[^0]%', BIBNAC),len(BIBNAC) - patindex('%[^0]%', BIBNAC) + 1)

    NOT IN

    (

    SELECT

    substring(accountnum,patindex('%[^0]%', accountnum),len(accountnum) - patindex('%[^0]%', accountnum) + 1)

    FROM

    KRI_ASP_AccFACSNtEqAccConsole_DATA_A

    )

    over the same amount of data on sql 2000 and 2005

    I could not save the execution plan for the 2000 instance but attached a screen shot

  • hwo long did you say the 2005 query takes vs the sme query on 2000?

    ----------------------------------------------
    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 15 posts - 16 through 30 (of 68 total)

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