January 10, 2007 at 9:30 am
Hi,
I have a query that takes an average of 5 seconds to return results. If I remove the two OR statements comparing the counts indicated in RED font below, then it takes less than 1 second to return the result.
Table ra_review_t has 2,000 records.
View sr_note_v has 20,000 records.
View sr_activity_v has 40,000 records.
Is there a way to enhance the query below to provide a better performance?
SELECT rev.srvc_request_num AS serviceRequestId,
rev.reviewed_dt AS "review.lastUpdatedDate",
rev.reviewed_by AS "review.lastUpdatedBy",
rev.comments AS "review.comments",
sr.summary AS summary,
sr.description AS description,
sr.status AS status,
FROM ra_review_t rev
JOIN sr_srvc_request_v sr ON rev.srvc_request_num = sr.srvc_request_num
WHERE rev.sr_status != sr.status
AND rev.last_updated_by = 'U123456'
AND ( rev.sr_description != sr.description
OR rev.sr_summary != sr.summary
OR rev.sr_notes_count != (SELECT count(*) FROM sr_note_v WHERE srvc_request_num = rev.srvc_request_num)
OR rev.sr_activity_count != (SELECT count(*) FROM sr_activity_v WHERE srvc_request_num = rev.srvc_request_num)
)
ORDER BY sr.created_dt ASC
THanks in advance for your help and advise.
Tuan
January 10, 2007 at 12:15 pm
try something like this: i've moved the counts you are comparing into two subselect tables instead;
I've found this improves performance for me:
SELECT rev.srvc_request_num AS serviceRequestId,
rev.reviewed_dt AS "review.lastUpdatedDate",
rev.reviewed_by AS "review.lastUpdatedBy",
rev.comments AS "review.comments",
sr.summary AS summary,
sr.description AS description,
sr.status AS status
FROM ra_review_t rev
INNER JOIN sr_srvc_request_v sr
ON rev.srvc_request_num = sr.srvc_request_num
LEFT OUTER JOIN (SELECT srvc_request_num,count(*) AS REQCOUNT FROM sr_note_v GROUP BY srvc_request_num) X
ON rev.srvc_request_num = x.srvc_request_num
LEFT OUTER JOIN (SELECT srvc_request_num,count(*) AS ACTCOUNT FROM sr_activity_v GROUP BY srvc_request_num) Y
ON rev.srvc_request_num = x.srvc_request_num
WHERE rev.sr_status != sr.status
AND rev.last_updated_by = 'U123456'
AND ( rev.sr_description != sr.description
OR rev.sr_summary != sr.summary
OR rev.sr_notes_count != x.REQCOUNT
OR rev.sr_activity_count != Y.ACTCOUNT
)
ORDER BY sr.created_dt ASC
Lowell
January 10, 2007 at 1:04 pm
Hi Lowell -
Thanks very much for your response. I have update the SQL statement as you suggested. Unfortunately, the updated SQL statement took more than 3 minutes to return the same result.
Any ideas?
Thanks,
Tuan
January 10, 2007 at 1:28 pm
Just for the heck of it, change the LEFT OUTER JOIN's to INNER JOIN's in the query Lowell gave you.
January 10, 2007 at 1:53 pm
Sorry, it was a mistype (corrected and indicated in red font) in the updated SQL statement below. With the new query it takes 4 seconds which is better than my original query.
SELECT rev.srvc_request_num AS serviceRequestId,
rev.reviewed_dt AS "review.lastUpdatedDate",
rev.reviewed_by AS "review.lastUpdatedBy",
rev.comments AS "review.comments",
sr.summary AS summary,
sr.description AS description,
sr.status AS status
FROM ra_review_t rev
INNER JOIN sr_srvc_request_v sr
ON rev.srvc_request_num = sr.srvc_request_num
LEFT OUTER JOIN (SELECT srvc_request_num,count(*) AS REQCOUNT FROM sr_note_v GROUP BY srvc_request_num) X
ON rev.srvc_request_num = X.srvc_request_num
LEFT OUTER JOIN (SELECT srvc_request_num,count(*) AS ACTCOUNT FROM sr_activity_v GROUP BY srvc_request_num) Y
ON rev.srvc_request_num = Y.srvc_request_num
WHERE rev.sr_status != sr.status
AND rev.last_updated_by = 'U123456'
AND ( rev.sr_description != sr.description
OR rev.sr_summary != sr.summary
OR rev.sr_notes_count != X.REQCOUNT
OR rev.sr_activity_count != Y.ACTCOUNT
)
ORDER BY sr.created_dt ASC
Thanks,
Tuan
January 10, 2007 at 5:18 pm
good catch and sorry for the typo;
check to see if there is an index on ra_review_t.srvc_request_num ;
all three joins use that for this query, and it might speed up if one gets added(if it doesn't already exist)
syntax: CREATE INDEX IX_ra_review_t_srvc_request_num ON ra_review_t(rvc_request_num)
Lowell
January 10, 2007 at 7:09 pm
Hi Lowell -
Thanks very much again for your great help with the issue.
Tuan
January 10, 2007 at 8:23 pm
Change the Count(*) to Count(1)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 10, 2007 at 8:45 pm
What do you expect from that?
COUNT(*) allows the server to use the fastest access path (will choose an index scan instead of clustered scan whenever possible).
January 11, 2007 at 3:58 am
I read somewhere that it would be faster. I've never tested it though....
I'll have to look into that today. ----
I tested on a table with 1.5mil rows and it seems that select count(*) and count(1) both use an index scan and the execution plan/time is the same.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 11, 2007 at 4:38 am
since all three of these tables (sr_srvc_request_v,sr_note_v,sr_activity_v) are joining on the srvc_request_num column, all three might also benefit from an index on their srvc_request_num column;
I'd look at the execution plan and any of the tables that had a table scan,and add any indexes to those tables, one at a time, in order to test performance. you might just need one index to make this sql perform faster.
Lowell
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply