Optimizing Stored Procedure
-
I have a procedure that is taking 100 minute to return. It's basically inserting data into two separate temporary tables then using those two table (and other static tables), insert into another static table. When I take each query piece out of the procedure and run it with STATISTICS IO ON and look at the execution plan, the query returns in less than 2 seconds with 0 logical reads on the temporary table, Worktable, and the static table. But this query as part of the stored procedure returns 38677 logical reads on the temporary table, 39797 logical reads on Worktable, and 77352 logical reads on the static table.
How should I approach optimizing? My query piece is below. Thanks!
create table #payments (paymentid int,
customerid int,
ccnumber varchar(20),
sequence varchar(10),
authcode varchar(30),
authdate datetime ,
amount money,
enterdate datetime)
insert into #payments
select
p.paymentid,
p.customerid,
p.ccnumber,
p.sequence,
p.authcode,
p.authdate,
round(p.amount,2) as amount,
p.enterdate
from
billing.dbo.payments p
left join paymentprocessing.dbo.log l on l.paymentid = p.paymentid
where
p.authdate between @sdate and @edate
and p.void = 0
and p.amount != 0
and ( p.processor = 'Processed'
or
p.processor is null
)
and l.paymentid is null
-
make sure to check out index usage in the execution plan. If the optimizer can't find an appropriate index to make the query plan it'll be forced to do a table scan. Table scans really slow things down in large recordsets.
You may be doing something different in the test rig outside the sp to get different results
-
I suppose the variables @sdate and @edate are parameters. I had the same type of problem in my procedures. Make sure the optimiser has correct values for @sdate and @edate the first time you exec the procedure. You could also try the 'with recompile' option ...
Also, the join looks a bit strange : l.paymentid = p.paymentid .... and l.paymentid is null. You coul probably help the optimiser by adding also 'and p.paymentid is null'.
-
OR processing can be slow,
try changing
.......and ( p.processor = 'Processed'
or
p.processor is null
).....
to
.......and isnull(p.processor,'Processed') = 'Processed' .........
Might make the query optimizer happier !?
/rockmoose
You must unlearn what You have learnt -
Reorder where clause:
p.void = 0
and l.paymentid is null
and p.authdate between @sdate and @edate
and isnull (p.processor, 'Processed') = 'Procesed'
and p.amount != 0
-
It sounds like you need to index one (or both) of the temp tables so that joining can work efficiently. Difficult to say without seeing the whole thing though - you should be able to run it in QA with the execution plan on to get an idea of where all the effort is going.
-
Thank you for all of your replies. I actually added indexes to the temporary tables yesterday and now the procedure takes 2 hours and 50 minutes!
I'll go through and try the code suggestions and let you know what happens.
Thank you for your time!
-
After reading your statement and the code I can see immediately we don't have enough of the picutre to know for sure what is going on. Can you post the entire code so we can be more objective in what we are saying?
-
Here is the entire procedure as it was prior to any testing. The first insert into the recon table is where my headache appears to be located. I am currently running a test of this procedure with yet another temp table (it's the third temp table) for all cctrans records because I'm thinking of ridding the left join to cctrans in the first insert into the recon table.
Thank you so much!
__________________________________________________________
create procedure dbo.test1
@ccserverid int,
@sdate datetime,
@edate datetime,
@client_id varchar(10) = '100010001'
as
begin
set transaction isolation level read uncommitted
set nocount on
declare @nreccount int
declare @scpoolid int
select @scpoolid = scpoolid
from dbo.processormerchantxref
where merchantid = @client_id
/* processormerchantxref not indexed; only two records in table */
if @scpoolid is null
begin
select 0 as [count]
return 0
end
if exists (select top 1 customerid from dbo.recon)
begin
truncate table recon
end
------------------------------------------------------------------------------
--create the first temp table for all ccresponse records for this day
------------------------------------------------------------------------------
if object_id('tempdb..#ccresponse') is not null
begin
drop table #ccresponse
end
create table #ccresponse (ccrespid int,
customerid int,
amount money,
ccnumber varchar(20),
ccexpire varchar(4),
response varchar(100),
authdate datetime,
trxno varchar(10),
scpoolserverxrefid int)
------------------------------------------------------------------------------
--create the temp table for all payment records for this day
------------------------------------------------------------------------------
if object_id('tempdb..#payments') is not null
begin
drop table #payments
end
create table #payments (paymentid int,
customerid int,
ccnumber varchar(20),
sequence varchar(10),
authcode varchar(30),
authdate datetime ,
amount money,
enterdate datetime)
------------------------------------------------------------------------------
--grab the payments for this date range and insert into temp table
------------------------------------------------------------------------------
insert into #payments
select
p.paymentid,
p.customerid,
p.ccnumber,
p.sequence,
p.authcode,
p.authdate,
round(p.amount,2) as amount,
p.enterdate
from
billing.dbo.payments p
/* indexes: customerid
paymentid, scpoolid
authdate */
paymentprocessing.dbo.auth_log l on l.paymentid = p.paymentid
/* auth_log non-clustered index on paymentid exists */
where
p.authdate between @sdate and @edate
and p.void = 0
and p.scpoolid = @scpoolid
and p.paymethodid = 5
and p.amount <> 0
and ( p.processor = 'processor'
or
p.processor is null
and l.paymentid is null
------------------------------------------------------------------------------
--grab the ccresponse records for this date range
------------------------------------------------------------------------------
insert into #ccresponse
select ccr.ccrespid,
ccr.customerid,
round(ccr.amount,2) as amount,
ccr.ccnumber,
ccr.ccexpire,
ccr.response,
ccr.authdate,
ccr.trxno,
ccr.scpoolserverxrefid
from
billing.dbo.ccresponse ccr
/* indexes: ccnumber
customerid
ccrespid
trxno */
left join paymentprocessing.dbo.auth_log l on l.sequence = ccr.trxno
and l.customerid = ccr.customerid
and l.ccnumber = ccr.ccnumber
and l.amount= ccr.amount
/* indexes: paymentid */
where
ccr.authdate between @sdate and @edate
and ccr.amount != 0
and ccr.scpoolserverxrefid = -1
and ccr.successfultrx = 1
and l.paymentid is null
------------------------------------------------------------------------------
--insert into recon
------------------------------------------------------------------------------
insert into dbo.recon
select
ccr.ccrespid,
ccr.customerid,
p.amount as ccr_amount,
case when cct.trans_action = 2
then cct.amount * -1
else cct.amount end as trans_amount,
cct.trans_action,
case when ccr.trxno = 'partnertrx' then 0
else convert(int, ccr.trxno) end as ccr_trxno ,
ccr.ccnumber ,
ccr.ccexpire,
ccr.response as ccr_response,
ccr.authdate as ccr_authdate,
p.paymentid ,
p.authcode,
case when cct.issuer is null and substring(ccr.ccnumber,1,1) = 5 then 'mc'
when cct.issuer is null and substring(ccr.ccnumber,1,1) = 4 then 'visa'
when cct.issuer is null and substring(ccr.ccnumber,1,1) = 6 then 'disc'
when cct.issuer is null and substring(ccr.ccnumber,1,1) = 3 then 'amex'
else cct.issuer end as issuer,
@ccserverid,
cct.trans_date,
'processor' as serverpath
from
#ccresponse ccr
inner join #payments p on p.customerid= ccr.customerid
and p.ccnumber = ccr.ccnumber
and p.sequence = ccr.trxno
left join paymentprocessing.dbo.cctrans cct on cct.number = ccr.trxno
and cct.ccserverid = @ccserverid
and cct.client_id = @client_id
and cct.trans_date between @sdate and @edate
/* indexes: ccserverid
trans_date
number
client_id
cctransid */
------------------------------------------------------------------------------------------
--insert into recon from cctrans where there are no matching ccresponse records
------------------------------------------------------------------------------------------
insert into dbo.recon
select
null,
cct.ticket,
null as ccr_amount,
case when cct.trans_action = 2
then cct.amount * -1
else cct.amount end as trans_amount,
cct.trans_action,
cct.number as ccr_trxno ,
cct.card as ccnumber ,
cct.expdate as ccexpire,
cct.response as ccr_response,
null as ccr_authdate,
null as paymentid ,
cct.auth,
cct.issuer,
cct.ccserverid,
cct.trans_date,
'rita' as serverpath
from
paymentprocessing.dbo.cctrans cct
/* indexes: ccserverid
trans_date
number
client_id
cctransid */
inner join paymentprocessing.dbo.ccsettle ccs on ccs.number = cct.number
and ccs.client_id = @client_id
/* indexes: ccserverid
settle_date
settle_number
number
ccsettleid
client_id */
left join paymentprocessing.dbo.recon r on r.ccr_trxno = cct.number
/* indexes: ccr_trxno */
where
cct.ccserverid = @ccserverid
and
cct.trans_date between @sdate and @edate
and
r.ccrespid is null
if exists (select top 1 serverid from paymentprocessing.dbo.recon where serverid = @ccserverid)
begin
insert into paymentprocessing.dbo.recon
select
null,
null,
0 as ccr_amount,
0 as trans_amount,
0,
'' as ccr_trxno ,
'' as ccnumber ,
'' as ccexpire,
'' as ccr_response,
null as ccr_authdate,
null as paymentid ,
'',
'xxxx',
@ccserverid,
null,
''
end
if object_id('tempdb..#ccresponse') is not null
begin
drop table #ccresponse
end
if object_id('tempdb..#payments') is not null
begin
drop table #payments
end
select count(1) as [count] from paymentprocessing.dbo.recon
return 0
end
-
Hi,
Try adding clustered index on authdate column of billing.dbo.payments if not already existing. Since authdate is searched for a range of values, clustered index is recommended.
Hope this helps.
Thanks,
Amol
-
Hi Amol,
There is a two column clustered index already existing in billing.dbo.payments and it is on paymentid and scpoolid.
Thanks!
-
Now that I can see what you are doing I suggest this.
I have done similar code that I have found numerous ways to write and each time something suprising happens.
I am curious that maybe "l.paymentid is null" may be using the index for that table in some manner or maybe another field is causing an index choice to be an issue.
However if you data will be distinct without the need of a distinct keyword then you may be able to do something like the following and see what the effect is.
Note: Try moving various pieces from the Where to the Having and see what happens. May be one or more of the where conditions is the root cause. Noticed this on an Oracle query with a cost of 116000 when I changed to something like this it dropped to a cost of 17000 and in that istuation it wasn't an index being choosen that was the issue as the paln didn't change in any visible way.
------------------------------------------------------------------------------
--grab the payments for this date range and insert into temp table
------------------------------------------------------------------------------
insert into #payments
select
p.paymentid,
p.customerid,
p.ccnumber,
p.sequence,
p.authcode,
p.authdate,
round(p.amount,2) as amount,
p.enterdate
from
billing.dbo.payments p
LEFT JOIN
paymentprocessing.dbo.auth_log l
on
l.paymentid = p.paymentid
where
p.authdate between @sdate and @edate
and p.void = 0
and p.scpoolid = @scpoolid
and p.paymethodid = 5
and p.amount != 0
and IsNull(p.processor,'processor') = 'processor'
Group by
p.paymentid,
p.customerid,
p.ccnumber,
p.sequence,
p.authcode,
p.authdate,
round(p.amount,2),
p.enterdate,
l.paymentid
HAVING
l.paymentid is null
Keep in mind thou, if the results would produce duplicate rows then this will not be an option but you might still want to test it to see the impact.
-
Sorry try this first if you havent already read.
This method handles the conditions for the payments table at the time of the join.
This way may significantly reduce the number of items the where clause then has to process.
------------------------------------------------------------------------------
--grab the payments for this date range and insert into temp table
------------------------------------------------------------------------------
insert into #payments
select
p.paymentid,
p.customerid,
p.ccnumber,
p.sequence,
p.authcode,
p.authdate,
round(p.amount,2) as amount,
p.enterdate
from
billing.dbo.payments p
LEFT JOIN
paymentprocessing.dbo.auth_log l
on
l.paymentid = p.paymentid AND
p.authdate between @sdate and @edate
and p.void = 0
and p.scpoolid = @scpoolid
and p.paymethodid = 5
and p.amount != 0
and IsNull(p.processor,'processor') = 'processor'
where
l.paymentid is null
-
Thank you for the suggestion. I'll try that. I wanted to also note, as I have been fiddling with this all day, that I ran each query individually and I think the problem is in the second insert into the recon table (this is reprinted below). That alone took an hour and 50 minutes to run. The two tables that are being joined (cctrans and ccsettle) both have over three million records in it. If I count only the relevant records from cctrans, it is 39,000. But all records in ccsettle are, for the most part, relevant because just about all 3 million records have a client_id of what I am passing in my tests.
And I also changed the below query to use a subquery in the WHERE clause instead of a LEFT JOIN.
------------------------------------------------------------------------------------------
--insert into recon from cctrans where there are no matching ccresponse records
------------------------------------------------------------------------------------------
insert into dbo.recon
select
null,
cct.ticket,
null as ccr_amount,
case when cct.trans_action = 2
then cct.amount * -1
else cct.amount end as trans_amount,
cct.trans_action,
cct.number as ccr_trxno ,
cct.card as ccnumber ,
cct.expdate as ccexpire,
cct.response as ccr_response,
null as ccr_authdate,
null as paymentid ,
cct.auth,
cct.issuer,
cct.ccserverid,
cct.trans_date,
'rita' as serverpath
from
paymentprocessing.dbo.cctrans cct
/* indexes: ccserverid
trans_date
number
client_id
cctransid */
inner join paymentprocessing.dbo.ccsettle ccs on ccs.number = cct.number
and ccs.client_id = @client_id
/* indexes: ccserverid
settle_date
settle_number
number
ccsettleid
client_id */
left join paymentprocessing.dbo.recon r on r.ccr_trxno = cct.number
/* indexes: ccr_trxno */
where
cct.ccserverid = @ccserverid
and
cct.trans_date between @sdate and @edate
and
r.ccrespid is null
-
Try like this then
insert into dbo.recon
select
null,
cct.ticket,
null as ccr_amount,
case when cct.trans_action = 2
then cct.amount * -1
else cct.amount end as trans_amount,
cct.trans_action,
cct.number as ccr_trxno ,
cct.card as ccnumber ,
cct.expdate as ccexpire,
cct.response as ccr_response,
null as ccr_authdate,
null as paymentid ,
cct.auth,
cct.issuer,
cct.ccserverid,
cct.trans_date,
'rita' as serverpath
from
paymentprocessing.dbo.cctrans cct
/* indexes: ccserverid
trans_date
number
client_id
cctransid */
inner join paymentprocessing.dbo.ccsettle ccs
on ccs.number = cct.number
and ccs.client_id = @client_id AND
cct.ccserverid = @ccserverid
and
cct.trans_date between @sdate and @edate
/* indexes: ccserverid
settle_date
settle_number
number
ccsettleid
client_id */
left join paymentprocessing.dbo.recon r on r.ccr_trxno = cct.number
/* indexes: ccr_trxno */
where
r.ccrespid is null
Again it is trying to get as much work join at the join time than at the where.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply