Link to home
Create AccountLog in
Avatar of kinton
kinton

asked on

How to Select and make use of the indexes

Hi all,

I'm trying to find if its possible to utilise the indexes so my query doesnt spend 2 hours sorting itself before rows are returned.

If I run this, rows are returned instantly;
SELECT  subs.ID, subs.customer, subs.contract,  subs.ref, subs.sequence FROM subs

if I add this it orders it first before returning the rows;
order by   subs.ref, subs.sequence

However, if I add this rows are returned instantly because there is an index for that order;
order by subs.customer, subs.contract,  subs.ref, subs.sequence

This is where i get a bit stuck.  I can't seem to link the table to itself and still have it return rows instantly.  It orders the linked table without using the indexes so goes back to taking 2 hours before returning any rows.
SELECT     subs.subscription_id, renewals.subscription_ID FROM subscription.subscription subs
LEFT OUTER JOIN subscription.subscription AS renewals ON subs.subscription_ref = renewals.subscription_ref AND subs.subscription_sequence + 1 = renewals.subscription_sequence
order by subs.subscription_customer, subs.subscription_magazine,  subs.subscription_ref, subs.subscription_sequence

To make matters worse I actually need to link it to itself twice.
I need to bring in fields from the rows in the table for the 1st in the sequence and the next in the sequence.  Example;
I'll use just the ID for what I need to include, but there are actually 4 fields so I can't use a sub query.
ID,      customer,      ref, sequence, acquisitionID, renewalID
25,     myCompany,  pro1,  1,               25,               340
340,   myCompany,  pro1,  2,               25,               700
700,   myCompany,  pro1,  3,               25,               1020
1020, mycompany,  pro1,  4,               25,               NULL

My current SQL for this is as follows;
SELECT      subs.subscription_id FROM         subs LEFT OUTER JOIN
   subs AS renewals ON subs.subscription_ref = renewals.subscription_ref AND
   subs.subscription_sequence = renewals.subscription_sequence LEFT OUTER JOIN
   subs AS acquisitions ON subs.subscription_ref = acquisitions.subscription_ref AND
   subs.subscription_acquisition_sequence = acquisitions.subscription_sequence AND acquisitions.subscription_term_type = 'ACQUISITION' ORDER BY subs.subscription_customer, subs.subscription_magazine, subs.subscription_ref, subs.subscription_sequence

Any help would be much appreciated.

Regards,
Kinton
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Maybe on a slight sidetrack, if angelIII's query sorts you out, but is worth bearing in mind....

You can force which index should be used for the various tables in a query using index hints.

e.g.
SELECT *
FROM SomeTable WITH (INDEX(IX_SomeTableIndex1))
WHERE SomeField = 'blah'

Depending on the complexity of the query and/or the quantity of data, index hints can be your best friend. However, if you use them unnecessarily without careful performance testing, then they can also have a negative impact on performance if you get them wrong.

Note that to get a true impression of performance tuning, you should clear down the procedure/data cache with each attempt using:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

This way, you will get a more accurate impression of whether a change to a query is truly more optimal.
(Recommend you do this on a dev server, not a production server as it will clear out any cached execution plans etc)
Avatar of kinton
kinton

ASKER

Sorry adathelad I didn't refresh before I accepted Angelll's anwer (which worked!  I also understand why it worked which is good).

However, I think i'll try out your suggestions anyway as I can see that being very helpful!  I also hadn't used the clearing cache methods in a while so thank you for the timely reminder there too!

Thanks again Angelll for your help.

Regards,
Kinton