SQL Server Partition Performance Issue

Question

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!

usr · Accepted Answer · 2013-02-21 14:59:13Z

2

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)).

edited Feb 21, 2013 at 14:59

answered Feb 21, 2013 at 12:33

usr

172k38 gold badges251 silver badges380 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ypercubeᵀᴹ Over a year ago

You mean A (Type, ID). Column Type is in table A.

Collectives™ on Stack Overflow

SQL Server Partition Performance Issue

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related