We have people running invoice printing jobs. The query on the database seems to be taking more than 10 minutes to run. The query seems to be spending the most time in a table scan on the SINVOICE table. Can I improve the performance by creating any index on SINVOICE or any other table? The problematic SQL is:
Can I improve the performance by creating any index
SELECT sinvoiced.itmref_0,
sinvoicev.num_0,
sinvoicev.prityp_0,
sinvoiced.qty_0,
sinvoiced.sau_0,
sinvoiced.netpri_0,
sinvoiced.amtnotlin_0,
sinvoice.bpr_0,
sinvoice.bprnam_0,
sinvoice.bpaaddlig_0,
sinvoice.bpaaddlig_1,
sinvoice.bpaaddlig_2,
sinvoice.cty_0,
sinvoice.poscod_0,
sinvoice.cur_0,
sinvoicev.insati_2,
sinvoice.amtati_0,
sinvoicev.insati_0,
sinvoicev.insati_1,
sinvoicev.insati_3,
areportm.usr_0,
areportm.rptcod_0,
areportm.numreq_0,
tabunit.uomdec_0,
tabcur.decnbr_0,
sinvoicev.invtyp_0,
tabcur.eurrat_0,
sinvoice.crynam_0,
facility.fcynam_0,
BPADDRESS_FCY.bpaaddlig_0,
BPADDRESS_FCY.bpaaddlig_1,
BPADDRESS_FCY.bpaaddlig_2,
BPADDRESS_FCY.poscod_0,
BPADDRESS_FCY.cty_0,
BPADDRESS_FCY.tel_0,
BPADDRESS_FCY.fax_0,
BPADDRESS_FCY.bpatyp_0,
BPADDRESS_BPR.bpaaddlig_0,
BPADDRESS_BPR.bpaaddlig_1,
BPADDRESS_BPR.bpaaddlig_2,
BPADDRESS_BPR.poscod_0,
BPADDRESS_BPR.cty_0,
BPADDRESS_BPR.crynam_0,
sinvoice.amtnot_0,
sinvoiced.itmdes_0,
bpartner.bprnam_0,
sinvoice.gte_0,
sinvoice.num_0,
sinvoicev.invref_0,
sinvoicev.sihori_0,
sinvoicev.sihorinum_0,
sinvoiced.sdhnum_0,
bpartner.lan_0,
BPADDRESS_FCY.sat_0,
sinvoice.sat_0,
BPADDRESS_BPR.sat_0,
sinvoice.sta_0,
afctfcy.fnc_0,
afctfcy.prfcod_0,
sinvoicev.bpdaddlig_0,
sinvoicev.bpdaddlig_1,
sinvoicev.bpdaddlig_2,
sinvoicev.bpdposcod_0,
sinvoicev.bpdcty_0,
sinvoicev.bpdsat_0,
sinvoicev.bpdcrynam_0,
sinvoiced.sidlin_0,
sinvoiced.num_0,
SDELIVERY_1.bptnum_0,
BPADDRESS_BPA.bpatyp_0,
BPADDRESS_BPR.bpatyp_0,
areportm.seqreq_0,
tabdepagio.deprat_0,
tabdepagio.deprat_1,
tabdepagio.deprat_2,
tabdepagio.deprat_3,
tabdepagio.deprat_4,
tabdepagio.deprat_5,
tabdepagio.deprat_6,
tabdepagio.deprat_7,
tabdepagio.deprat_8,
tabdepagio.deprat_9,
tabdepagio.deprat_10,
tabdepagio.deprat_11,
pricstruct.bpcbps_0,
sinvoiced.amtatilin_0,
BPADDRESS_FCY.cry_0,
facility.cry_0,
sinvoicev.insordnum_0,
sinvoicev.inslin_0,
sinvoicev.insordnum_1,
sinvoicev.inslin_1,
sinvoicev.insordnum_2,
sinvoicev.inslin_2,
sinvoicev.insordnum_3,
sinvoicev.inslin_3,
sinvoicev.bpdnam_0,
TABLAN_BPR.lanstd_0,
TABLAN_BPR.lanrpl_0,
tabpayterm.ptelin_0,
BPADDRESS_BPA.bpaaddlig_0,
BPADDRESS_BPA.bpaaddlig_1,
BPADDRESS_BPA.bpaaddlig_2,
BPADDRESS_BPA.poscod_0,
BPADDRESS_BPA.sat_0,
BPADDRESS_BPA.crynam_0,
bpdlvcust.bpdnam_0,
sdelivery.shidat_0,
sinvoiced.sohnum_0,
sorder.orddat_0,
sinvoice.strduddat_0,
tabpayterm.nbrday_0,
sinvoicev.invdat_0,
company.leg_0,
sinvoicev.sihoridat_0,
facility.crn_0,
itmmaster.ssu_0,
bpdlvcust.bpdnam_1,
BPADDRESS_FIN.bpatyp_0,
FACILITY_FIN.fcynam_0,
BPADDRESS_FIN.bpaaddlig_0,
BPADDRESS_FIN.bpaaddlig_1,
BPADDRESS_FIN.bpaaddlig_2,
BPADDRESS_FIN.cty_0,
BPADDRESS_FIN.sat_0,
BPADDRESS_FIN.poscod_0,
BPADDRESS_FIN.tel_0,
FACILITY_FIN.crn_0,
FACILITY_FIN.fcy_0,
xx1xysorderm.vetnum_0,
facility.finrspfcy_0,
sinvoice.cpy_0,
sinvoice.fcy_0,
xx1xysorderm.rxid_0,
itmmaster.zaltgraeqv_0,
zfrstselldat.firstselldat_0,
zprdcls.texte_0,
BPADDRESS_FIN.bpaadd_0,
bpcarrier.bptnam_0,
zstojinv.thcperunit_0,
zstojinv.cbdperunit_0,
zstojinv.thcperpkg_0,
zstojinv.cbdperpkg_0,
zstojinv.qty_0,
xx1xysorderm.policynum_0,
tabpayterm.pte_0,
tabmodeliv.mdl_0,
TEXCLOB_DET.texte_0,
TEXCLOB_FIN.texte_0
FROM ((((((((((((((((((((((((
(((
((
((db.test.areportm AREPORTM
INNER JOIN db.test.sinvoicev SINVOICEV
ON areportm.clea1_0 =
sinvoicev.num_0)
INNER JOIN db.test.sinvoice SINVOICE
ON
sinvoicev.num_0 = sinvoice.num_0)
INNER JOIN db.test.tabcur TABCUR
ON sinvoicev.cur_0 = tabcur.cur_0)
INNER JOIN db.test.bpaddress BPADDRESS_BPA
ON
( sinvoicev.bpcord_0 = BPADDRESS_BPA.bpanum_0 )
AND
( sinvoicev.bpaadd_0 = BPADDRESS_BPA.bpaadd_0 ))
LEFT OUTER JOIN db.test.sdelivery SDELIVERY
ON
sinvoicev.sihorinum_0 = sdelivery.sdhnum_0)
LEFT OUTER JOIN db.test.texclob TEXCLOB_FIN
ON
sinvoicev.sihtex2_0 = TEXCLOB_FIN.code_0)
LEFT OUTER JOIN db.test.bpdlvcust BPDLVCUST
ON
( sinvoicev.bpcord_0 = bpdlvcust.bpcnum_0 )
AND
( sinvoicev.bpaadd_0 = bpdlvcust.bpaadd_0 ))
LEFT OUTER JOIN db.test.pricstruct PRICSTRUCT
ON
sinvoicev.plistc_0 = pricstruct.plistc_0)
LEFT OUTER JOIN db.test.bpcarrier BPCARRIER
ON sdelivery.bptnum_0 = bpcarrier.bptnum_0)
INNER JOIN db.test.bpartner BPARTNER
ON sinvoice.bpr_0 = bpartner.bprnum_0)
INNER JOIN db.test.facility FACILITY
ON sinvoice.fcy_0 = facility.fcy_0)
INNER JOIN db.test.sinvoiced SINVOICED
ON sinvoice.num_0 = sinvoiced.num_0)
INNER JOIN db.test.bpaddress BPADDRESS_BPR
ON ( sinvoice.bpainv_0 = BPADDRESS_BPR.bpaadd_0 )
AND ( sinvoice.bpr_0 = BPADDRESS_BPR.bpanum_0 ))
INNER JOIN db.test.afctfcy AFCTFCY
ON sinvoice.fcy_0 = afctfcy.fcy_0)
LEFT OUTER JOIN db.test.tabdepagio TABDEPAGIO
ON sinvoice.dep_0 = tabdepagio.dep_0)
LEFT OUTER JOIN db.test.tabpayterm TABPAYTERM
ON sinvoice.pte_0 = tabpayterm.pte_0)
INNER JOIN db.test.tablan TABLAN_BPR
ON bpartner.lan_0 = TABLAN_BPR.lan_0)
INNER JOIN db.test.bpaddress BPADDRESS_FCY
ON ( facility.bpaadd_0 = BPADDRESS_FCY.bpaadd_0 )
AND ( facility.fcy_0 = BPADDRESS_FCY.bpanum_0 ))
LEFT OUTER JOIN db.test.company COMPANY
ON facility.legcpy_0 = company.cpy_0)
LEFT OUTER JOIN db.test.facility FACILITY_FIN
ON facility.finrspfcy_0 = FACILITY_FIN.fcy_0)
LEFT OUTER JOIN db.test.bpaddress BPADDRESS_FIN
ON ( FACILITY_FIN.fcy_0 = BPADDRESS_FIN.bpanum_0 )
AND
( FACILITY_FIN.bpaadd_0 = BPADDRESS_FIN.bpaadd_0 ))
INNER JOIN db.test.itmmaster ITMMASTER
ON sinvoiced.itmref_0 = itmmaster.itmref_0)
LEFT OUTER JOIN db.test.tabunit TABUNIT
ON sinvoiced.sau_0 = tabunit.uom_0)
LEFT OUTER JOIN db.test.texclob TEXCLOB_DET
ON sinvoiced.sidtex_0 = TEXCLOB_DET.code_0)
LEFT OUTER JOIN db.test.sdelivery SDELIVERY_1
ON sinvoiced.sdhnum_0 = SDELIVERY_1.sdhnum_0)
LEFT OUTER JOIN db.test.sorderq SORDERQ
ON ( ( sinvoiced.sohnum_0 = sorderq.sohnum_0 )
AND ( sinvoiced.soplin_0 = sorderq.soplin_0 ) )
AND ( sinvoiced.soqseq_0 = sorderq.soqseq_0 ))
LEFT OUTER JOIN db.test.zstojinv ZSTOJINV
ON ( sinvoiced.sdhnum_0 = zstojinv.vcrnum_0 )
AND ( sinvoiced.sddlin_0 = zstojinv.vcrlin_0 ))
LEFT OUTER JOIN db.test.zprdcls ZPRDCLS
ON itmmaster.tsicod_4 = zprdcls.ident2_0)
LEFT OUTER JOIN db.test.tabmodeliv TABMODELIV
ON SDELIVERY_1.mdl_0 = tabmodeliv.mdl_0)
LEFT OUTER JOIN db.test.sorder SORDER
ON sorderq.sohnum_0 = sorder.sohnum_0)
LEFT OUTER JOIN db.test.xx1xysorderm XX1XYSORDERM
ON sorder.sohnum_0 = xx1xysorderm.sohnum_0)
LEFT OUTER JOIN db.test.zfrstselldat ZFRSTSELLDAT
ON xx1xysorderm.rxid_0 = zfrstselldat.rxid_0
WHERE areportm.rptcod_0 = N'P1'
AND areportm.numreq_0 = 41830546
AND AREPORTM.USR_0=N'P2'
AND BPADDRESS_FCY.bpatyp_0 = 3
AND BPADDRESS_BPA.bpatyp_0 = 1
AND afctfcy.fnc_0 = N'CONSCSI'
AND afctfcy.prfcod_0 = N'ARCC'
AND areportm.seqreq_0 = 0
AND BPADDRESS_BPR.bpatyp_0 = 1
AND pricstruct.bpcbps_0 = 1
AND tabpayterm.ptelin_0 = 1
ORDER BY sinvoicev.num_0,
sinvoiced.sidlin_0
This was working fine until a couple of weeks ago but now we are facing performance issues. I tried adding an index to the SDELIVERY table but that didn't help.
'''