WHERE NOT IN very slow query? need help

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 2000 Forums

Transact-SQL (2000)

WHERE NOT IN very slow query? need help

Author

Topic

matlas
Starting Member

2 Posts

Posted - 2006-03-03 : 11:57:20

This query is running on Postgres 8.0.3 on RedHat

If anybody believes they can help optimize this SQL statement to run in a more efficient manner, please help! This query's objective is to return the # of name_ids in "fa_name" that are not in fa_checked or fa_skipped.

The slowdown happens due to the WHERE NOT IN statment, I believe.

SELECT count(*) as queue_size
FROM fa_name fan

WHERE name_id NOT IN
(
SELECT name_id
FROM fa_checked fac
WHERE fac.who = 'someguy'
)
AND name_id NOT IN
(
SELECT name_id
FROM fa_skipped fas
WHERE fas.user_name = 'someguy'
)
AND fan.who != 'someguy'
AND fan.deleted IS FALSE

This is what I get when I do EXPLAIN to see the excution plan:

"Seq Scan on fa_name fan (cost=1.47..193680.97 rows=1957 width=86)"
" Filter: (((who)::text <> 'someguy'::text) AND (deleted IS FALSE) AND (NOT (subplan)) AND (NOT (subplan)))"
" SubPlan"
" -> Materialize (cost=1.47..1.50 rows=3 width=8)"
" -> Seq Scan on fa_skipped (cost=0.00..1.46 rows=3 width=8)"
" Filter: ((user_name)::text = 'someguy'::text)"
" -> Seq Scan on fa_checked (cost=0.00..35.23 rows=417 width=8)"
" Filter: ((who)::text = 'someguy'::text)"

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-03 : 12:59:52

This is a SQL Server forum, so answers will be in T-SQL and not the language that you are using. But hopefully they'll still work.

It might be faster to use LEFT OUTER JOIN instead of WHERE NOT IN.


SELECT count(*) as queue_size
FROM fa_name fan 
LEFT OUTER JOIN fa_checked fac
ON fan.name_id = fac.name_id
LEFT OUTER JOIN fa_skipped fas
ON fan.name_id = fas.name_id
WHERE 
	fac.who = 'someguy' AND
	fas.[user_name] = 'someguy' AND
	fan.who != 'someguy' AND
	--fan.deleted IS FALSE AND
	fac.name_id IS NOT NULL AND
	fas.name_Id IS NOT NULL

Tara Kizer
aka tduggan

matlas
Starting Member

2 Posts

Posted - 2006-03-03 : 14:00:44

this is what I ended up using:

SELECT
count(*) as query_size
FROM
fa_name fan
LEFT JOIN
fa_checked fac
ON
fan.name_id = fac.name_id
AND
fac.who = 'someguy'
LEFT JOIN
fa_skipped fas
ON
fan.name_id = fas.name_id
AND
fas.user_name = 'someguy'

WHERE
fan.who != 'someguy'
AND
fan.deleted IS FALSE
AND
fac.name_id Is NULL
AND
fas.name_id IS NULL
ORDER BY fan.latn ASC
LIMIT 1

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-03 : 14:02:33

This code may be faster by combining the subquery result set with a union.


SELECT
	count(*) as queue_size
FROM
	fa_name fan
WHERE
	fan.name_id NOT IN
	(
	-- Combine subquery results with a union to
	-- avoid checking each outer row against
	-- 2 result sets.
	SELECT
		fac.name_id
	FROM
		fa_checked fac
	WHERE
		fac.who = 'someguy'
	union
	SELECT
		fas.name_id
	FROM
		fa_skipped fas
	WHERE
		fas.user_name = 'someguy'
	)
	AND fan.who != 'someguy'
	AND fan.deleted IS FALSE

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources