Re: SQL Server performing much better?!?!

On Tue, 19 Mar 2002 18:06:44 -0800
"Christian Cabanero" <chumpboy@yahoo.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