asked on

Query takes a long time to return

I have a database table with about 40 columns. Of those 40 columns, 5 of them make up the primary key and a clustered index. Initially with small amounts of data, the query "Select Distinct A, B, C From TABLE Where D = 1" takes seconds to return. After months of data, the query takes minutes to return.

I have tried rebuilding and reorganizing the index with no success. However, if I remove the primary key, save the table, and then add it back, the queries begin returning in seconds again.

Does anyone know why this is. Is there something else I can do. I have a DB right now that I cannot do this do because of replication, and it seems as if removing the primary key is not the correct route to take.

Geert G

add a new primary key with a ident number and add a unique constraint on the old primary key values

srnar

Sorry I don't understand: what does it mean "Save table"? Are you querying columns from the composed index?

Allpax

ASKER

All I meant was apply the changes. In SQL Management Studio, you must save the table after removing the primary key, to apply the changes.

ASKER CERTIFIED SOLUTION

srnar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Allpax

ASKER

srnar,

I am not sure exactly what you are looking for, but Ctrl+L doesnt seem to do anything. I am using SQL Server 2005 with management studio, which does not have Query Analyzer.

srnar

In Management studio open new database query. Put there your long taking query - in this new window and hit Ctrl L. You will see a Query plan window in the bottom. Optionally you can use query menu and option Display estimated query plan.

Allpax

ASKER

Attached Execution Plan
Query.bmp