Solved

Query takes a long time to return

Posted on 2008-06-10
7
854 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 37

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
2016 SQL Licensing 7 41
Find SQL query used by application 3 20
Tsql query 6 22
SQL Select to Group and Filter Data 5 13
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

809 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