Join Efficieny

3 Jun 2005 1:48 PM

RTP

I have this query...(Attached below)

It does have a UNION in it and I need to expand this by probably adding even
a couple more UNION clauses. My question is whether I should do this more
efficiently using INNER and/or OUTER joins. Not sure how the efficiency Gods
might react. And I am concerned about run time. I welcome your opinions. I am
CERTAINLY not looking for anyone to re-write my SQL here. I can do that I
just need to know whether INNER and OUTER joins are more efficient than
UNIONs.

Thanks.

Here's the SQL I have so far...

SELECT    CONTACT1.ACCOUNTNO,
    CONTACT1.COMPANY,
    CONTACT1.CONTACT,
    CONTACT1.PHONE1,
    CONTACT1.STATE,
    CONTACT1.KEY1,
    CONTACT1.KEY2,
    CONTACT1.KEY5
FROM    CONTACT1,
    (SELECT    ACCOUNTNO,
        MAX(CONTHIST.LASTDATE)    MAXDATE
    FROM    CONTHIST
         WHERE USERID NOT IN ('CZUKOWSK')
         GROUP BY    ACCOUNTNO)    MAXSTAMP,
    CONTACT2
WHERE    (CONTACT1.SOURCE    IN    ('00-New',
                    '07-Unqualified'))
AND    (DATEDIFF(DAY,CONTACT1.CREATEON,GETDATE()) >= 21)
AND    (CONTACT1.KEY1        NOT IN    ('Consultant',
                    'Reseller'))
AND     (CONTACT1.ACCOUNTNO    NOT IN
        (SELECT    ACCOUNTNO
        FROM    CONTHIST
        WHERE     USERID    =    'CZUKOWSK'
        GROUP BY ACCOUNTNO))
AND    (CONTACT1.ACCOUNTNO    =    MAXSTAMP.ACCOUNTNO)
AND    (DATEDIFF(DAY,MAXSTAMP.MAXDATE,GETDATE()) >= 21)
AND    (CONTACT1.ACCOUNTNO    =    CONTACT2.ACCOUNTNO)
AND    ((CONTACT2.UDOCS    <=    5
AND    CONTACT2.UPROVS    <=    5)
OR    (CONTACT2.UDOCS    <=    5
AND    CONTACT2.UPROVS    IS    NULL)
OR    (CONTACT2.UDOCS    IS    NULL
AND    CONTACT2.UPROVS    <=    5)
OR    (CONTACT2.UDOCS    IS    NULL
AND    CONTACT2.UPROVS    IS    NULL))
UNION
SELECT    CONTACT1.ACCOUNTNO,
    CONTACT1.COMPANY,
    CONTACT1.CONTACT,
    CONTACT1.PHONE1,
    CONTACT1.STATE,
    CONTACT1.KEY1,
    CONTACT1.KEY2,
    CONTACT1.KEY5
FROM    CONTACT1,
    CONTACT2
WHERE    (CONTACT1.SOURCE    IN    ('00-New',
                    '07-Unqualified'))
AND    (DATEDIFF(DAY,CONTACT1.CREATEON,GETDATE()) >= 21)
AND    (CONTACT1.KEY1        NOT IN    ('Consultant',
                    'Reseller'))
AND     (CONTACT1.ACCOUNTNO    NOT IN
        (SELECT    ACCOUNTNO
        FROM    CONTHIST))
AND    (CONTACT1.ACCOUNTNO    =    CONTACT2.ACCOUNTNO)
AND    ((CONTACT2.UDOCS    <=    5
AND    CONTACT2.UPROVS    <=    5)
OR    (CONTACT2.UDOCS    <=    5
AND    CONTACT2.UPROVS    IS    NULL)
OR    (CONTACT2.UDOCS    IS    NULL
AND    CONTACT2.UPROVS    <=    5)
OR    (CONTACT2.UDOCS    IS    NULL
AND    CONTACT2.UPROVS    IS    NULL))

3 Jun 2005 2:23 PM

Cowboy (Gregory A. Beamer) - MVP

I have nothing specific, but here are a few notes to think about:

1. You are asking the same basic question twice. The only major difference I
see is you are either concerned with a date (query 1) or not (query 2). Why
not set up an OR condition and ask the question once instead of twice, or
more?

2. You are already joining tables, although you are using an Oracle-like
syntax, so the question of whether to join or not is a moot point. The
question is "can I join more efficiently"; I would venture a "yes" on this
one.

3. You are setting up a filter as a table in query 1. While there are
reasons to do this, I do not see the question of the max of lastdate is a
reasonable reason to create a "temp table" in memory when it can be covered
in an OR in your filter (with much greater efficiency).

The easiest way to write a query is to break it down. First, what do you
want to return; this is your SELECT list. Second, what tables are necessary
to get that answer (CONTACT1, CONTACT2 and CONTHIST, it appears); JOIN those
tables. Third, what needs to be filtered out of the results. Finally, do you
need to order or group the results. From here, you take a look at the query
as a whole to determine if you can get better perf by using subqueries,
unions, etc., but I would consider each section separately to get the
original answer. In most cases, it is more maintainable to do it this way
and, in many, it is just as, if not more, efficient to write it this way.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

Show quoteHide quote

"RTP" wrote:

> I have this query...(Attached below)
>
> It does have a UNION in it and I need to expand this by probably adding even
> a couple more UNION clauses. My question is whether I should do this more
> efficiently using INNER and/or OUTER joins. Not sure how the efficiency Gods
> might react. And I am concerned about run time. I welcome your opinions. I am
> CERTAINLY not looking for anyone to re-write my SQL here. I can do that I
> just need to know whether INNER and OUTER joins are more efficient than
> UNIONs.
>
> Thanks.
>
> Here's the SQL I have so far...
>
> SELECT    CONTACT1.ACCOUNTNO,
>     CONTACT1.COMPANY,
>     CONTACT1.CONTACT,
>     CONTACT1.PHONE1,
>     CONTACT1.STATE,
>     CONTACT1.KEY1,
>     CONTACT1.KEY2,
>     CONTACT1.KEY5
> FROM    CONTACT1,
>     (SELECT    ACCOUNTNO,
>         MAX(CONTHIST.LASTDATE)    MAXDATE
>     FROM    CONTHIST
>          WHERE USERID NOT IN ('CZUKOWSK')
>          GROUP BY    ACCOUNTNO)    MAXSTAMP,
>     CONTACT2
> WHERE    (CONTACT1.SOURCE    IN    ('00-New',
>                     '07-Unqualified'))
> AND    (DATEDIFF(DAY,CONTACT1.CREATEON,GETDATE()) >= 21)
> AND    (CONTACT1.KEY1        NOT IN    ('Consultant',
>                     'Reseller'))
> AND     (CONTACT1.ACCOUNTNO    NOT IN
>         (SELECT    ACCOUNTNO
>         FROM    CONTHIST
>         WHERE     USERID    =    'CZUKOWSK'
>         GROUP BY ACCOUNTNO))
> AND    (CONTACT1.ACCOUNTNO    =    MAXSTAMP.ACCOUNTNO)
> AND    (DATEDIFF(DAY,MAXSTAMP.MAXDATE,GETDATE()) >= 21)
> AND    (CONTACT1.ACCOUNTNO    =    CONTACT2.ACCOUNTNO)
> AND    ((CONTACT2.UDOCS    <=    5
> AND    CONTACT2.UPROVS    <=    5)
> OR    (CONTACT2.UDOCS    <=    5
> AND    CONTACT2.UPROVS    IS    NULL)
> OR    (CONTACT2.UDOCS    IS    NULL
> AND    CONTACT2.UPROVS    <=    5)
> OR    (CONTACT2.UDOCS    IS    NULL
> AND    CONTACT2.UPROVS    IS    NULL))
> UNION
> SELECT    CONTACT1.ACCOUNTNO,
>     CONTACT1.COMPANY,
>     CONTACT1.CONTACT,
>     CONTACT1.PHONE1,
>     CONTACT1.STATE,
>     CONTACT1.KEY1,
>     CONTACT1.KEY2,
>     CONTACT1.KEY5
> FROM    CONTACT1,
>     CONTACT2
> WHERE    (CONTACT1.SOURCE    IN    ('00-New',
>                     '07-Unqualified'))
> AND    (DATEDIFF(DAY,CONTACT1.CREATEON,GETDATE()) >= 21)
> AND    (CONTACT1.KEY1        NOT IN    ('Consultant',
>                     'Reseller'))
> AND     (CONTACT1.ACCOUNTNO    NOT IN
>         (SELECT    ACCOUNTNO
>         FROM    CONTHIST))
> AND    (CONTACT1.ACCOUNTNO    =    CONTACT2.ACCOUNTNO)
> AND    ((CONTACT2.UDOCS    <=    5
> AND    CONTACT2.UPROVS    <=    5)
> OR    (CONTACT2.UDOCS    <=    5
> AND    CONTACT2.UPROVS    IS    NULL)
> OR    (CONTACT2.UDOCS    IS    NULL
> AND    CONTACT2.UPROVS    <=    5)
> OR    (CONTACT2.UDOCS    IS    NULL
> AND    CONTACT2.UPROVS    IS    NULL))
>
>
>
>
>
>
>

5 Jun 2005 5:09 PM

Brian Selzer

I have experienced that using IN (SELECT ...) performs worse than using an
inner join. I think that the optimizer chooses an INNER LOOP JOIN when you
use IN (SELECT...), whereas the JOIN syntax enables the optimizer to use hash
or merge joins.

NOTE: I haven't seen slowdowns when using IN (SELECT TOP 1 ...)

Show quoteHide quote

"RTP" wrote:

> I have this query...(Attached below)
>
> It does have a UNION in it and I need to expand this by probably adding even
> a couple more UNION clauses. My question is whether I should do this more
> efficiently using INNER and/or OUTER joins. Not sure how the efficiency Gods
> might react. And I am concerned about run time. I welcome your opinions. I am
> CERTAINLY not looking for anyone to re-write my SQL here. I can do that I
> just need to know whether INNER and OUTER joins are more efficient than
> UNIONs.
>
> Thanks.
>
> Here's the SQL I have so far...
>
> SELECT    CONTACT1.ACCOUNTNO,
>     CONTACT1.COMPANY,
>     CONTACT1.CONTACT,
>     CONTACT1.PHONE1,
>     CONTACT1.STATE,
>     CONTACT1.KEY1,
>     CONTACT1.KEY2,
>     CONTACT1.KEY5
> FROM    CONTACT1,
>     (SELECT    ACCOUNTNO,
>         MAX(CONTHIST.LASTDATE)    MAXDATE
>     FROM    CONTHIST
>          WHERE USERID NOT IN ('CZUKOWSK')
>          GROUP BY    ACCOUNTNO)    MAXSTAMP,
>     CONTACT2
> WHERE    (CONTACT1.SOURCE    IN    ('00-New',
>                     '07-Unqualified'))
> AND    (DATEDIFF(DAY,CONTACT1.CREATEON,GETDATE()) >= 21)
> AND    (CONTACT1.KEY1        NOT IN    ('Consultant',
>                     'Reseller'))
> AND     (CONTACT1.ACCOUNTNO    NOT IN
>         (SELECT    ACCOUNTNO
>         FROM    CONTHIST
>         WHERE     USERID    =    'CZUKOWSK'
>         GROUP BY ACCOUNTNO))
> AND    (CONTACT1.ACCOUNTNO    =    MAXSTAMP.ACCOUNTNO)
> AND    (DATEDIFF(DAY,MAXSTAMP.MAXDATE,GETDATE()) >= 21)
> AND    (CONTACT1.ACCOUNTNO    =    CONTACT2.ACCOUNTNO)
> AND    ((CONTACT2.UDOCS    <=    5
> AND    CONTACT2.UPROVS    <=    5)
> OR    (CONTACT2.UDOCS    <=    5
> AND    CONTACT2.UPROVS    IS    NULL)
> OR    (CONTACT2.UDOCS    IS    NULL
> AND    CONTACT2.UPROVS    <=    5)
> OR    (CONTACT2.UDOCS    IS    NULL
> AND    CONTACT2.UPROVS    IS    NULL))
> UNION
> SELECT    CONTACT1.ACCOUNTNO,
>     CONTACT1.COMPANY,
>     CONTACT1.CONTACT,
>     CONTACT1.PHONE1,
>     CONTACT1.STATE,
>     CONTACT1.KEY1,
>     CONTACT1.KEY2,
>     CONTACT1.KEY5
> FROM    CONTACT1,
>     CONTACT2
> WHERE    (CONTACT1.SOURCE    IN    ('00-New',
>                     '07-Unqualified'))
> AND    (DATEDIFF(DAY,CONTACT1.CREATEON,GETDATE()) >= 21)
> AND    (CONTACT1.KEY1        NOT IN    ('Consultant',
>                     'Reseller'))
> AND     (CONTACT1.ACCOUNTNO    NOT IN
>         (SELECT    ACCOUNTNO
>         FROM    CONTHIST))
> AND    (CONTACT1.ACCOUNTNO    =    CONTACT2.ACCOUNTNO)
> AND    ((CONTACT2.UDOCS    <=    5
> AND    CONTACT2.UPROVS    <=    5)
> OR    (CONTACT2.UDOCS    <=    5
> AND    CONTACT2.UPROVS    IS    NULL)
> OR    (CONTACT2.UDOCS    IS    NULL
> AND    CONTACT2.UPROVS    <=    5)
> OR    (CONTACT2.UDOCS    IS    NULL
> AND    CONTACT2.UPROVS    IS    NULL))
>
>
>
>
>
>
>