July 9, 2003 at 7:15 am
I am quite new to indexes so bear with me if some things sound a little wrong.
I have two tables, tblLine and tblCall.
In tblCall, I have a clustered index on CALL_ID and a non-clustered index on ANUMBER. When I do a search on ANUMBER in this table it takes about a second (apx 6.2 million records in table).
What I want to do though is link the call table to the Line table and then do a search on SITE_FK (a field in tblLine). SITE_FK is a non-clusterd index, as is the corresponding ANUMBER field in the Line table. When I run the following query,
select site_fk, b.* from tblline a
inner join tblcall b
on a.anumber = b.anumber
where a.site_fk = 52
It takes nearly 47 seconds to retrieve the results. It is doing a Clustered Index Scan on tblCall, from my research I find this is similiar to scanning every line in the Call table. I would have thought it would find the ANUMBER for the SITE_FK and then do a bookmark lookup on that anumber.
Anyone got any ideas what i'm doing wrong.
Help much appreciated.
Paul
July 9, 2003 at 3:06 pm
Have you any indices on tblLine (on site_fk for example :-)?
How UNIQUE are the different sites in the tblLine? I.e how many DISTINCT site_fk's do you have compared to the total amount of rows in the table?
How does the query plan look like? Have you tried to force your query to use an index on site_fk in tblLine? If everything else fails and the density of column site_fk is rather low, try to do your join with(SELECT anumber FROM tblLine). Ex:
SELECT 52, b.*
FROM (SELECT anumber FROM tblLine WHERE site_fk = 52) a
INNER JOIN
tblcall b
ON a.anumber = b.anumber
Otherwise, please ask more 🙂
Regards, Hans!
Edited by - HansLindgren on 07/09/2003 3:07:01 PM
July 10, 2003 at 6:57 am
The optimizer is choosing the best plan in your case as your query has multiple column search (a.anumber and a.site_FK). From your writing, tblline a does not have any composite index on the columns you are joining or filtering on. Another reason for the Clustered Index scan is when the join retrieves the records then the Where clause is applied and hence the usage of site_FK index. If the table "tblcall" does not have any index on the column b.anumber which you are joining the query may take longer. Try adding an Index on the joining column on b and consider using a composite Index on tablline a.
Good Luck
-Sravan
July 10, 2003 at 7:09 am
There is an index on site_fk in tblline.
I tried your query but it displayed the same execution plan as with my query. It uses the site_fk index to find the anumbers, and then does a clustered index scan on the call table. It then creates a hash table and matches them up. I'm pretty sure this is why it takes so long as it has to compare against the whole call table.
What I would rather it do is find the anumbers in tblline for the site specified and then look them up in the call table, the bookmark lookup on the anumber index in tblcall is nearly instantenous.
Thanks for your help and more would be much appreciated.
Paul
July 10, 2003 at 7:18 am
Hi skasarla,
I tried the composite index on tblline and the same, as I've just posted above the plan is the same as it was before.
All the fields I join on and use are indexed.
If you have any more ideas they will be much appreciated.
Paul
July 10, 2003 at 7:58 am
Paul, I have tried to reproduce your scenario and the Optimizer should have chosen to use "Clustered Index Seek". I did the similar query with tables of 12.5 M and 8.5 M records and very large in size. I am not sure your optimizer is using Clustered Index Scan.. By the way, you can try adding statistics on the table a with two columns involved in the query. Nothing helps then try updating stastics on both tables and query again.
Good Luck
-Sravan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply