Solved

Query takes a long time to return

Posted on 2008-06-10
7
832 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 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 8

Accepted Solution

by:
srnar earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Attached Execution Plan
Query.bmp
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now