Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


SQl Query takes too long

Posted on 2009-02-12
Medium Priority
Last Modified: 2012-05-06
Why does this query take forever. And it never even results in anything. Ive waited 5 mins finally ending up cancelling it.

Is it something to do with indexes or corrupt logs.

SELECT DISTINCT TOP 100 cn_id, cn_fname, cn_lname, cn_age, cn_years_experience,cn_max_edu_txt,cn_present_position, cn_present_salary, cn_salary_sought, cn_incoming, cn_cart, cn_placed, cn_english_ability FROM v_get_candidates_jobs WHERE 0=0   AND cn_placed=0  ORDER BY cn_id  DESC

Open in new window

Question by:isaackhazi
  • 2
  • 2
  • 2
  • +3
LVL 39

Accepted Solution

Pratima Pharande earned 600 total points
ID: 23620459
remove 0=0

SELECT DISTINCT TOP 100 cn_id, cn_fname, cn_lname, cn_age,
cn_present_salary, cn_salary_sought, cn_incoming, cn_cart,
cn_placed, cn_english_ability FROM v_get_candidates_jobs
WHERE  cn_placed=0  ORDER BY cn_id  DESC
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 300 total points
ID: 23620467
Create nonclustered index on the column cn_placed
And the below syntax will perform much metter.
SELECT cn_id,
       row_number() OVER ( partition BY cn_id ORDER BY cn_id DESC) rnum
FROM   v_get_candidates_jobs
WHERE  cn_placed = 0) temp
WHERE rnum <= 100

Open in new window


Author Comment

ID: 23620473
removed it.

Still takes forever.

The problem is that this query is being executed by an ASP app. So I keep getting the TIMEOUT message
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 23

Assisted Solution

adathelad earned 300 total points
ID: 23620479
What indexes do you have on the table? Presumably, cn_id is your primary key and so that will have an index on? Does cn_placed have an index on?

The other thing to consider, is there a long running transaction on the table? If there is, it could be blocking the query returning the results (a quick test would be to temporarily add "WITH (NOLOCK)" after the table name in the query (FROM v_get_candidates_jobs WITH (NOLOCK)...) and try the query again. If that comes back with results when the original query doesn't, then it's because there is a long running transaction/lock being held on the data. Note, don't keep WITH (NOLOCK) in there permanently unless it is what you actually want as it means you could read data that hasn't actually been committed yet.
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 600 total points
ID: 23620485
what is this v_get_candidates_jobs  ?
Is it table ?  or View ?

LVL 57

Expert Comment

by:Raja Jegan R
ID: 23620567
Have you tried my approach and query

Author Comment

ID: 23620626
Yes i tried all the above queries but they all take for ever.

Is this because the database was copied from an online server and then attached to the local server

Do you think any files are corrupt or something like that. may be log file...????
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23620729
can you remove the DISTINCT?
can you post the explain plan?
can you run the UPDATE STATISTICS on the table?

Assisted Solution

tncbbthositg earned 300 total points
ID: 23625297
If there are so many rows in that table that using DISTINCT is degrading performance that dramatically, I'd recommend periodically generating a "cube" of sorts.  It'd be really easy and wouldn't be that cube-like, but it could speed things up if you inserted distinct rows into a staging table and you could select top 100 from that.  That'll work only if you don't really need live data and can deal with a slight delay.

The other alternative may be to create a composite index over all of the columns you're returning.  This'll dramatically expedite the DISTINCT operation.  I ran a test against a test table with about 4.2 million rows (3 int columns randomly assigned values between 0 and 99).  I ran 5 trials per condition and excluded 2 outliers.  Then, I averaged the remaining 3 results.  Here are the results of my tests:

non-indexed: 1856 ms
nonclustered-index: 1016 ms
non-indexed: 1795 ms
clustered-index: 1063 ms
non-indexed: 1911 ms
nonclustered, non-composite: 1771 ms

The composite index won't help with your where clause so you'll still need an index on each column you'll be searching within.


Featured Post

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!

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

579 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