How to I force a better execution plan when the database is forcing a join?

user_7512122 0
Viewed 926

I am optimizing a query on SQL Server 2005. I have a simple query against mytable that has about 2 million rows:

SELECT id, num
FROM mytable
WHERE t_id = 587

The id field is the Primary Key (clustered index) and there exists a non-clustered index on the t_id field.

The query plan for the above query is including both a Clustered Index Seek and an Index Seek, then it's executing a Nested Loop (Inner Join) to combine the results. The STATISTICS IO is showing 3325 page reads.

If I change the query to just the following, the server is only executing 6 Page Reads and only a single Index Seek with no join:

SELECT id
FROM mytable
WHERE t_id = 587

I have tried adding an index on the num column, and an index on both num and tid. Neither index was selected by the server.

I'm looking to reduce the number of page reads but still retrieve the id and num columns.

query-performance sql-server-2005 tsql sql-server
3 Answers

The following index should be optimal:

CREATE INDEX idx ON MyTable (t_id)
INCLUDE (num)

I cannot remember if INCLUDEd columns were valid syntax in 2005, you may have to use:

CREATE INDEX idx ON MyTable (t_id, num)

The [id] column will be included in the index as it is the clustered key.

user_93957400

The optimal index would be on (t_id, num, id).

The reason your query is probably one that bad side is because multiple rows are being selected. I wonder if rephrasing the query like this would improve performance:

SELECT t.id, t.num
FROM mytable t
WHERE EXISTS (SELECT 1
              FROM my_table t2
              WHERE t2.t_id = 587 AND t2.id = t.id
             );
user_11440350

Lets clarify the problem and then discuss on the solutions to improve it:

You have a table(lets call it tblTest1 and contains 2M records) with a Clustered Index on id and a Non Clustered Index on t_id, and you are going to query the data which filters the data using Non Clustered Index and getting the id and num columns.

So SQL server will use the Non Clustered Index to filter the data(t_id=587), but after filtering the data SQL server needs to get the values stored in id and num columns. Apparently because you have Clustered index then SQL server will use this index to obtain the data stored in id and num columns. This happens because leafs in the Non clustered index's tree contains the Clustered index's value, this is why you see the Key Lookup operator in the execution plan. In fact SQL Server uses the Index seek(NonCluster) to find the t_id=587 and then uses the Key Lookup to get the num data!(SQL Server will not use this operator to get the value stored in id column, because your have a Clustered index and the leafs in NonClustered Index contains the Clustered Index's value).

enter image description here

Referred to the above-mentioned screenshot, when we have Index Seek(NonClustred) and a Key Lookup, SQL Server needs a Nested Loop Join operator to get the data in num column using the Index Seek(Nonclustered) operator. In fact in this stage SQL Server has two separate sets: one is the results obtained from Nonclustered Index tree and the other is data inside Clustered Index tree.

Based on this story, the problem is clear! What will happen if we say to SQL server, not to do a Key Lookup? this will cause the SQL Server to execute the query using a shorter way(No need to Key Lookup and apparently no need to the Nested loop join! ).

To achieve this, we need to INCLUDE the num column inside the NonClustered index's tree, so in this case the leaf of this index will contains the id column's data and also the num column's data! Clearly when we say the SQL Server to find a data using NonClustred Index and return the id and num columns, it will not need to do a Key Lookup!

Finally what we need to do, is to INCLUDE the num in NonClustered Index! Thanks to @MJH's answer:

CREATE NONCLUSTERED INDEX idx ON tblTest1 (t_id)
INCLUDE (num)

enter image description here

Luckily, SQL Server 2005 provided a new feature for NonClustered indexes, the ability to include additional, non-key columns in the leaf level of the NonClustered indexes!

Read more:

https://www.red-gate.com/simple-talk/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns?view=sql-server-2017

But what will happens if we write the query like this?

SELECT id, num
FROM tblTest1 AS t1
WHERE 
EXISTS (SELECT 1
              FROM tblTest1 t2
              WHERE t2.t_id = 587 AND t2.id = t1.id
             )

This is a great approach, but lets see the execution plan:

enter image description here

Clearly, SQL server needs to do a Index seek(NonClustered) to find the t_id=587 and then obtain the data from Clustered Index using Clustered Index Seek. In this case we will not get any notable performance improvement.

Note: When you are using Indexes, you need to have an appropriate plan to maintain them. As the indexes get fragmented, their impact on the query performance will be decreased and you might face performance problems after a while! You need to have an appropriate plan to Reorganize and Rebuild them, when they get fragmented!

Read more: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-2017

user_16668000
Related