Re: SQL Server performing much better?!?!

On Tue, 19 Mar 2002 18:06:44 -0800
"Christian Cabanero" <chumpboy(at)yahoo(dot)com> wrote:

> SELECT
> a.user_id, b.sample_id
> FROM
> user_company a,
> samples b,
> users c
> WHERE
> a.company_id = b.sample_manufacturer_id AND
> b.sample_state = 1 AND
> b.sample_author_id = c.user_id AND
> NOT EXISTS
> (
> SELECT
> p.territory_id
> FROM
> territories p,
> territory_ranges q,
> manufacturer_territories r
> WHERE
> r.manufacturer_id = b.sample_manufacturer_id AND
> r.assignment_flag = 2 AND
> r.territory_id = p.territory_id AND
> p.territory_id = q.territory_id AND p.type IN (1, 2)
> AND
> c.zip BETWEEN q.start_value AND q.end_value
> )

If query 1, 2 don't return rows so much and query 2 isn't slow,
using EXCEPT ALL might work faster than using NOT EXISTS.
Thus, could you show us the results of EXPLAIN ANALYZE
they'll return respectively ?

-- query 1.
SELECT
COUNT(*) -- a.user_id, b.sample_id
FROM
user_company a,
samples b,
users c
WHERE
a.company_id = b.sample_manufacturer_id AND
b.sample_state = 1 AND
b.sample_author_id = c.user_id
;

-- query 2.
SELECT
COUNT(*) -- a.user_id, b.sample_id
FROM
user_company a,
samples b,
users c
territories p,
territory_ranges q,
manufacturer_territories r
WHERE
a.company_id = b.sample_manufacturer_id AND
b.sample_state = 1 AND
b.sample_author_id = c.user_id AND
r.manufacturer_id = b.sample_manufacturer_id AND
r.assignment_flag = 2 AND
r.territory_id = p.territory_id AND
p.territory_id = q.territory_id AND
p.type IN (1, 2) AND
c.zip BETWEEN q.start_value AND q.end_value
;

Regards,
Masaru Sugawara