query takes a long time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query takes a long time

Author

Topic

Sean_B
Posting Yak Master

111 Posts

Posted - 2009-11-04 : 12:57:42

Hi,

the following sql is used in a report to check if there have been any changes between two tables

insert into #report select 'Error in CheckFrozenCache_SimSample'
where (select COUNT(*) FROM (SELECT * FROM CheckFrozenCache_SimSample except SELECT * FROM tempCheckFrozenCache_SimSample) X) > 0

this has been built using dynamic sql and I expect it to write into the following table

CREATE TABLE #report (line VARCHAR(MAX))

both bits run quickly
insert into #report select 'Error in CheckFrozenCache_SimSample'
and
select COUNT(*) FROM (SELECT * FROM CheckFrozenCacheexcept SELECT * FROM tempCheckFrozenCache) X) > 0

but when I put them together it takes nearly a minute. The data in the tables it is comparing are identical in this case

I'sd be grateful if someone can explain why putting these two bits together suddenly slows it down.

Sean

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-11-05 : 05:43:30

Try an extra FROM. Something like:


INSERT INTO #report
SELECT 'Error in CheckFrozenCache_SimSample'
FROM
(
	SELECT COUNT(*) AS ECount
	FROM
	(
		SELECT *
		FROM CheckFrozenCache_SimSample
		EXCEPT
		SELECT *
		FROM tempCheckFrozenCache_SimSample
	) E
) D
WHERE ECount > 0

asgast
Posting Yak Master

149 Posts

Posted - 2009-11-06 : 04:39:07

putting sub query in where will always work slower then just selecting
and later you have two more subqueries with except between them

I don't know our structure but i would do the following
1 change SELECT COUNT(*) AS ECount to EXISTS, as i understand you are looking for at least 1 value that fits your criteria
IF EXISTS (SELECT 1 FROM ((SELECT * FROM CheckFrozenCache_SimSample except SELECT * FROM tempCheckFrozenCache_SimSample)))
insert into #report select 'Error in CheckFrozenCache_SimSample'

IF at least one row is returned from (SELECT * FROM CheckFrozenCache_SimSample except SELECT * FROM tempCheckFrozenCache_SimSample) Error in CheckFrozenCache_SimSample will be inserted into your table. As I understand you are trying to achieve this with counts.

it should work faster than your original query, I think so :)

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources