Select query runs forever in sql 2005

  • wow this is very strange....

    I've created two tables with with the columns in your query added data and indeed the query runs in secs if not milliseconds on 2000 and well have yet to get a result in 2005 🙁

    ----------------------------------------------
    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
  • Can you post the actual execution plan instead of the estimated one? I'd really like to see, as much as possible, what's happening.

    This is a weird one. Also, can you post the 2000 plan as a text plan? I hate the bloody things, but that's the only way to transmit the underlying data in 2000.

    Thanks,

    Grant

    "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

  • Here is the actual execution paln for the query run on 2005

    I'm not sure how to get the 2000 text plan you are talking about? Please excuse the ignorance

  • Can you tell me more about the configuration of the server all of this is running on?

  • You can use SET SHOWPLAN_ALL or SHOWPLAN_TEXT.

    ALL provides more information but it's more difficult to read. Let's go with ALL despite the difficult, but you can post both.

    "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 am confused as to why the performance is so different between the two, but the fact that performance is sub-optimal is because of the functions on the columns in the WHERE clause. You're just not going to get index use out of that. If you need to run this query all the time, what about creating a derived column on the side. Use a trigger to populate it with values so that you can do a straight select with a standard WHERE clause against it instead of all this funky manipulation of strings.

    "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

  • Grant Fritchey (4/15/2009)


    I am confused as to why the performance is so different between the two, but the fact that performance is sub-optimal is because of the functions on the columns in the WHERE clause. You're just not going to get index use out of that. If you need to run this query all the time, what about creating a derived column on the side. Use a trigger to populate it with values so that you can do a straight select with a standard WHERE clause against it instead of all this funky manipulation of strings.

    Here is the SHOWPLAN_ALL results

    StmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions

    SET STATISTICS PROFILE ON12101SETSTATON0

    (1 row(s) affected)

    StmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions

    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

    )13101313.926760.10347269SELECT0

    |--Nested Loops(Left Anti Semi Join, WHERE:((substring([KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC], patindex('%[^0]%', [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC]), len([KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC])-patindex('%[^0]%', [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC])+1)=NULL OR substring([KRI_ASP_AccFACSNtEqAccConsole_DATA_A].[ACCOUNTNUM], patindex('%[^0]%', [KRI_ASP_AccFACSNtEqAccConsole_DATA_A].[ACCOUNTNUM]), len([KRI_ASP_AccFACSNtEqAccConsole_DATA_A].[ACCOUNTNUM])-patindex('%[1331Nested LoopsLeft Anti Semi JoinWHERE:((substring([KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC], patindex('%[^0]%', [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC]), len([KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC])-patindex('%[^0]%', [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC])+1)=NULL OR substring([KRI_ASP_AccFACSNtEqAccConsole_DATA_A].[ACCOUNTNUM], patindex('%[^0]%', [KRI_ASP_AccFACSNtEqAccConsole_DATA_A].[ACCOUNTNUM]), len([KRI_ASP_AccFACSNtEqAccConsole_DATA_A].[ACCOUNTNUM])-patindex('%[^0]%', [KRI_ASP_AccFACSNtEqAccConsole_D313.926760.02.5706999E-31070.1034413[KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRTRTP], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRCRNO], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRDOCT], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRURF1], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRURF2], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRSTAT], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRDTPT]PLAN_ROW01.0

    |--Table Scan(OBJECT:([Momentum].[dbo].[KRI_ASP_AccFACSNtEqAccConsole_DATA_C]))1343Table ScanTable ScanOBJECT:([Momentum].[dbo].[KRI_ASP_AccFACSNtEqAccConsole_DATA_C])[KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRTRTP], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRCRNO], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRDOCT], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRURF1], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRURF2], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRSTAT], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRDTPT]615.04.2022943E-27.5499999E-41074.2777944E-2[KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRTRTP], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRCRNO], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRDOCT], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRURF1], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRURF2], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[BIBNAC], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRSTAT], [KRI_ASP_AccFACSNtEqAccConsole_DATA_C].[CRDTPT]PLAN_ROW01.0

    |--Table Spool1353Table SpoolLazy Spool1.01.6756756E-22.7999999E-7365.4525658E-2[KRI_ASP_AccFACSNtEqAccConsole_DATA_A].[ACCOUNTNUM]PLAN_ROW0615.0

    |--Table Scan(OBJECT:([Momentum].[dbo].[KRI_ASP_AccFACSNtEqAccConsole_DATA_A]))1365Table ScanTable ScanOBJECT:([Momentum].[dbo].[KRI_ASP_AccFACSNtEqAccConsole_DATA_A])[KRI_ASP_AccFACSNtEqAccConsole_DATA_A].[ACCOUNTNUM]1.03.7578501E-27.9600002E-51683.7658099E-2[KRI_ASP_AccFACSNtEqAccConsole_DATA_A].[ACCOUNTNUM]PLAN_ROW01.0

    (5 row(s) affected)

    StmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions

    SET STATISTICS PROFILE OFF14101SETSTATON0

    (1 row(s) affected)

  • Lynn Pettis (4/15/2009)


    Can you tell me more about the configuration of the server all of this is running on?

    Its a Windows Server 2003 server SP2

    Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)

  • Rowan (4/15/2009)


    Lynn Pettis (4/15/2009)


    Can you tell me more about the configuration of the server all of this is running on?

    Its a Windows Server 2003 server SP2

    Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)

    Single proc? Dual Proc? Single Core? Dual Core? Quad Core? How much memory? Anything else regarding configuration?

  • Lynn Pettis (4/15/2009)


    Rowan (4/15/2009)


    Lynn Pettis (4/15/2009)


    Can you tell me more about the configuration of the server all of this is running on?

    Its a Windows Server 2003 server SP2

    Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)

    Single proc? Dual Proc? Single Core? Dual Core? Quad Core? How much memory? Anything else regarding configuration?

    Sorry, its a Intel Xeon 3.4Ghz with 7 GB of RAM with 4 CPU's

  • This may be counter-intuitive. on SQL Server 2005, add this to the end of your query: OPTION (MAXDOP 1)

    I would still like you to try my other SQL Server 2005 solution as well.

  • If I'm reading the data correctly, the estimated rows on the 2000 query is 13 right through, but the estimated values in 2005 are all over the place, from 1 up to 829 and it ultimately returns 112 rows, but was estimating 493. That's a fairly wide disparity. Can you try updating the statistics with a full scan? Instead of using sp_updatestats, run UPDATE STATISTICS [tablename] WITH FULLSCAN.

    I think that's the cause of the disparity between the two plans. Although I could be very wrong.

    "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

  • Thanks Lynn, i will try that asap

    I did try your query where you use 'except'

    This did cut the running time down quite a bit

  • did you try and update the stats like Grant mentioned. I'm very interested to know why different versions of sql are giving very different results

    ----------------------------------------------
    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
  • Thanks Grant i will try that asap

Viewing 15 posts - 31 through 45 (of 68 total)

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