Solved

Query takes a long time to return

Posted on 2008-06-10
7
877 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Accepted Solution

by:
srnar earned 500 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

630 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