Underperforming Query 2

[briangriffin]

This query takes a little over a minute to run, but I think it should run faster. I'm not too skilled at interpreting execution plans, but I don't see any red flags - there is a sort for 37%, but I don't know where that is coming from. When I run each component individually they all take less than a couple of seconds.

Any advice is always appreciated.

declare @v_latestmergedate smalldatetime
set @v_latestmergedate = (select max(mergedate) from ekw1.rml_opi.dbo.OPI_Account_Collections)

select 
a.accountnumber,
a.companyid,
replace(fullname,'~','') as FullName,
city,
state,
a.unbilled + a.thirty + a.sixty + a.ninety + a.overninety as priorbalance,
b.unbilled,
b.thirty,
b.sixty,
b.ninety,
b.over90,
b.inbaddebt,
b.currentbalance,
b.lastnotedate,
b.lastnote,
b.lastpaymentdate,
b.lastpayment,
b.paymentplannote,
b.lastPPPayment,
b.PPBalance,
a.LetterSent,
case when (b.lastnotedate > @v_latestmergedate) 
	or (b.lastpaymentdate > @v_latestmergedate) 
	or (b.currentbalance <>  a.unbilled + a.thirty + a.sixty + a.ninety + a.overninety) then '***' else '' end as AccountChanged

from ekw1.rml_opi.dbo.OPI_Account_Collections a

left outer join

(
select
a.accountnumber,
a.companyID,
sum(case when b.firststatementdate is null then b.amount else 0 end) as 'Unbilled',
sum(case when datediff(dd,b.firststatementdate,getdate()) <= 30 then b.amount else 0 end) as 'Thirty',
sum(case when datediff(dd,b.firststatementdate,getdate()) between 31 and 60 then b.amount else 0 end) as 'Sixty',
sum(case when datediff(dd,b.firststatementdate,getdate()) between 61 and 90 then b.amount else 0 end) as 'Ninety',
sum(case when datediff(dd,b.firststatementdate,getdate()) > 90 then b.amount else 0 end) as 'Over90',
sum(case when b.baddebtflag = 1 then b.amount else 0 end) as 'InBadDebt',
(sum(case when b.firststatementdate is null then b.amount else 0 end) +
sum(case when datediff(dd,b.firststatementdate,getdate()) <= 30 then b.amount else 0 end) +
sum(case when datediff(dd,b.firststatementdate,getdate()) between 31 and 60 then b.amount else 0 end) +
sum(case when datediff(dd,b.firststatementdate,getdate()) between 61 and 90 then b.amount else 0 end) +
sum(case when datediff(dd,b.firststatementdate,getdate()) > 90 then b.amount else 0 end) -
sum(case when b.baddebtflag = 1 then b.amount else 0 end)) as CurrentBalance,
addnotes.changeddt as LastNoteDate,
addnotes.note as LastNote,
addpmt.postdate as LastPaymentDate,
addpmt.amount as LastPayment,
ppqry.currentbalance as PPBalance,
ppqry.ppnote as PaymentPlanNote,
ppqry.lastpaymentdate as LastPPPayment


from dbo.OPI_Account_Collections a left outer join rmlbilling1.ois.dbo.vw_am_GetAccount_InAR b with(nolock) 
	on a.accountid = b.accountid
	and a.companyid = b.companyid
	
-------------------------------

left outer join

(
select
x.accountid,
x.companyid,
x.changeddt,
x.note

from rmlbilling1.ois.dbo.tblaccount_notes x with(nolock) inner join

(select 
accountid, 
companyid,
max(id) as maxid,
max(changeddt) as maxdate

from rmlbilling1.ois.dbo.tblaccount_notes notes with(nolock) 
where exists (select 1 from ekw1.RML_OPI.dbo.OPI_Account_Collections where mergedate = @v_latestmergedate 
			and accountid = notes.accountid
			and companyid = notes.companyid  )


group by accountid, companyid) noteqry


on x.accountid = noteqry.accountid
and x.id = noteqry.maxid) addnotes

on a.accountid = addnotes.accountid
and a.companyid = addnotes.companyid

-------------------------------
left outer join

(select
x.accountid,
x.companyid,
x.postdate,
x.amount

from rmlbilling1.ois.dbo.tblaccount_payment x with(nolock) inner join

(select 
accountid,
companyid, 
max(id) as maxid,
max(postdate) as maxdate

from rmlbilling1.ois.dbo.tblaccount_payment payment with(nolock) 
where exists (select 1 from ekw1.RML_OPI.dbo.OPI_Account_Collections where mergedate = @v_latestmergedate 
			and accountid = payment.accountid
			and companyid = payment.companyid  ) 

group by accountid, companyid ) noteqry

on x.accountid = noteqry.accountid
and x.id = noteqry.maxid) addpmt

on a.accountid = addpmt.accountid
and a.companyid = addpmt.companyid

-------------------------------
left outer join

(select 
accountid,
companyid,
sum(currentbalance) as currentbalance,
ppnote,
lastpaymentdate,
lastpayment as lastpppayment
 
from 
rmlbilling1.ois.dbo.vw_am_Get_PaymentPlan PP with(nolock)

where currentbalance > 0
and exists (select 1 from ekw1.RML_OPI.dbo.OPI_Account_Collections where mergedate = @v_latestmergedate
			and accountid = PP.accountid
			and companyid = PP.companyid  )
group by
accountid,
companyid,
ppnote,
lastpaymentdate,
lastpayment) ppqry

on a.accountid = ppqry.accountid
and a.companyid = ppqry.companyid

---------------------------------

group by
a.companyid,
addnotes.changeddt,
addnotes.note,
addpmt.postdate,
addpmt.amount,
ppqry.currentbalance,
ppqry.ppnote,
ppqry.lastpaymentdate,
a.accountnumber,
a.baddebtflag
) b

on a.accountnumber = b.accountnumber
and a.companyid = b.companyid


where mergedate = @v_latestmergedate

 

[markros]

Try selecting each part of it into temp table, index it on the JOIN field and then join all of them together.

See, if the performance improves.

PluralSight Learning Library

 

[briangriffin]

Thanks for the input. I'd like to try avoiding temp tables if possible, so I retooled the query to use variable tables instead. But I guess I've never done this with multiple variable tables before, because I'm getting the error that the variable must be declared.

In other words, this works:

declare @v_table1 table
(somenumber int,
sometext varchar(6))

insert into @v_table1
select 1, 'A'

select * from @v_table1

But this does not:

declare @v_table1 table
(somenumber int,
sometext varchar(6))

insert into @v_table1
select 1, 'A'


declare @v_table2 table
(somenumber int,
sometext varchar(6))

insert into @v_table1
select 1, 'B'


select * from @v_table1 inner join @v_table2
	on @v_table1.somenumber = @v_table2.somenumber

'Must declare the scalar variable @v_table1'

Can you explain why both variables don't remain in scope, and is there another way?

If not I'll go the temp table route. Thanks as always for the assist.

 

[markros]

Here you need to use aliases, e.g.
<pre>
select * from @v_table1 V1 inner join @v_table2 V2
on V1.somenumber = V2.somenumber</pre>

If your tables are small, then you can go with the table variables or CTEs (in SQL 2005+). If your each resulting table is big enough, then I stay with my original suggestion of temp tables and indexes.

PluralSight Learning Library

 

[gmmastros]

One method you can use to improve the performance of table variables is to put an index on them. The ONLY way to index a table variable is to declare a primary key for it. So... IF your data is unique, you can create a primary key in a table variable like this:

declare @v_table1 
table   (somenumber int,
        sometext varchar(6)
        [!]Primary Key (somenumber, sometext)[/!])

Indexes will help speed up the query when you use it elsewhere in your code. Sometimes it helps to swap the order of the columns in the table variable's primary key definition.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[briangriffin]

Thanks to both of you - didn't get as much improvement as I had hoped, so I'll schedule a job that loads it into a table each morning and report from there.

I didn't know you could create any type of index on a variable table, so that will prove useful in the future (nothing unique enough about this data to use one).

 

[markros]

No, as far as I know you ONLY can add one index to the table variable and this will be PRIMARY KEY clustered index.

PluralSight Learning Library