|
|
Posted by Erland Sommarskog on 04/21/07 12:28
Jimbo (jim.ferris@motorola.com) writes:
> I have a query..if you look at the bottom of the where clause you'll
> see an "NOT IN" statement that is really hanging up the query..i'm
> trying to replace with a "NOT EXISTS" but it isnt appearing to
> work...I need to get a result set where the email address of the
> outter most query is not in that sub query...thanks:
It's a little unclear what you ask for. Your subject line talks about
performance, but the question more sounds like you have problems to get
the desired result.
In any case, without knowledge of the tables, it's difficult to say what
you need to do to get the right result, and for advice performance I would
need to know indexes and indiciation of table sizes. However, the initial
DISTINCT is an indication of that you have insufficient join conditions.
Or that you are just using it as a matter of routine. DISTINCT is something
which in my experience there rarely is a need for. But since DISTICT calls
for a sorting operation, it can be costly.
Another reflection:
> CASE WHEN LEN(ISNULL(B.EXTENSION, '')) = 0 THEN '[blank]' ELSE
> B.EXTENSION END AS EXTENSION,
You could write this as :
coalesce(nullif(B.EXTENSION, ''), '[blank]') AS EXTENSION
Not that it is a performance winner, but just that it is a little briefer.
As for rewriting your NOT IN with NOT EXISTS, I would expect that to
improve performance, as they usually result in the same plan. But NOT IN
can cause undesired results when NULL is involved, and NOT EXISTS circum-
vents that. Also, you have a second DISTINCT in the subquery, and that
may be bad for performance.
Here is a rewrite of your subquery:
AND NOT EXISTS
(
SELECT *
FROM FCT_CASE G, LU_SITE_REP H
WHERE G.CASE_CONTACT = H.PERSON_ID
AND( -->RESTRICTIONS FOR RC_STATUS=CLOSR
(ISNULL(G.RC_STATUS,'') = 'CLOSR'AND ISNULL(G.SOLUTION_STATE,'1') =
'1' AND ISNULL(G.SOLUTION_ID,0) NOT IN (319852, 319716, 319825,
319775, 319776) )
-->RESTRICTION FOR RC_STATUS= OPENC
OR(ISNULL(G.RC_STATUS,'')='OPENC')
)
AND CAST(G.SITE_ID AS INT) NOT IN (909, 900, 903)
AND G.CUSTSAT_EXCLUDE_FLG <> 'Y'
AND DATEADD(dd, DATEDIFF(dd,0,G.ROW_ADDED_DTTM), 0) BETWEEN
DATEADD(dd, DATEDIFF(dd,0,getdate()-30), 0) AND
DATEADD(dd, DATEDIFF(dd, 0,getdate()-2), 0)
AND H.EMAIL_ADDR = B.EMAIL_ADDR)
I cannot vouch for that it gives the correct result.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[Back to original message]
|