Optimize the execution of select

Question

I want optimize this select:

Select Dane1, Dane5, Dane6, Dane7 FROM Test
INNER JOIN Test2 ON Test.Id=Test2.IdTest
WHERE Dane5 > 199850

My database has 2 tables test, test2:

test design: Id int ->PRIMARY KEY, Dane1 int, Dane2 int, Dane3 int, Dane4 int, Dane5 int,

test2 design: Id int ->PRIMARY KEY, Dane6 int, Dane7 int, IdTest int,

Default index: PK__test__7C8480AE(Clustered), PK__test2__7E6CC920(Clustered)

The question is: Which indexes to attach or remove?

Answer 1

There's few things to consider when creating indexes, for example in this case:

• How many rows have Dane5 > 199850 and how many rows there are in total?
• Are there a lot of updates to the columns in the index -> slowness to updates.
• Are there going to be a lot of key lookups to the base table to get the rest of the columns needed in the query?

You could try something like this:

Create index test_xxx on test (Dane5) include (Dane1)

Weather to include Dane1 depends on how much rows there are and if key lookups are causing issues

Id is already included since it's the clustered index

Create index test2_yyy on test2 (IdTest) include (Dane6, Dane7)

Weather to have Dane6 and Date7 as included columns depends also here on the total amount of key lookups that needs to be done to the table to get them

You should turn on statistics io to see what causes the most logical reads, and weather to have the included columns in the indexes are needed or not.

Answer 2

It's always a good idea to define foreign-key relationships. On that way you keep data integrity and you can specify what happens if a parent record is deleted(f.e. delete children recursively).

A foreign-key is also a good candidate for an index to lookup the child-records quickly.

Answer 3

As Tim pointed out, foreign keys and an index defined on the foreign key is a good call.

One additional index that could get you some additional speed - assuming your where clause is always going to be on Dane5 - is to add a nonclustered index on Dane5