SQl Query takes too long

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

LVL 8
isaackhaziAsked:
Who is Participating?
 
Pratima PharandeCommented:
remove 0=0

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  cn_placed=0  ORDER BY cn_id  DESC
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Create nonclustered index on the column cn_placed
And the below syntax will perform much metter.
SELECT * FROM 
(
SELECT 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,
       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

0
 
isaackhaziAuthor Commented:
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
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
adatheladCommented:
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.
0
 
Pratima PharandeCommented:
what is this v_get_candidates_jobs  ?
Is it table ?  or View ?

0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Have you tried my approach and query
0
 
isaackhaziAuthor Commented:
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...????
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you remove the DISTINCT?
can you post the explain plan?
can you run the UPDATE STATISTICS on the table?
0
 
tncbbthositgCommented:
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.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.