1

I have a multi-join SQL query.

SELECT A.COMPANYEMAIL, A.CONTRACTNAME, 
B.CLAIMNAME, C.PARTNAME, C.PARTPRICE, 
C.DAMAGEAMOUNT, C.DAMAGEDATE
FROM CONTRACT A
JOIN CLAIM B ON A.ID=B.CONTRACTID
JOIN PART C ON B.ID=C.CLAIMID
WHERE A.COMPANYEMAIL='XYZ@ABC.COM'
ORDER BY DAMAGEDATE DESC

The ID column in each table is primary key, clustered. Each table has atleast 10M records.

Does it make sense to:

  1. Create non clustered index on B.CONTRACTID and C.CLAIMID

  2. Instead of non clustered index, create non clustered covering index for example B.CONTRACTID (INCLUDE B.CLAIMNAME) and C.CLAIMID (INCLUDE PARTNAME)?

  3. A.EMAIL column has unique index- what can be done to improve the WHERE clause and prevent it from having to read all pages of the table? If I add non clustered index on A.EMAIL then would that help? Or do I need INCLUDE A.ID and A.CONTRACTNAME as well?

  4. Any other indexing recommendation please?

2

2 Answers 2

1
  1. Create non clustered index on B.CONTRACTID and C.CLAIMID

No, by themselves, these are non-covering and would leave you with Key Lookup operations on a large tables. A Key Lookup is a row based operation, meaning if you feed it 10 million rows, it'll turn around and execute 10 million independent Key Lookups.

  1. Instead of non clustered index, create non clustered covering index for example B.CONTRACTID (INCLUDE B.CLAIMNAME) and C.CLAIMID (INCLUDE PARTNAME)?

Yes/No

  1. The Index on CLAIM would be good in this case, as your proposed index has a key on the column used in the where clause, and an INCLUDE on the column returned in the select statement.
  2. However, the index you're proposing on PART only has a key on CLAIMID and an INCLUDE on PARTNAME. This still leaves PARTPRICE, DAMAGEAMOUNT, and DAMAGEDATE uncovered, in need of Key Lookups to find their values. Of course, this is all the fact that you're sorting, which is going to have significant impact on the keys and includes of your indexes. More on that later.

3 A.EMAIL column has unique index. What can be done to improve the WHERE clause and prevent it from having to read all pages of the table? If I add nonclustered index on A.EMAIL then would that help? Or do I need INCLUDE A.ID and A.CONTRACTNAME as well?

Yes, you would want to try adding a NONCLUSTERED index on CONTACT, and you'd want INCLUDE for CONTRACTNAME. Including ID is not necessary, as it's implicitly included in all NONCLUSTERED indexes, since it's the CLUSTERED key. The only time you might want considering it as an INCLUDE is if there a chance the CLUSTERED index on the table may be changed in the future.

4.Any other indexing recommendation please?

An ORDER BY may throw a monkey wrinkle into every thing mentioned here. Since you're joining three tables, you basically need to decide between performance on the JOIN operations, or performance when sorting the data. You say you 10M rows in each table. Since you're only WHERE clause is on EMAIL, I would suspect that you're going to narrow down your result drastically upfront, making the ORDER By a more trivial operation. For example, if you have 5-10 rows returned by the query, that's not enough to worry about optimizing for. However, if you were to be left with 1 million rows returned, that's where you'd want to rethink your strategy.

To summarize, I'd create the below three indexes as a starting point, and test them. As with most indexing operations, without having the full data set to test, query plan and I/O stats, we can only guess these will be optimal for your situation. For now, I would not even worry about the ORDER BY without knowing how many rows this query returns, on average.

CREATE NONCLUSTERED INDEX IX_Contract_CompanyEmail
    ON Contract(CompanyEmail) INCLUDE(ContractName);

CREATE NONCLUSTERED INDEX IX_Claim_ContractID
    ON Claim(ContractID) INCLUDE(ClaimName);

CREATE NONCLUSTERED INDEX IX_Part_ClaimId
    ON Part(ClaimId) INCLUDE(PartName,PartPrice, DamageAmount,DamageDate);
2
  • The result of the SELECT (on this multi-join query) can have maximum 5000 records returned, on which the ORDER BY needs applying.
    – variable
    Feb 18 at 12:02
  • Right, so in your case, it's the trade off between performing a SCAN on 10 million rows that are already sorted by date, to then match on your JOIN conditions. Or simply sorting 5,000 rows at the end. You could always try both and see which works better. Feb 18 at 12:05
0
  1. Yes, it is good practice to index your foreign key columns.

  2. Yes, try to cover where you can. Test by adding non covering elements as included columns, or key columns.

  3. I'd focus on what is being filtered in the where.

    create nonclustered index [ix_Contract_CompanyEmail] 
    on Contract(CompanyEmail) include (ContractName)
    

    As long as A.ID is indeed a primary key the ID value will be saved within the non clustered index.

  4. You might also want to create another for the order by. I'd consider a clustered index on the Contract table clustering on DamageDate.

    create clustered index [cix_Contract_DamageDate] on Contract(DamageDate)
    

    This index will sort the data leaf pages in order with the damage date as the "key" column allowing the execution plan to omit a sort operation. This is somewhat common for transaction based tables that grow by date.

    If this table already has a primary key on it, you'll need to convert that to a non clustered primary key before you can create the clustered index.

4
  • In point 1 note that they are the foreign key column names. Do you want to edit your answer with this feedback? In point 4 what's the harm on making non clustered index?
    – variable
    Feb 18 at 3:39
  • This depends on a number of things. The main difference between the two is how data is stored. Clustered index will be stored on the table itself, and the non clustered is a new object in the DB that points records to data pages. In order to improve performance, a key concept is to design the query and index to force a seek operation. If you're going the non-clustered route, I'd start by placing the DamageDate in the first position of the key value and include the other columns in the include for covering purposes. create nonclustered inex [name] on Part(DamageDate) include (....)
    – BradG
    Feb 18 at 4:31
  • @BradG Welcome to DBA.SE. In this situation, be careful suggesting an index (CLUSTERED or NONCLUSTERD) that has a leading key column on a date column. As the OP stated, each table has 10M rows. Date is only used in the ORDER BY and SELECT. Therefore, any index with a leading key column on date would either be ignored by the query optimizer, or be fully scanned to match the the other columns used in the join. Your theory would work on simpler query, where there are not other join operations to consider. In his case, I would not expect him to have a lot of rows sort at the end. Feb 18 at 12:04
  • Thanks for the welcome.
    – BradG
    Feb 18 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.