Optimizing selection execution
I want to optimize this selection:
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,
design test2: Id int -> PRIMARY KEY, Dane6 int, Dane7 int, IdTest int,
Default index: PK__test__7C8480AE (clustered), PK__test2__7E6CC920 (clustered)
Q: Which indexes to attach or remove?
source to share
When creating indexes, for example, there are several things to consider in this case:
- How many lines does Dane5> 199850 have and how many lines does it have?
- There are many updates for columns in the index -> slowness for updates.
- Will there be many key searches in the base table to get the rest of the columns needed in the query?
You can try something like this:
Create index test_xxx on test (Dane5) include (Dane1)
The weather for including Dane1 depends on how many rows there are and if the key searches are causing problems.
Id is already included because the clustered index
Create index test2_yyy on test2 (IdTest) include (Dane6, Dane7)
The weather in which Dane6 and Date7 are included in the included columns also depends on the total number of key queries that need to be done on the table to get them
You have to enable io statistics to see what triggers the most logical reads, and you need weather to include included columns in indexes.
source to share
It is always helpful to define foreign key relationships . This way you preserve data integrity and you can specify what happens if the parent record is deleted (for example, recursively delete child elements).
A foreign key is also a good candidate for index for quickly finding child records.
source to share