CODEWITHSUNDEEP
sql-server-2008indexingpartitioning
Reynier Booysen
Reynier Booysen

Reputation: 281

SQL Server Partition Performance Issue

I have the following scenario:

TableA (ID GUID, Type INT) : +60M rows
TableB (ID GUID) : +5M rows

TableA has an Index on ID and Type TableB the Primary Key is ID

I need to improve the following query:

SELECT * FROM TableA A
INNER JOIN TableB B
   ON A.ID = B.ID AND A.Type = 5

The query takes about 30 seconds to complete.

We have tried partitioning TableA on the Type column but the query execution time remains the same. Even the execution plan is still the same. As far as I understood partitioning the table should greatly improve the performance?

Do I have to adjust my query to use the partition thus increasing performance? Are my indexes wrong?

Thanks in advance!

Upvotes: 0

Views: 213

Answers (1)

usr
usr

Reputation: 171246

You are one of the people who think partitioning is a magic switch that improves performance when pressed. Partitioning mostly reduces performance and helps in a few narrow cases. It is mostly a management feature for bulk loading and data archiving/deletion.

Partitioning introduces serious consequences and cannot be done without proper understanding, planing and testing.

Create the proper indices (in your case A(Type, ID) would be a good start. Alternatively A(ID) WHERE Type = 5 (a filtered index)).

Upvotes: 2

Related Questions