RE: [SQLDownUnder] INNNER JOIN?

[Victor . Isakov]

Don't forget that an explicit DISTINCT potentially involves another step
by the optimizer, so generally I would only use it only where required.
DISTINCT operations are a special type of GROUP BY. This then depends on
the number of duplicates you expect to get (density), etc. But generally
I avoid DISTINCTs unless there is a real need to use them.

But there is no need, strictly speaking. 

Generally the parser will translate the IN into a whole bunch of ORs and
(I assume, memory is shocking) would get rid of duplicate search
arguments (SARGs). But in this case it's a sub-query, so it won't know
the list until run-time. So it will have to generate a hashtable, etc in
TEMPDB. Now if your subquery generates 1 million records but only with a
domain of 1..5 you might be tempted to get rid of the duplicates by
using DISTINCT, but then again it will use a B-Tree to get to the
initial values quicker so there's no need. You'd have an extra step
being performed and you would save space, but it still might not be
worth it.

So it's a trade-off between space and speed.

I think ultimately the answer depends on whether you have an index on
the column in question. If so, you might consider using a DISTINCT if
you have a high density as it should not impact performace too much
doing the distinct step. Otherwise it might not be worth it.

I think it's very much a suck-and-see exercise...

It will be interesting to see other's comments on this.


Cheers,

Victor
-----Original Message-----
From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
[mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Junying
Xue
Sent: Wednesday,9 March 2005 1:52 PM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: RE: [SQLDownUnder] INNNER JOIN?

Thanks Greg!  I just learned one more thing about SQL Server.

One more thing, do I need to use "distinct" in the sub-query if I use
"not in"?

-----Original Message-----
From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
[mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Greg Low
Sent: Wednesday, 9 March 2005 1:41 PM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: RE: [SQLDownUnder] INNNER JOIN?

Absolutely. Not In requires a list to be created in the sub-query. It
would be nice if the optimiser recognised it and made it the same but I
wouldn't want to depend on that behaviour.

EXISTS can exit as soon as anything that proves the sub-query is found. 

Another little note as well: In SQL Server, you don't need to specify a
column in the select statement in an EXISTS clause ie: it's the one
situation where SELECT * is considered "good form", unlike what occurs
in other databases.

Regards,

Greg

-----Original Message-----
From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
[mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Junying
Xue
Sent: Wednesday, 9 March 2005 12:20 PM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: RE: [SQLDownUnder] INNNER JOIN?

I know for sure that there is a performance difference between "not in"
and "not exists" in Oracle.  How about in SQL Server?

-----Original Message-----
From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
[mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of William
Luu
Sent: Wednesday, 9 March 2005 11:25 AM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: Re: [SQLDownUnder] INNNER JOIN?

Thanks Aaron, that worked.

On Wed, 9 Mar 2005 11:16:06 +1100, Aaron Bull <aaron@xxxxxxxxxxxx>
wrote:
<snip /> 
> select ItemID
> from Items
> where
> ItemID not in (select distinct FK_ItemID from Products)
> 
> Cheers,
> 
> Aaron
>


to unsubscribe to this list, please send a message back to the list with
'unsubscribe' as the subject. Powered by mailenable.com - List managed
by http://www.readify.net





to unsubscribe to this list, please send a message back to the list with
'unsubscribe' as the subject. Powered by mailenable.com - List managed
by http://www.readify.net




to unsubscribe to this list, please send a message back to the list with
'unsubscribe' as the subject. Powered by mailenable.com - List managed
by http://www.readify.net





to unsubscribe to this list, please send a message back to the list with
'unsubscribe' as the subject. Powered by mailenable.com - List managed
by http://www.readify.net






This message and any attachment is confidential and may be privileged or otherwise protected from disclosure.  If you have received it by mistake please let us know by reply and then delete it from your system; you should not copy the message or disclose its contents to anyone.

==============================================================================



to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net