Post reply

long runing simple query

  • June 25, 2009 at 10:04 am

    #207474

    I am running 2 queries simultaneously

    select

    from dbo.Table1

    except

    select

    from obeaver.RevBud.dbo.Table1

    Run time = 54min

    select

    from dbo.Table1

    except

    select

    from obeaver.RevBud.dbo.Table1 where recorddate>'2009-06-03'

    runtime= taking forever, its been 15hrs still running

    I was just wondering why is it taking so long when i have a where clause with recorddate which looks for only recent updated records.

  • June 25, 2009 at 10:35 am

    #1015446

    At first blush this looks like a case of a bad execution plan. Can you post the execution plan?

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

  • June 25, 2009 at 12:13 pm

    #1015526

    Execution plan Plz

  • June 25, 2009 at 12:15 pm

    #1015529

    EXCEPT and INTERSECT will have bad plans for the most part, so this doesn't surprise me. Handy, but not performance-friendly.

  • June 25, 2009 at 12:19 pm

    #1015532

    Well sure, but in my tests except performs better than the NOT IN equivalent... Plus that does nothing to explain why it takes so much longer when you add a WHERE clause.

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

  • June 25, 2009 at 12:51 pm

    #1015557

    I am not sure , But Can you try once with UNION ALL (combined with a GROUP BY)

    : really quick and easy way to compare two tables.

  • June 25, 2009 at 12:56 pm

    #1015560

    John Paul (6/25/2009)

    I am not sure , But Can you try once with UNION ALL (combined with a GROUP BY)

    : really quick and easy way to compare two tables.

    Well sure, but still an exec plan is what we need here. WHERE clause could make it worse if the query processor is confused on statistics, indexes, etc. Could be a number of things right?

Viewing 7 posts - 1 through 6 (of 6 total)

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