Joining two queries slow. - 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)

Joining two queries slow.

Author

Topic

rkruis
Starting Member

28 Posts

Posted - 2011-07-22 : 15:50:49

Can anyone make a suggestion on how to speed this query up? It takes approx. 45 seconds to run. Running the two select on their own run under 1 second.


Select  count (OptionalData.Field1  )
from 
	(	SELECT distinct TBL.Field1
		FROM ID_71600_LANG_1 AS TBL 
		INNER JOIN OBJECT AS OBJ1 ON OBJ1.OBJECT_ID = TBL.Field1 
				AND OBJ1.OBJECT_OBJCLASS_ID = 256 
		INNER JOIN USERATTRIB AS U1 ON OBJ1.OBJECT_ID = U1.OBJECT_ID
			AND U1.ID_79600_LANG_1 = 1
		WHERE TBL.OBJECT_ID = 1294509 
			AND TBL.Field5 like 'Optional%' 
			AND TBL.Field7 ='TRADING PARTNER' 
	) as OptionalData 

left join 
	(	select distinct Field1
		FROM ID_71600_LANG_1  
		where OBJECT_ID = 1294509 
			AND Field7 ='TRADING PARTNER' 
			AND Not Field5 like 'Optional%' 
) as NonOptionalData 

on NonOptionalData.Field1 = OptionalData.Field1 
WHERE NonOptionalData.Field1 is null

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-22 : 16:12:34

Haven't tested this obviously but should give same results:

SELECT COUNT(DISTINCT TBL.Field1)
FROM ID_71600_LANG_1 AS TBL 
INNER JOIN OBJECT AS OBJ1 ON OBJ1.OBJECT_ID = TBL.Field1  AND OBJ1.OBJECT_OBJCLASS_ID = 256 
INNER JOIN USERATTRIB AS U1 ON OBJ1.OBJECT_ID = U1.OBJECT_ID AND U1.ID_79600_LANG_1 = 1
WHERE TBL.OBJECT_ID = 1294509 AND TBL.Field5 LIKE 'Optional%' AND TBL.Field7 ='TRADING PARTNER'
AND NOT EXISTS(SELECT * FROM ID_71600_LANG_1 
WHERE OBJECT_ID = 1294509 AND Field7 ='TRADING PARTNER' AND Field5 NOT LIKE 'Optional%' AND Field1=TBL.Field1)

Can't say if it will perform better. Make sure all the columns you're JOINing on and in the WHERE clause are indexed.

rkruis
Starting Member

28 Posts

Posted - 2011-07-22 : 17:11:19

Thanks for the response Rob.

This query actually ran a lot slower. It took about 3 min.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-22 : 17:38:57

How about this one:

;WITH CTE(Field1) AS (
SELECT Field1 FROM ID_71600_LANG_1 
WHERE OBJECT_ID = 1294509 AND Field7 ='TRADING PARTNER' AND Field5 LIKE 'Optional%'
EXCEPT
SELECT Field1 FROM ID_71600_LANG_1 
WHERE OBJECT_ID = 1294509 AND Field7 ='TRADING PARTNER' AND Field5 NOT LIKE 'Optional%')
SELECT COUNT(DISTINCT TBL.Field1)
FROM CTE AS TBL 
INNER JOIN OBJECT AS OBJ1 ON OBJ1.OBJECT_ID = TBL.Field1  AND OBJ1.OBJECT_OBJCLASS_ID = 256 
INNER JOIN USERATTRIB AS U1 ON OBJ1.OBJECT_ID = U1.OBJECT_ID AND U1.ID_79600_LANG_1 = 1

suba.anand
Starting Member

8 Posts

Posted - 2011-07-26 : 05:58:05

hi.. can you post the query plan?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources