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)