3

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?

Prezes Łukasz
  • 938
  • 1
  • 9
  • 30
  • also tell us the columns that are in your clustered indexes. By default they would be your primary keys, but maybe you don't have the defaults? We can't really infer anything from auto-generated cluster names :P – Kritner Jun 15 '15 at 15:08
  • Clustered indexes are on primary keys: Id in test table and Id on test2 table. – Prezes Łukasz Jun 15 '15 at 15:11

3 Answers3

4

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.

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
3

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.

James Z
  • 12,209
  • 10
  • 24
  • 44
1

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

Kritner
  • 13,557
  • 10
  • 46
  • 72
  • I have defined foreign keys and adding a nonclustered index on Dane5, but estimated subtreecost is worse than before, before: 4,96352, after: 4,9659 why? – Prezes Łukasz Jun 15 '15 at 15:24
  • well... "estimated" is in the name of the metric - don't necessarily take it as anything more than that. Indeces can be pretty tricky - they can help some queries while hurting others. I've always looked at them as more of an art than science, though there is definitely science involved. Just takes testing and tweaking. Maybe that's just me though :D – Kritner Jun 15 '15 at 15:37