June 30, 2009 at 1:49 am
Hi dudes, I'm bit of a returning oldie that has forgotten a lot since the 90th. The lingo has developed 🙂
I administer a SQL2005 server and I want to pick out from one of the databases table, a log table, rows that have douplicate interactionid (created elsewhere and unique id of application session). These rows also have 2 certain column values = null.
The query exists and works but it somehow eats/blocks to much resources hence blocks app from writing to the log table
I run it once a day to view the rows and sometimes they need to be removed( if certain external conditions are fulfilled)
Suggestion to reduce load? (Additional commands like Priority = low???)
The query is long but the base is like this (removed column names in the select
select type,
interactionid,
donetime,
isnull(donetime, (
select top 1 donetime
from usermaillog m2
where m2.interactionid = m.interactionid
and m2.donetime is not null)) as donetime_m2,
tht,
status,
ignore
from usermaillog m
where (type = 3 or type = 14 or type = 15 )
and ignore = 0 and donetime is null and tht is null
and m.interactionid in
(
selectm0.interactionid
from g7_usermaillog m0
wherem0.interactionid in
(selectinteractionid fromusermaillog m1
where m1.interactionid = m0.interactionid
and m0.recordid <> m1.recordid
and m1.donetime is not null
)
AND interactionid in
(selectinteractionid
fromusermaillog m1
group by interactionid
havingcount(*) > 1
)
AND interactionid not in
(
selectinteractionid
fromg7_usermaillog m1
where m1.forwardedto is not null
)
)
June 30, 2009 at 10:20 am
Ok I'm gonna guess and say that some of those nested correlated queries are going to kill you.
However please could you attach the Actual Graphical Execution plan (*.sqlplan) as a zip file to the job, I'll be able to tell you more once I see the query plan.
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 1, 2009 at 12:34 am
Hi, Something is wrong caus' I can see the option "Save executionplan as.." but it s not accessable.
Found out that if I use the old 'SHOWPLAN_TEXT' option it works.
include the .zip file
July 1, 2009 at 12:41 am
Forgot to mention 😎
This .sqlplan is executed against a test SQL2000 server hence the _TEXT option and the low execution time.
The production server am I only able to execute against really early in the morning to prevent from blocking users (prior to 0600) and then it takes around 5 minutes to execute. Like to get a bit more sleep in the morning, thats why I'm eager to make it more efficient, pref below 2 minutes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply