June 25, 2009 at 10:04 am
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
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
Execution plan Plz
June 25, 2009 at 12:15 pm
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
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
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
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