?
Solved

Query takes a long time to return

Posted on 2008-06-10
7
Medium Priority
?
888 Views
Last Modified: 2011-10-19
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.
0
Comment
Question by:Allpax
  • 3
  • 3
7 Comments
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 21753002
add a new primary key with a ident number and add a unique constraint on the old primary key values
0
 
LVL 8

Expert Comment

by:srnar
ID: 21753976
Sorry I don't understand: what does it mean "Save table"? Are you querying columns from the composed index?
0
 
LVL 1

Author Comment

by:Allpax
ID: 21754334
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.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
LVL 8

Accepted Solution

by:
srnar earned 2000 total points
ID: 21754463
I see. You need to create an index that will be using your query column "Where D = 1". It may be the solution that Geert is pointing above. You can also see the query plan - in management studio hit the Ctrl+L. Can you post image of the execution plan? For queries "index seek" means that the result will be immediate.
0
 
LVL 1

Author Comment

by:Allpax
ID: 21754576
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.
0
 
LVL 8

Expert Comment

by:srnar
ID: 21754618
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.
0
 
LVL 1

Author Comment

by:Allpax
ID: 21909500
Attached Execution Plan
Query.bmp
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question