CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Mustafa
Mustafa

Reputation: 147

Join on columns with different indexes

I am using a query the join columns one has a clustered index and the other has a non-clustered index. The query is taking a long time. Is this the reason that I am using different type of indexes ?

SELECT @NoOfOldBills = COUNT(*)
FROM Billing_Detail D, Meter_Info m, Meter_Reading mr
WHERE D.Meter_Reading_ID = mr.id
     AND m.id = mr.Meter_Info_ID
     AND m.id = @Meter_Info_ID
     AND BillType = 'Meter'

IF (@NoOfOldBills > 0) BEGIN

     SELECT TOP 1 @PReadingDate = Bill_Date
     FROM Billing_Detail D, Meter_Info m, Meter_Reading mr
     WHERE D.Meter_Reading_ID = mr.id
          AND m.id = mr.Meter_Info_ID
          AND m.id = @Meter_Info_ID
          AND billtype = 'Meter'
     ORDER BY Bill_Date DESC

END

Upvotes: 0

Views: 126

Answers (3)

Ian P
Ian P

Reputation: 1724

OK there are a number of things here. First, are the indexes you have relevant (or as relevant as they can be). There should be a clustered index on each table - a non clustered index does not work effectively if the table does not have a clustered index which non clustered indexes use to identify rows.

Does your index cover only one column? SQL Server uses indexes with the order of the columns for that index being very important. The left most column of the index should (in general) be the column that has the greatest ordinality (divides the data into the smallest amounts) Do either of the indexes cover all the columns referred to in the query (this is known as a covering index (Google this for more info).

In general indexes should be wide, if SQL Server has an index on col1, col2, col3, col4 and another on col1, col2 the later is redundant, as the information from the second index is fully contained in the first and SQL Server understands this.

Are you statistics up to date? SQL Server can / will choose a bad execution plan if the statistics are not up to date. What does query analyser show for plan execution (SSMS Query | show execution plan)?

Upvotes: 0

John Tseng
John Tseng

Reputation: 6352

The reason is not because you have different types of indices. Since you said you have clustered indices on all primary keys, you should be fine there. To support this query, you would also need an index with two columns on BillType and Bill_Date to cut down the time.

Hopefully they are in the same table. Otherwise, you may need a few indices to finally create one that does have two columns.

Upvotes: 0

Charles Anthony
Charles Anthony

Reputation: 3155

Without knowing more details and the context, it's tricky to advise - but it looks like you are trying to find out the date of the oldest bill. You could probably rewrite those two queries as one, which would improve the performance significantly (assuming that there are some old bills).

I would suggest something like this - which in addition to probably performing better, is a little easier to read!

SELECT count(d.*) NoOfOldBills, MAX(d.Billing_Date) OldestBillingDate FROM Billing_Detail d
  INNER JOIN Meter_Reading mr ON  mr.id=d.Meter_Reading_ID
  INNER JOIN Meter_Info m ON m.Id=mr.Meter_Info_ID
WHERE
  m.id = @Meter_Info_ID AND billtype = 'Meter'

Upvotes: 1

Related Questions