December 16, 2015 at 2:52 pm
I have inherited the following code involving cursors which happens to be step1 in a job, currently this step is taking 19 hours to run, there has to be something wrong with this code, I am not a big fan of cursors nor have used it, can somebody look over the code and see what is going on here.
declare @curr_LoanID varchar(10)
declare LettersQueue_cursor CURSOR for
select LoanID from srv.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 srv.dbo.Status where CouponStop = 1)
or
loanid in (select loanid from srv.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 srv.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 srv.dbo.loan where loanid = @curr_LoanID and lastbilldate is null)
begin
update srv.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
December 16, 2015 at 4:42 pm
Can you post DDL statements for all tables involved in the queries?
_____________
Code for TallyGenerator
December 17, 2015 at 8:44 am
CREATE TABLE [dbo].[M1CustLetters](
[LoanID] [nvarchar](10) NOT NULL,
[StatementDate] [datetime] NOT NULL,
[CustomerName] [varchar](40) NOT NULL,
[Address1] [varchar](40) NOT NULL,
[Address2] [varchar](40) NOT NULL,
[BankPool] [varchar](12) NOT NULL,
[AcctNum] [varchar](12) NOT NULL,
[DueDate] [datetime] NOT NULL,
[NextStatementDate] [datetime] NOT NULL,
[CurrentBalance] [varchar](17) NOT NULL,
[Payment] [varchar](11) NOT NULL,
[PastDue] [varchar](11) NOT NULL,
[LateCharge] [varchar](10) NOT NULL,
[OtherCharge] [varchar](10) NOT NULL,
[TotalDue] [varchar](11) NOT NULL,
[BankMessage] [varchar](60) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[M1LettersQueue](
[LoanID] [nvarchar](10) NOT NULL,
[StatementDate] [datetime] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[MailingAddress](
[LoanID] [varchar](10) NOT NULL,
[AssmRecCounter] [tinyint] NOT NULL DEFAULT (0),
[AddressLine1] [varchar](40) NULL,
[AddressLine2] [varchar](40) NULL,
[City] [varchar](25) NULL,
[State] [varchar](25) NULL,
[Zip] [varchar](10) NULL,
[PromotionalMail] [tinyint] NOT NULL DEFAULT (1),
[FirstLine] [tinyint] NOT NULL DEFAULT (1),
[SecondLine] [tinyint] NOT NULL DEFAULT (0),
[PrivacyRequested] [tinyint] NOT NULL DEFAULT (0),
[CountryCode] [varchar](3) NOT NULL DEFAULT ('USA'),
[PrimaryMailing] [tinyint] NOT NULL DEFAULT (1),
[ReturnedMail] [bit] NOT NULL CONSTRAINT [DF_MailingAddress_ReturnedMail] DEFAULT (0),
[AddressIndicator] [tinyint] NOT NULL DEFAULT ((0)),
[AddressLine3] [varchar](40) NULL,
[MailingAddressID] [int] IDENTITY(1,1) NOT NULL,
[TimeZone] [tinyint] NOT NULL DEFAULT ((0)),
[CityCode] [varchar](10) NULL,
CONSTRAINT [MailingAddress_PK] PRIMARY KEY NONCLUSTERED
(
[MailingAddressID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
Do you want the DDL for loan table? it is approx. 100 columns
January 6, 2016 at 10:37 am
I am not a fan of cursors, never used it. Is there a way we can write the code without it? Any help would be appreciated.
January 6, 2016 at 10:44 am
I don't have test data, but this should be equivalent to your code.
SELECT LoanID
INTO #Loans
FROM srv.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 srv.dbo.STATUS
WHERE CouponStop = 1
)
OR loanid IN (
SELECT loanid
FROM srv.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 srv.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 srv.dbo.loan
SET lastbilldate = dateadd(mm, - 1, nextcoupondate)
WHERE loanid IN (SELECT LoanID FROM #Loans)
AND lastbilldate IS NULL;
INSERT INTO m1lettersqueue (
loanid
,statementdate
)
SELECT loanid
,convert(VARCHAR(10), getdate(), 101)
FROM m1lettersqueue
WHERE loanid NOT IN (SELECT right(LoanID, 7) FROM #Loans)
OR statementdate != convert(VARCHAR(10), getdate(), 101);
January 6, 2016 at 11:09 am
SQLPain (12/16/2015)
I have inherited the following code involving cursors which happens to be step1 in a job, currently this step is taking 19 hours to run, there has to be something wrong with this code, I am not a big fan of cursors nor have used it, can somebody look over the code and see what is going on here.
declare @curr_LoanID varchar(10)declare LettersQueue_cursor CURSOR for
select LoanID from srv.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 srv.dbo.Status where CouponStop = 1)
or
loanid in (select loanid from srv.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 srv.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 srv.dbo.loan where loanid = @curr_LoanID and lastbilldate is null)
begin
update srv.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
Check your ANDs and ORs. They don't appear to make sense and your query is probably processing many more rows than it should. Use brackets where appropriate.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 6, 2016 at 11:17 am
ChrisM@home (1/6/2016)
Check your ANDs and ORs. They don't appear to make sense and your query is probably processing many more rows than it should. Use brackets where appropriate.
You're right, I didn't pay much attention to that part of the query. I'd probably change it to use set operators (UNION, INTERSECT, EXCEPT), but I won't suggest anything without being able to test the code.
January 6, 2016 at 11:18 am
Thanks Luis,
Even the following is taking endless amount of time, I kill it after 3-4 minutes, I am trying to see if there are any repetitions which we can avoid, plus the dates are also not making a lot of sense here.
SELECT LoanID
--INTO #Loans
FROM srv.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 srv.dbo.STATUS
WHERE CouponStop = 1
)
OR loanid IN (
SELECT loanid
FROM srv.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 srv.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())
)
This is the step 2 of the job:
declare @curr_LoanID varchar(10)
declare CustumerLetters_cursor CURSOR for
select LoanID from srv.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
January 7, 2016 at 9:40 am
Can anyone make sense of what exactly is happening with the tables(loan, usr_m1_letter_requests, m1lettersque)?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply