Solved

Query takes a long time to return

Posted on 2008-06-10
7
872 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 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

751 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