June 29, 2007 at 3:25 am
I have a heavily used webpage that returns job results to a user. The user can click on column header links that will sort the results by that column both ASC and DESC. I have found however that whenever a user does order by LiveDate ASC the query performs very badly (sometimes taking over 30 seconds to return results) where's ordering DESC returns instantaneously.
I have tried to make use of SQL 2005 new TOP(x), ROW_NUMBER() and OVER functions for this query to return paged
results and the higher the page number the slower it takes on this one query (liveDate ASC).
SELECT
JobPK ,jobtitle,JobDescription
FROM
(SELECT TOP(80) ROW_NUMBER() OVER(ORDER BY livedate DESC) -- THIS PERFORMS <1 SECOND
As Row, JobPk ,jobtitle, j.JobDescription
FROM SITE_JOBS as sj
JOIN JOBS as j ON sj.JobFK = j.JobPK
WHERE j.Live = 1 AND sj.SiteFK = 1 AND j.TempMode<>1) AS JOBS
WHERE
Row between 61 AND 80
SELECT
JobPK ,jobtitle,JobDescription
FROM
(SELECT TOP(80) ROW_NUMBER() OVER(ORDER BY livedate ASC) -- THIS PERFORMS BAD 25 - 40 SECONDS
As Row, JobPk ,jobtitle, j.JobDescription
FROM SITE_JOBS as sj
JOIN JOBS as j ON sj.JobFK = j.JobPK
WHERE j.Live = 1 AND sj.SiteFK = 1 AND j.TempMode<>1) AS JOBS
WHERE
Row between 61 AND 80
--Row between 21 AND 40
--Row between 1 AND 20 -- This performs bad still with 19 seconds
Indexes on the JOBS table are
clustered PK JobPK integer SORT ORDER ASC
nclidx CreateDate SORT ORDER ASC
nclidx LiveDate Sort SORT ORDER ASC
nclidx ClientFK Sort SORT ORDER ASC
nclidx SiteFk Sort SORT ORDER ASC
on SITE_JOBS table is just
clusterd PK JobPk and SiteFK SORT ORDER ASC
I have tried adding another non-clustered index on LiveDate sorted DESC but that makes no difference nor does
recompiling the query each time its ran, which fixed a similar query I had with retrieval time issues.
Does anyone have any ideas how I can get this query to return in an acceptable time.
Thanks in advance for any help.
June 29, 2007 at 4:32 am
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply