We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

SQl Query takes too long

Medium Priority
708 Views
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

Comment
Watch Question

CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
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

Author

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
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.
CERTIFIED EXPERT
Commented:
what is this v_get_candidates_jobs  ?
Is it table ?  or View ?

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Have you tried my approach and query

Author

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...????
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
can you remove the DISTINCT?
can you post the explain plan?
can you run the UPDATE STATISTICS on the table?
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.

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.