Help with query rewrite

I have the following nonindexable query due to the "<>" operater. Column "id
"
in the following scenario is a clustered index.
DECLARE @.tid
SET @.tid = 1000
SELECT t.id
FROM table t
WHERE t.id <> @.tid
Is there a way to rewrite such a search condition so as to make it an
indexexable search condition?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1How large is the table? How much of the table is equal to @.tid? Is
t.id unique?
If the test is going to eliminate only a small percentage of the table
then a table scan is the fastest way to get through it. In that case
the most you can hope for is that perhaps clustering on t.id will make
a small difference.
If the test will eliminate a major percentage of the table then a
clustering on t.id would probably help.
Roy Harvey
Beacon Falls, CT
On Wed, 03 May 2006 23:04:56 GMT, "cbrichards" <u3288@.uwe> wrote:

>I have the following nonindexable query due to the "<>" operater. Column "i
d"
>in the following scenario is a clustered index.
>DECLARE @.tid
>SET @.tid = 1000
>SELECT t.id
>FROM table t
>WHERE t.id <> @.tid
>
>Is there a way to rewrite such a search condition so as to make it an
>indexexable search condition?|||If you have a clustered index on the "id" column, the index will get used in
the search, but it will be an index scan rather than a index seek(which will
be in case of =), for the simple reason that you are not trying to find one
value, you are trying to eleminate a value, so it has to compare it against
every value in the index. So I am assuming you are looking for an index see
k
rather than a scan,
You can try doing this
DECLARE @.tid int
SET @.tid = 1000
SELECT t.id
FROM table t
WHERE t.id <> @.tid
and t.id>0
I am not sure if you can make this assumption that "id" will always be > 0,
this actually runs an index seek on the table. The plan improves if you have
this inside a stored proc, as the query plan gets cached.
As Roy pointed out this can be a very expensive query without anything else
in your where clause depending on the size of the data. It almost took 31
seconds for me to run this on 11 Million records. If I was you I would look
at changing the query and including some more filtering in the where clause.
HTH
RA
"Roy Harvey" wrote:

> How large is the table? How much of the table is equal to @.tid? Is
> t.id unique?
> If the test is going to eliminate only a small percentage of the table
> then a table scan is the fastest way to get through it. In that case
> the most you can hope for is that perhaps clustering on t.id will make
> a small difference.
> If the test will eliminate a major percentage of the table then a
> clustering on t.id would probably help.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 03 May 2006 23:04:56 GMT, "cbrichards" <u3288@.uwe> wrote:
>
>|||Thanks Rocky.
The "id" column is a nonclustered composite index with another column name
"col_k". The index was created with in this order (id, col_k).
Column "id" is an identity column and column "col_k" is not very unique.
There are approximately 15,000 records in the table.
Since Column "id" is first in the composite index, it seems like it could be
used in the revised query you wrote, but perhaps the optimizer believes a
scan is still faster than using the query (and yes, I have run sp_updatestat
s)
.
I further rewrote the query to give it an extra filter in the WHERE clause:
DECLARE @.tid int
DECLARE @.colk int
SET @.tid = 1000
SET @.colk = 5
SELECT t.id
FROM table t
WHERE t.id <> @.tid
and t.id>0
and t.col_k = @.colk
However, my logical reads have not improved from the original and it is stil
l
performing a Clustered Index Scan (using the clustered index which is a
datetime field), which to me in this case, is in essence a table scan.
Any further ideas or suggestions to have it use the composite index would be
appreciated.
Rocky A wrote:[vbcol=seagreen]
>If you have a clustered index on the "id" column, the index will get used i
n
>the search, but it will be an index scan rather than a index seek(which wil
l
>be in case of =), for the simple reason that you are not trying to find one
>value, you are trying to eleminate a value, so it has to compare it against
>every value in the index. So I am assuming you are looking for an index se
ek
>rather than a scan,
>You can try doing this
>DECLARE @.tid int
>SET @.tid = 1000
>SELECT t.id
>FROM table t
>WHERE t.id <> @.tid
>and t.id>0
>I am not sure if you can make this assumption that "id" will always be > 0,
>this actually runs an index seek on the table. The plan improves if you hav
e
>this inside a stored proc, as the query plan gets cached.
>As Roy pointed out this can be a very expensive query without anything else
>in your where clause depending on the size of the data. It almost took 31
>seconds for me to run this on 11 Million records. If I was you I would look
>at changing the query and including some more filtering in the where clause
.
>HTH
>RA
>
>[quoted text clipped - 22 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||RA,
Is this your real query, or a simplified version?
If id is the only column you are selecting, then one would expect that
SQL-Server would scan the smallest index that contains the id column.
You did not post DDL, so we cannot check this.
If you are selecting other columns than the ones in the nonclustered
index and clustered index, then the nonclustered index will (most
likely) not be used, because that would require bookmark lookups for too
many rows. In that case, a clustered index scan would simply be faster.
Gert-Jan
"cbrichards via droptable.com" wrote:
> Thanks Rocky.
> The "id" column is a nonclustered composite index with another column name
> "col_k". The index was created with in this order (id, col_k).
> Column "id" is an identity column and column "col_k" is not very unique.
> There are approximately 15,000 records in the table.
> Since Column "id" is first in the composite index, it seems like it could
be
> used in the revised query you wrote, but perhaps the optimizer believes a
> scan is still faster than using the query (and yes, I have run sp_updatest
ats)
> .
> I further rewrote the query to give it an extra filter in the WHERE clause
:
> DECLARE @.tid int
> DECLARE @.colk int
> SET @.tid = 1000
> SET @.colk = 5
> SELECT t.id
> FROM table t
> WHERE t.id <> @.tid
> and t.id>0
> and t.col_k = @.colk
> However, my logical reads have not improved from the original and it is st
ill
> performing a Clustered Index Scan (using the clustered index which is a
> datetime field), which to me in this case, is in essence a table scan.
> Any further ideas or suggestions to have it use the composite index would
be
> appreciated.
> Rocky A wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1|||given the specific example, i would expect the engine to use any index
it can.
however, sending the data across the wire will be the bottleneck, so no
matter what it probably take as long as it takes to send the data.

Help with query rewrite

I have the following nonindexable query due to the "<>" operater. Column "id"
in the following scenario is a clustered index.
DECLARE @.tid
SET @.tid = 1000
SELECT t.id
FROM table t
WHERE t.id <> @.tid
Is there a way to rewrite such a search condition so as to make it an
indexexable search condition?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1How large is the table? How much of the table is equal to @.tid? Is
t.id unique?
If the test is going to eliminate only a small percentage of the table
then a table scan is the fastest way to get through it. In that case
the most you can hope for is that perhaps clustering on t.id will make
a small difference.
If the test will eliminate a major percentage of the table then a
clustering on t.id would probably help.
Roy Harvey
Beacon Falls, CT
On Wed, 03 May 2006 23:04:56 GMT, "cbrichards" <u3288@.uwe> wrote:
>I have the following nonindexable query due to the "<>" operater. Column "id"
>in the following scenario is a clustered index.
>DECLARE @.tid
>SET @.tid = 1000
>SELECT t.id
>FROM table t
>WHERE t.id <> @.tid
>
>Is there a way to rewrite such a search condition so as to make it an
>indexexable search condition?|||If you have a clustered index on the "id" column, the index will get used in
the search, but it will be an index scan rather than a index seek(which will
be in case of =), for the simple reason that you are not trying to find one
value, you are trying to eleminate a value, so it has to compare it against
every value in the index. So I am assuming you are looking for an index seek
rather than a scan,
You can try doing this
DECLARE @.tid int
SET @.tid = 1000
SELECT t.id
FROM table t
WHERE t.id <> @.tid
and t.id>0
I am not sure if you can make this assumption that "id" will always be > 0,
this actually runs an index seek on the table. The plan improves if you have
this inside a stored proc, as the query plan gets cached.
As Roy pointed out this can be a very expensive query without anything else
in your where clause depending on the size of the data. It almost took 31
seconds for me to run this on 11 Million records. If I was you I would look
at changing the query and including some more filtering in the where clause.
HTH
RA
"Roy Harvey" wrote:
> How large is the table? How much of the table is equal to @.tid? Is
> t.id unique?
> If the test is going to eliminate only a small percentage of the table
> then a table scan is the fastest way to get through it. In that case
> the most you can hope for is that perhaps clustering on t.id will make
> a small difference.
> If the test will eliminate a major percentage of the table then a
> clustering on t.id would probably help.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 03 May 2006 23:04:56 GMT, "cbrichards" <u3288@.uwe> wrote:
> >I have the following nonindexable query due to the "<>" operater. Column "id"
> >in the following scenario is a clustered index.
> >
> >DECLARE @.tid
> >SET @.tid = 1000
> >
> >SELECT t.id
> >FROM table t
> >WHERE t.id <> @.tid
> >
> >
> >Is there a way to rewrite such a search condition so as to make it an
> >indexexable search condition?
>|||Thanks Rocky.
The "id" column is a nonclustered composite index with another column name
"col_k". The index was created with in this order (id, col_k).
Column "id" is an identity column and column "col_k" is not very unique.
There are approximately 15,000 records in the table.
Since Column "id" is first in the composite index, it seems like it could be
used in the revised query you wrote, but perhaps the optimizer believes a
scan is still faster than using the query (and yes, I have run sp_updatestats)
.
I further rewrote the query to give it an extra filter in the WHERE clause:
DECLARE @.tid int
DECLARE @.colk int
SET @.tid = 1000
SET @.colk = 5
SELECT t.id
FROM table t
WHERE t.id <> @.tid
and t.id>0
and t.col_k = @.colk
However, my logical reads have not improved from the original and it is still
performing a Clustered Index Scan (using the clustered index which is a
datetime field), which to me in this case, is in essence a table scan.
Any further ideas or suggestions to have it use the composite index would be
appreciated.
Rocky A wrote:
>If you have a clustered index on the "id" column, the index will get used in
>the search, but it will be an index scan rather than a index seek(which will
>be in case of =), for the simple reason that you are not trying to find one
>value, you are trying to eleminate a value, so it has to compare it against
>every value in the index. So I am assuming you are looking for an index seek
>rather than a scan,
>You can try doing this
>DECLARE @.tid int
>SET @.tid = 1000
>SELECT t.id
>FROM table t
>WHERE t.id <> @.tid
>and t.id>0
>I am not sure if you can make this assumption that "id" will always be > 0,
>this actually runs an index seek on the table. The plan improves if you have
>this inside a stored proc, as the query plan gets cached.
>As Roy pointed out this can be a very expensive query without anything else
>in your where clause depending on the size of the data. It almost took 31
>seconds for me to run this on 11 Million records. If I was you I would look
>at changing the query and including some more filtering in the where clause.
>HTH
>RA
>> How large is the table? How much of the table is equal to @.tid? Is
>> t.id unique?
>[quoted text clipped - 22 lines]
>> >Is there a way to rewrite such a search condition so as to make it an
>> >indexexable search condition?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||RA,
Is this your real query, or a simplified version?
If id is the only column you are selecting, then one would expect that
SQL-Server would scan the smallest index that contains the id column.
You did not post DDL, so we cannot check this.
If you are selecting other columns than the ones in the nonclustered
index and clustered index, then the nonclustered index will (most
likely) not be used, because that would require bookmark lookups for too
many rows. In that case, a clustered index scan would simply be faster.
Gert-Jan
"cbrichards via SQLMonster.com" wrote:
> Thanks Rocky.
> The "id" column is a nonclustered composite index with another column name
> "col_k". The index was created with in this order (id, col_k).
> Column "id" is an identity column and column "col_k" is not very unique.
> There are approximately 15,000 records in the table.
> Since Column "id" is first in the composite index, it seems like it could be
> used in the revised query you wrote, but perhaps the optimizer believes a
> scan is still faster than using the query (and yes, I have run sp_updatestats)
> .
> I further rewrote the query to give it an extra filter in the WHERE clause:
> DECLARE @.tid int
> DECLARE @.colk int
> SET @.tid = 1000
> SET @.colk = 5
> SELECT t.id
> FROM table t
> WHERE t.id <> @.tid
> and t.id>0
> and t.col_k = @.colk
> However, my logical reads have not improved from the original and it is still
> performing a Clustered Index Scan (using the clustered index which is a
> datetime field), which to me in this case, is in essence a table scan.
> Any further ideas or suggestions to have it use the composite index would be
> appreciated.
> Rocky A wrote:
> >If you have a clustered index on the "id" column, the index will get used in
> >the search, but it will be an index scan rather than a index seek(which will
> >be in case of =), for the simple reason that you are not trying to find one
> >value, you are trying to eleminate a value, so it has to compare it against
> >every value in the index. So I am assuming you are looking for an index seek
> >rather than a scan,
> >
> >You can try doing this
> >
> >DECLARE @.tid int
> >SET @.tid = 1000
> >
> >SELECT t.id
> >FROM table t
> >WHERE t.id <> @.tid
> >and t.id>0
> >
> >I am not sure if you can make this assumption that "id" will always be > 0,
> >this actually runs an index seek on the table. The plan improves if you have
> >this inside a stored proc, as the query plan gets cached.
> >
> >As Roy pointed out this can be a very expensive query without anything else
> >in your where clause depending on the size of the data. It almost took 31
> >seconds for me to run this on 11 Million records. If I was you I would look
> >at changing the query and including some more filtering in the where clause.
> >
> >HTH
> >RA
> >
> >> How large is the table? How much of the table is equal to @.tid? Is
> >> t.id unique?
> >[quoted text clipped - 22 lines]
> >> >Is there a way to rewrite such a search condition so as to make it an
> >> >indexexable search condition?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||given the specific example, i would expect the engine to use any index
it can.
however, sending the data across the wire will be the bottleneck, so no
matter what it probably take as long as it takes to send the data.

help with joining on an index

I'm trying to speed up a query by joining on a known index. However, I've
forgotten the syntax.
Could someone please help me?
JOIN Cases C WITH(NOLOCK) ON D.DoctorID = INDEX(Cases_Doctor) <-- here!!
Thanks a million,
PatrickYou cannot join to an index. You can use an index hint, but in my
experience they are generally not very effective.
JOIN Cases C (NOLOCK INDEX=Doctor) ON D.DoctorID = C.DoctorID
Can you share more information about your situation and problem?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"news.microsoft.com" <Patrick@.hotmail.com> wrote in message
news:%232lzQsF8FHA.2600@.tk2msftngp13.phx.gbl...
> I'm trying to speed up a query by joining on a known index. However, I've
> forgotten the syntax.
> Could someone please help me?
> JOIN Cases C WITH(NOLOCK) ON D.DoctorID = INDEX(Cases_Doctor) <-- here!!
> Thanks a million,
> Patrick
>|||Hi Adam,
Thanks for responding.
I'm just getting the sum of sales per doctor for either a particular
department or all departments.
When going to product level from an order, many record scans take place; so
i'm trying to speed things up. I have an index called Cases_Doctor that I
want to explicitly use in hopes of making things go quicker. I don't expect
anyone to do my job, but here is my query:
SELECT BeginningDate = @.BeginningDate, EndingDate = @.EndingDate,
PriorBeginningDate = @.PriorBeginningDate, PriorEndingDate =@.PriorEndingDate
,
C.OriginFacility, CP.Prod_Fac, C.DoctorID, D.DoctorDisplayName,
D.WorkPhone, D.SalesPersonID,
Dollars = SUM(CASE WHEN C.DateInvoiced BETWEEN @.BeginningDate AND
@.EndingDate THEN CP.TotalCharge ELSE 0.00 END),
Units = SUM(CASE WHEN C.DateInvoiced BETWEEN @.BeginningDate AND
@.EndingDate THEN CP.Quantity ELSE 0.00 END),
PriorDollars = SUM(CASE WHEN C.DateInvoiced BETWEEN
@.PriorBeginningDate AND @.PriorEndingDate THEN CP.TotalCharge ELSE 0.00 END),
PriorUnits = SUM(CASE WHEN C.DateInvoiced BETWEEN @.PriorBeginningDate
AND @.PriorEndingDate THEN CP.Quantity ELSE 0.00 END)
FROM dim_Doctors D WITH(NOLOCK)
JOIN Cases C WITH(NOLOCK) ON D.DoctorID = INDEX(Cases_Doctor) AND
C.DateInvoiced BETWEEN @.PriorBeginningDate AND @.EndingDate
JOIN CaseProducts CP WITH(NOLOCK) ON CP.CaseID = C.CaseID AND
COALESCE(CP.Prod_Fac, '1') = COALESCE(@.ProductionFacility,
COALESCE(CP.Prod_Fac, '1'))
JOIN Products P WITH(NOLOCK) ON P.ProductID = CP.ProductID AND
COALESCE(P.ProductTypeID, '1') = COALESCE(@.ProductTypeID,
COALESCE(P.ProductTypeID, '1'))
WHERE COALESCE(C.OriginFacility, '1') = COALESCE(@.OriginFacility,
COALESCE(C.OriginFacility, '1'))
AND COALESCE(D.SalesPersonID, '1') = COALESCE(@.SalesPersonID,
COALESCE(D.SalesPersonID, '1'))
AND C.Type IN(0,1)
GROUP BY C.OriginFacility, CP.Prod_Fac, C.DoctorID, D.DoctorDisplayName,
D.WorkPhone, D.SalesPersonID
If anything jumps out as a definite no-no. Please let me know.
Thanks again for your response,
Patrick
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23fkSS4F8FHA.604@.TK2MSFTNGP10.phx.gbl...
> You cannot join to an index. You can use an index hint, but in my
> experience they are generally not very effective.
> JOIN Cases C (NOLOCK INDEX=Doctor) ON D.DoctorID = C.DoctorID
> Can you share more information about your situation and problem?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "news.microsoft.com" <Patrick@.hotmail.com> wrote in message
> news:%232lzQsF8FHA.2600@.tk2msftngp13.phx.gbl...
>|||Hey Adam,
You're exactly right (I'm starting to learn thats more than likely the
case :))
When specifying an index the query took over 2 minutes.. without it took 55
secs.
A total of 6 mons worth of sales for about 16 thousand customers will take
some time,
but I'm still hoping to get it quicker.
Patrick
"news.microsoft.com" <Patrick@.hotmail.com> wrote in message
news:u7OHBAG8FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi Adam,
> Thanks for responding.
> I'm just getting the sum of sales per doctor for either a particular
> department or all departments.
> When going to product level from an order, many record scans take place;
> so i'm trying to speed things up. I have an index called Cases_Doctor that
> I want to explicitly use in hopes of making things go quicker. I don't
> expect anyone to do my job, but here is my query:
> SELECT BeginningDate = @.BeginningDate, EndingDate = @.EndingDate,
> PriorBeginningDate = @.PriorBeginningDate, PriorEndingDate
> =@.PriorEndingDate ,
> C.OriginFacility, CP.Prod_Fac, C.DoctorID, D.DoctorDisplayName,
> D.WorkPhone, D.SalesPersonID,
> Dollars = SUM(CASE WHEN C.DateInvoiced BETWEEN @.BeginningDate AND
> @.EndingDate THEN CP.TotalCharge ELSE 0.00 END),
> Units = SUM(CASE WHEN C.DateInvoiced BETWEEN @.BeginningDate AND
> @.EndingDate THEN CP.Quantity ELSE 0.00 END),
> PriorDollars = SUM(CASE WHEN C.DateInvoiced BETWEEN
> @.PriorBeginningDate AND @.PriorEndingDate THEN CP.TotalCharge ELSE 0.00
> END),
> PriorUnits = SUM(CASE WHEN C.DateInvoiced BETWEEN
> @.PriorBeginningDate AND @.PriorEndingDate THEN CP.Quantity ELSE 0.00 END)
> FROM dim_Doctors D WITH(NOLOCK)
> JOIN Cases C WITH(NOLOCK) ON D.DoctorID = INDEX(Cases_Doctor) AND
> C.DateInvoiced BETWEEN @.PriorBeginningDate AND @.EndingDate
> JOIN CaseProducts CP WITH(NOLOCK) ON CP.CaseID = C.CaseID AND
> COALESCE(CP.Prod_Fac, '1') = COALESCE(@.ProductionFacility,
> COALESCE(CP.Prod_Fac, '1'))
> JOIN Products P WITH(NOLOCK) ON P.ProductID = CP.ProductID AND
> COALESCE(P.ProductTypeID, '1') = COALESCE(@.ProductTypeID,
> COALESCE(P.ProductTypeID, '1'))
> WHERE COALESCE(C.OriginFacility, '1') = COALESCE(@.OriginFacility,
> COALESCE(C.OriginFacility, '1'))
> AND COALESCE(D.SalesPersonID, '1') = COALESCE(@.SalesPersonID,
> COALESCE(D.SalesPersonID, '1'))
> AND C.Type IN(0,1)
> GROUP BY C.OriginFacility, CP.Prod_Fac, C.DoctorID, D.DoctorDisplayName,
> D.WorkPhone, D.SalesPersonID
> If anything jumps out as a definite no-no. Please let me know.
> Thanks again for your response,
> Patrick
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%23fkSS4F8FHA.604@.TK2MSFTNGP10.phx.gbl...
>|||SQL Server will tend to not use a non-clustered index if the lookup into the
cluster is going to be more expensive than simply doing a table scan (which
is probably what's happening in your case.) It looks like you may be able
to create a covering non-clustered index for that query.
Please read the following article, and post back here if you have any
questions:
http://www.sql-server-performance.c...ing_indexes.asp
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"news.microsoft.com" <Patrick@.hotmail.com> wrote in message
news:u7OHBAG8FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi Adam,
> Thanks for responding.
> I'm just getting the sum of sales per doctor for either a particular
> department or all departments.
> When going to product level from an order, many record scans take place;
> so i'm trying to speed things up. I have an index called Cases_Doctor that
> I want to explicitly use in hopes of making things go quicker. I don't
> expect anyone to do my job, but here is my query:
> SELECT BeginningDate = @.BeginningDate, EndingDate = @.EndingDate,
> PriorBeginningDate = @.PriorBeginningDate, PriorEndingDate
> =@.PriorEndingDate ,
> C.OriginFacility, CP.Prod_Fac, C.DoctorID, D.DoctorDisplayName,
> D.WorkPhone, D.SalesPersonID,
> Dollars = SUM(CASE WHEN C.DateInvoiced BETWEEN @.BeginningDate AND
> @.EndingDate THEN CP.TotalCharge ELSE 0.00 END),
> Units = SUM(CASE WHEN C.DateInvoiced BETWEEN @.BeginningDate AND
> @.EndingDate THEN CP.Quantity ELSE 0.00 END),
> PriorDollars = SUM(CASE WHEN C.DateInvoiced BETWEEN
> @.PriorBeginningDate AND @.PriorEndingDate THEN CP.TotalCharge ELSE 0.00
> END),
> PriorUnits = SUM(CASE WHEN C.DateInvoiced BETWEEN
> @.PriorBeginningDate AND @.PriorEndingDate THEN CP.Quantity ELSE 0.00 END)
> FROM dim_Doctors D WITH(NOLOCK)
> JOIN Cases C WITH(NOLOCK) ON D.DoctorID = INDEX(Cases_Doctor) AND
> C.DateInvoiced BETWEEN @.PriorBeginningDate AND @.EndingDate
> JOIN CaseProducts CP WITH(NOLOCK) ON CP.CaseID = C.CaseID AND
> COALESCE(CP.Prod_Fac, '1') = COALESCE(@.ProductionFacility,
> COALESCE(CP.Prod_Fac, '1'))
> JOIN Products P WITH(NOLOCK) ON P.ProductID = CP.ProductID AND
> COALESCE(P.ProductTypeID, '1') = COALESCE(@.ProductTypeID,
> COALESCE(P.ProductTypeID, '1'))
> WHERE COALESCE(C.OriginFacility, '1') = COALESCE(@.OriginFacility,
> COALESCE(C.OriginFacility, '1'))
> AND COALESCE(D.SalesPersonID, '1') = COALESCE(@.SalesPersonID,
> COALESCE(D.SalesPersonID, '1'))
> AND C.Type IN(0,1)
> GROUP BY C.OriginFacility, CP.Prod_Fac, C.DoctorID, D.DoctorDisplayName,
> D.WorkPhone, D.SalesPersonID
> If anything jumps out as a definite no-no. Please let me know.
> Thanks again for your response,
> Patrick
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%23fkSS4F8FHA.604@.TK2MSFTNGP10.phx.gbl...
>|||On Wed, 23 Nov 2005 13:59:20 -0500, news.microsoft.com wrote:
(snip)
>If anything jumps out as a definite no-no. Please let me know.
Hi Patrick,
These four comparisons are almost guaranteed performance-killers:

>COALESCE(CP.Prod_Fac, '1') = COALESCE(@.ProductionFacility,
>COALESCE(CP.Prod_Fac, '1'))
(...)
>COALESCE(P.ProductTypeID, '1') = COALESCE(@.ProductTypeID,
>COALESCE(P.ProductTypeID, '1'))
> WHERE COALESCE(C.OriginFacility, '1') = COALESCE(@.OriginFacility,
>COALESCE(C.OriginFacility, '1'))
> AND COALESCE(D.SalesPersonID, '1') = COALESCE(@.SalesPersonID,
>COALESCE(D.SalesPersonID, '1'))
These can only be satisfied by table scan or index scan (maybe a partial
index scan if some of the other filters allow it).
If you want to take maximum advantage of an index on, for instance,
P.ProductTypeID, then make sure that you write the filter as:
P.ProductTypeID = some interesting expression
This allows SQL Server to calculate the expression first, then use the
index to find the matching row(s) directly, without the need to scan
over lots of other rows and determine if they match as well.
I know too little about your tables and your data to be able to tell you
how "some interesting expression" would have to look.
If you need more assistance, then check www.aspfaq.com/5006 first.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)