September 3, 2015 at 11:28 am
Hello Everyone,
I have extremely slow job that runs every night, comprising of 3 steps, First 2 steps are T-SLQ, Step 3 is SSIS package. Steps 2 and 3 have no issues, hardly takes few seconds, on the other hand step 1 is taking on average 17 hours, there has to be something wrong. We have inherited this query that was built by someone years ago. both the step 1 and step 2 have used cursors, which is my assumption that's what making it slow. If someone could review the TSQL for step1 and step2, especially step 1. Any opinions or suggestions or different kind of approach I should take would be highly appreciated.
Step 1
declare @curr_LoanID varchar(10)
declare LettersQueue_cursor CURSOR for
select LoanID from service.dbo.loan where nextcoupondate <= convert(varchar(10),dateadd(dd,3,getdate()),101)
and nextcoupondate > '4/10/2009' and LoanID not in
(select LoanID from M1CustLetters where StatementDate >= convert(varchar(10),dateadd(dd,-4,getdate()),101))
and LoanID not in (select LoanID from service.dbo.Status where CouponStop = 1)
or
loanid in (select loanid from service.dbo.usr_m1_letter_requests
where customerstatement >= convert(varchar(10),dateadd(dd,-4,getdate()),101)
and customerstatement <= convert(varchar(10),dateadd(dd,3,getdate()),101)
and loanid not in (select loanid from m1custletters
where StatementDate >= convert(varchar(10),dateadd(dd,-4,getdate()),101)))
and LoanID not in (select LoanID from service.dbo.Status where CouponStop = 1)
or
loanid in (select loanid from service.dbo.loan where right(loanid,7) not in
(select loanid from m1lettersqueue)
and lastbilldate <= getdate() and lastbilldate >= dateadd(dd,-14,getdate())
and loanid not in (select loanid from service.dbo.status where CouponStop = 1)
and loanid not in (select loanid from m1custletters where statementdate >= dateadd(dd,-30,getdate()))
and duedate <= dateadd(dd,30,getdate()))
OPEN LettersQueue_cursor
FETCH LettersQueue_cursor into @curr_LoanID
WHILE @@FETCH_STATUS=0
BEGIN
if exists (select * from service.dbo.loan where loanid = @curr_LoanID and lastbilldate is null)
begin
update service.dbo.loan set lastbilldate = dateadd(mm,-1,nextcoupondate) where loanid = @curr_LoanID
end
if not exists (select * from m1lettersqueue where loanid = right(@curr_LoanID,7)
and statementdate = convert(varchar(10),getdate(),101))
begin
insert into m1lettersqueue (loanid,statementdate)
select right(@curr_LoanID,7),convert(varchar(10),getdate(),101)
end
FETCH LettersQueue_cursor into @curr_LoanID
END
close LettersQueue_cursor
deallocate LettersQueue_cursor
go
Step 2
declare @curr_LoanID varchar(10)
declare CustumerLetters_cursor CURSOR for
select LoanID from service.dbo.Participation where right(LoanID,7) in (select LoanID from M1LettersQueue
where StatementDate = convert(varchar(10),getdate(),101))
order by InvestorID asc
OPEN CustumerLetters_cursor
FETCH CustumerLetters_cursor into @curr_LoanID
WHILE @@FETCH_STATUS=0
BEGIN
--select LoanID = @curr_LoanID
exec M1_CreateCustStatements @curr_LoanID
FETCH CustumerLetters_cursor into @curr_LoanID
END
close CustumerLetters_cursor
deallocate CustumerLetters_cursor
go
September 3, 2015 at 4:05 pm
Try something like this for the first step... not sure why it was put into a loop... unless I'm missing something...
declare @LoanID as table (LoanID varchar(10))
insert into @LoanID(LoanID)
select LoanID from service.dbo.loan where nextcoupondate <= convert(varchar(10),dateadd(dd,3,getdate()),101)
and nextcoupondate > '4/10/2009' and LoanID not in
(select LoanID from M1CustLetters where StatementDate >= convert(varchar(10),dateadd(dd,-4,getdate()),101))
and LoanID not in (select LoanID from service.dbo.Status where CouponStop = 1)
or
loanid in (select loanid from service.dbo.usr_m1_letter_requests
where customerstatement >= convert(varchar(10),dateadd(dd,-4,getdate()),101)
and customerstatement <= convert(varchar(10),dateadd(dd,3,getdate()),101)
and loanid not in (select loanid from m1custletters
where StatementDate >= convert(varchar(10),dateadd(dd,-4,getdate()),101)))
and LoanID not in (select LoanID from service.dbo.Status where CouponStop = 1)
or
loanid in (select loanid from service.dbo.loan where right(loanid,7) not in
(select loanid from m1lettersqueue)
and lastbilldate <= getdate() and lastbilldate >= dateadd(dd,-14,getdate())
and loanid not in (select loanid from service.dbo.status where CouponStop = 1)
and loanid not in (select loanid from m1custletters where statementdate >= dateadd(dd,-30,getdate()))
and duedate <= dateadd(dd,30,getdate()))
update l
set l.lastbilldate = dateadd(mm,-1,l.nextcoupondate)
from service.dbo.loan l
where exists (select 1 from @LoanID i where l.loanid = i.LoanID)
and lastbilldate is null
insert into m1lettersqueue (loanid,statementdate)
select right(l.loanid,7),convert(varchar(10),getdate(),101)
from @LoanID l
where not exists(select 1 from m1lettersqueue m where m.loanid = right(l.LoanID,7)
and m.statementdate = convert(varchar(10),getdate(),101)))
I would normally view the statements as a select to see the data first before I actually do an update or insert...
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
September 4, 2015 at 9:49 am
Thank you so much,
The dilemma I am facing is that job is averaging 21 hours and it is scheduled for everyday, so the job pretty much keeps running all day. And it happens to be an important job that I cannot just stop to test few things. Also when I am testing the old code(one with cursors), just the select statement is taking up a lot of time and making the whole server slow...How should I proceed If I want to test your new code?
Also since we took out cursors in step 1, would it matter If I kept the cursors on step 2?
Thanks again.
September 4, 2015 at 10:54 am
SQLPain (9/4/2015)
Thank you so much,The dilemma I am facing is that job is averaging 21 hours and it is scheduled for everyday, so the job pretty much keeps running all day. And it happens to be an important job that I cannot just stop to test few things. Also when I am testing the old code(one with cursors), just the select statement is taking up a lot of time and making the whole server slow...How should I proceed If I want to test your new code?
Also since we took out cursors in step 1, would it matter If I kept the cursors on step 2?
Thanks again.
How large is that loan table? I can't imagine it taking very long to return a single column. Even returning 10's of millions of records shouldn't take very long. Maybe the process is running into blocking issue or missing indexes or stats are outdated. You can break up the main select statement into 3 separate scripts on the OR and see if any one of the 3 takes longer than expected... I'd be expecting only seconds to return the data. Also I'd re-write the statements with "Exist" instead of the "not in". I did the first one, I think it should return the same data. I assumed that the CouponStop is a bit field.
In regards to the 2nd step it's calling a stored proc that takes a single loan number so you'll have to re-write that stored proc or the content of it in a more set based script.
On how to test it without interrupting the PRD would be to get a copy from a backup. You might consider getting a consultant to come in and look at it.
Also check out Gail Shaw's page. She's got a ton of good stuff, link is below. Browse around SSC and her name "gilamonster" will come up a lot... along with Jeff Modden, Perry Whittle etc...
I've been doing a lot more .Net and less SQL so I'm a bit rusty.
select LoanID
from service.dbo.loan l
where nextcoupondate <= convert(varchar(10),dateadd(dd,3,getdate()),101)
and nextcoupondate > '4/10/2009'
and exists (select 1 from M1CustLetters where LoanID = l.LoanID and StatementDate < convert(varchar(10),dateadd(dd,-4,getdate()),101))
and exists (select 1 from service.dbo.Status where LoanID = l.LoanID and CouponStop = 0)
GO
select LoanID
from service.dbo.loan
where loanid in (select loanid from service.dbo.usr_m1_letter_requests
where customerstatement >= convert(varchar(10),dateadd(dd,-4,getdate()),101)
and customerstatement <= convert(varchar(10),dateadd(dd,3,getdate()),101)
and loanid not in (select loanid from m1custletters
where StatementDate >= convert(varchar(10),dateadd(dd,-4,getdate()),101)))
and LoanID not in (select LoanID from service.dbo.Status where CouponStop = 1)
GO
select LoanID
from service.dbo.loan
where loanid in (select loanid from service.dbo.loan where right(loanid,7) not in
(select loanid from m1lettersqueue)
and lastbilldate <= getdate() and lastbilldate >= dateadd(dd,-14,getdate())
and loanid not in (select loanid from service.dbo.status where CouponStop = 1)
and loanid not in (select loanid from m1custletters where statementdate >= dateadd(dd,-30,getdate()))
and duedate <= dateadd(dd,30,getdate()))
Gail Shaw
https://www.simple-talk.com/author/gail-shaw/
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
September 11, 2015 at 10:35 am
Thanks, somehow I am not getting the same output for the following two queries. 2nd one is fetching more records than the first one.
1)
select LoanID from service.dbo.loan where nextcoupondate <= convert(varchar(10),dateadd(dd,3,getdate()),101)
and nextcoupondate > '4/10/2009' and LoanID not in
(select LoanID from [MarineOne].[dbo].[M1CustLetters] where StatementDate >= convert(varchar(10),dateadd(dd,-4,getdate()),101))
and LoanID not in (select LoanID from service.dbo.Status where CouponStop = 1)
Order by LoanID
2)
select LoanID
from service.dbo.loan l
where nextcoupondate <= convert(varchar(10),dateadd(dd,3,getdate()),101)
and nextcoupondate > '4/10/2009'
and exists (select 1 from [MarineOne].[dbo].[M1CustLetters] where LoanID = l.LoanID and StatementDate < convert(varchar(10),dateadd(dd,-4,getdate()),101))
and exists (select 1 from service.dbo.Status where LoanID = l.LoanID and CouponStop = 0)
Order by LoanID
September 11, 2015 at 11:24 am
Are all those
convert(VARCHAR(10), SomeDate, 101)
functions preventing the query from being SARGable, and thus indexes not used?
Personally I'd save all the variations of those into suitable @DataVariables and use those within the query.
The only variations are +3m, -4m and "today"
although there is also
AND lastbilldate <= getdate()
AND lastbilldate >= dateadd(dd, - 14, getdate())
which is comparing against GetDate() / 14-days-prior both of which include the current time -maybe that is by design, but it would be more normal to base the range on Midnight (i.e. "whole days" rather than "part days")
Using CONVERT(..., somedate, 101) scares me anyway as it is an ambiguous date. I suppose all your hardware and the session etc. is safely running with US Locale, but should anything change it will go Base-Over-Apex 🙁
The EXISTS test in these seem a waste of CPU to me:
IF EXISTS (
SELECT *
FROM service.dbo.loan
WHERE loanid = @curr_LoanID
AND lastbilldate IS NULL
)
BEGIN
UPDATE service.dbo.loan
SET lastbilldate = dateadd(mm, - 1, nextcoupondate)
WHERE loanid = @curr_LoanID
END
IF NOT EXISTS (
SELECT *
FROM m1lettersqueue
WHERE loanid = right(@curr_LoanID, 7)
AND statementdate = convert(VARCHAR(10), getdate(), 101)
)
BEGIN
INSERT INTO m1lettersqueue (
loanid
,statementdate
)
SELECT right(@curr_LoanID, 7)
,convert(VARCHAR(10), getdate(), 101)
END
not to mention that presumably? the data could change between the Exists test and the UPDATE / INSERT? :w00t:
This should do the same thing only (probably only a small amount) faster
UPDATE service.dbo.loan
SET lastbilldate = dateadd(mm, - 1, nextcoupondate)
WHERE loanid = @curr_LoanID
AND EXISTS (
SELECT *
FROM service.dbo.loan
WHERE loanid = @curr_LoanID
AND lastbilldate IS NULL
)
INSERT INTO m1lettersqueue (
loanid
,statementdate
)
SELECT right(@curr_LoanID, 7)
,convert(VARCHAR(10), getdate(), 101)
WHERE NOT EXISTS (
SELECT *
FROM m1lettersqueue
WHERE loanid = right(@curr_LoanID, 7)
AND statementdate = convert(VARCHAR(10), getdate(), 101)
)
but I expect the whole cursor thing could be done as a single set-based statement and if that is possible would take seconds rather than hours ...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply