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/