I’m trying to find a way to simplify my stored procedure. When I try to query using single parameter it went well but when using array, the retrieval process took so long. Any idea how will I simplify this?
USE [TEST]
GO
/****** Object: StoredProcedure [TESTSU].[SelectReport] Script Date: 06/16/2022 9:29:25 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [TESTSU].[SelectReport] (@StringAsArray [TESTSU].[StringArray] READONLY)
AS BEGIN
SELECT
'SAM' as PLATFORM,
'SAM'+ '0'+ordh_sysrefno as ZINDEX,
ad_sapcode AS "SAP ADVERTISER CODE",
ad_advcde AS "BMS ADVERTISER CODE",
ad_advnme AS "ADVERTISER NAME",
ag_sapcode AS "SAP AGENCY CODE",
ag_agencde AS "BMS AGENCY CODE",
ag_agennme AS "AGENCY NAME",
ordh_docno AS "TO NUMBER",
ordh_createdate AS "TO CREATE DATE",
ordh_conttp AS "CONTRACT TYPE",
tt_desc AS "TELECAST TYPE",
'' AS "PACKAGE TYPE",
'' AS "REVENUE TYPE",
sapcode as "SAP PROGRAM CODE",
pg_prgcode as "BMS PROGRAM CODE",
pg_prgname as "PROGRAM",
ordd_teledte AS "TELECAST DATE",
ordd_agencost AS "INTERNAL COST",
ordd_billcost AS "BILLING COST",
'PHP' AS CURRENCY,
'' AS PRODUCTION,
spd_cpno as "CP NUMBER",
cph_cpdte as "CP DATE",
cph_prndte as "CP PRINT DATE",
CASE ordh_conttp
WHEN 'C'
THEN spd_invno
WHEN 'X'
THEN spd_exinvno
WHEN 'P'
THEN spd_pbinvno
ELSE '' END AS "INVOICE NUMBER",
COALESCE(A.invh_agencom, COALESCE(B.invh_agencom,C.invh_agencom)) as "COMMISSION AMOUNT",
COALESCE(A.invh_vat,COALESCE(B.invh_vat,C.invh_vat)) as "VAT AMOUNT",
COALESCE(A.invh_billamt,COALESCE(B.invh_billamt,C.invh_billamt)) as "BILLED AMOUNT",
spd_stat as "STATUS"
from
SERVER.DB2.ADMINSA.ord_hdr INNER JOIN
SERVER.DB2.ADMINSA.ord_dtl ON (ordh_sysrefno = ordd_sysrefno) INNER JOIN
SERVER.DB2.ADMINSA.spot_dtl ON (ordd_sysrefno = spd_sysrefno and ordd_dtlno = spd_dtlno ) INNER JOIN
SERVER.DB2.ADMINSA.program ON (pg_prgcode = ordd_prgcode ) INNER JOIN
SERVER.DB2.ADMINSA.advertiser ON (ad_advcde = ordh_advcde) INNER JOIN
SERVER.DB2.ADMINSA.agency ON (ag_agencde = ordh_agencde) INNER JOIN
SERVER.DB2.ADMINSA.cp_hdr ON (ordh_sysrefno = cph_refno) INNER JOIN
SERVER.DB2.ADMINSA.cp_dtl ON (cph_cpno = cpd_cpno and ordd_teledte = cpd_teledte and ordd_teletp = cpd_teletp and ordd_prgcode = cpd_prgcode and ordd_pcode = cpd_pcode and ordd_version = cpd_version and ordd_spotlen = cpd_spotlen) FULL OUTER JOIN
SERVER.DB2.ADMINSA.inv_hdr A ON (spd_invno = A.invh_invno and spd_sysrefno = A.invh_refno) FULL OUTER JOIN
SERVER.DB2.ADMINSA.inv_hdr B ON (spd_exinvno = B.invh_invno and spd_sysrefno = B.invh_refno) FULL OUTER JOIN
SERVER.DB2.ADMINSA.inv_hdr C ON (spd_exinvno = C.invh_invno and spd_sysrefno = C.invh_refno) INNER JOIN
SERVER.DB2.ADMINSA.telecast_type ON (ordd_teletp = tt_code) left outer join
SERVER.TEST.TESTSU.programs_season on (platform = 'SAM' and pg_prgcode = bmscode and
cpd_teledte BETWEEN date_start AND date_end)
WHERE
cpd_cpno in (Select LTRIM(RTRIM(StringValue)) FROM @StringAsArray)
END
Someone suggested to me to put StringAsArray into temp table but I don’t know exactly what they’re trying to suggest. Any help would be very much appreciated. Thank you.
Advertisement
Answer
So with the definition of the TESTDB.StringArray as TABLE ([StringValue] varchar(15)) clarified we can look at the table joins.
Your parameter is in fact already a table. As @Charlieface mentions in the comments, I’ll assume that you already have used TRIM on the values to populate the parameter before calling the Stored Procedure.
I was unable to infer which table the column sapcode came from so I have not aliased it. Likewise for the platform column in the LEFT OUTER JOIN TEST.TESTSU.programs_season condition.
ALTER PROCEDURE [TESTSU].[SelectReport] (
@StringAsArray [TESTSU].[StringArray] READONLY
)
AS
BEGIN
SELECT 'SAM' as PLATFORM,
'SAM' + '0' + ord_hdr.ordh_sysrefno as ZINDEX,
advertiser.ad_sapcode AS "SAP ADVERTISER CODE",
advertiser.ad_advcde AS "BMS ADVERTISER CODE",
advertiser.ad_advnme AS "ADVERTISER NAME",
agency.ag_sapcode AS "SAP AGENCY CODE",
agency.ag_agencde AS "BMS AGENCY CODE",
agency.ag_agennme AS "AGENCY NAME",
ord_hdr.ordh_docno AS "TO NUMBER",
ord_hdr.ordh_createdate AS "TO CREATE DATE",
ord_hdr.ordh_conttp AS "CONTRACT TYPE",
telecast_type.tt_desc AS "TELECAST TYPE",
'' AS "PACKAGE TYPE",
'' AS "REVENUE TYPE",
sapcode as "SAP PROGRAM CODE",
program.pg_prgcode as "BMS PROGRAM CODE",
program.pg_prgname as "PROGRAM",
ord_dtl.ordd_teledte AS "TELECAST DATE",
ord_dtl.ordd_agencost AS "INTERNAL COST",
ord_dtl.ordd_billcost AS "BILLING COST",
'PHP' AS CURRENCY,
'' AS PRODUCTION,
spot_dtl.spd_cpno as "CP NUMBER",
cp_hdr.cph_cpdte as "CP DATE",
cp_hdr.cph_prndte as "CP PRINT DATE",
CASE ord_hdr.ordh_conttp
WHEN 'C' THEN spot_dtl.spd_invno
WHEN 'X' THEN spot_dtl.spd_exinvno
WHEN 'P' THEN spot_dtl.spd_pbinvno
ELSE ''
END AS "INVOICE NUMBER",
COALESCE(A.invh_agencom, COALESCE(B.invh_agencom, C.invh_agencom)) as "COMMISSION AMOUNT",
COALESCE(A.invh_vat, COALESCE(B.invh_vat, C.invh_vat)) as "VAT AMOUNT",
COALESCE(A.invh_billamt, COALESCE(B.invh_billamt, C.invh_billamt)) as "BILLED AMOUNT",
spot_dtl.spd_stat as "STATUS"
FROM DB2.ADMINSA.ord_hdr ord_hdr
INNER JOIN DB2.ADMINSA.ord_dtl ord_dtl
ON (ord_hdr.ordh_sysrefno = ord_dtl.ordd_sysrefno)
INNER JOIN DB2.ADMINSA.spot_dtl spot_dtl
ON (ord_dtl.ordd_sysrefno = spot_dtl.spd_sysrefno
AND ord_dtl.ordd_dtlno = spot_dtl.spd_dtlno )
INNER JOIN DB2.ADMINSA.program program
ON (program.pg_prgcode = ord_dtl.ordd_prgcode)
INNER JOIN DB2.ADMINSA.advertiser advertiser
ON (advertiser.ad_advcde = ord_hdr.ordh_advcde)
INNER JOIN DB2.ADMINSA.agency agency
ON (agency.ag_agencde = ord_hdr.ordh_agencde)
INNER JOIN DB2.ADMINSA.cp_hdr cp_hdr
ON (ord_hdr.ordh_sysrefno = cp_hdr.cph_refno)
INNER JOIN DB2.ADMINSA.cp_dtl cp_dtl
ON (cph_cpno = cp_dtl.cpd_cpno
AND ord_dtl.ordd_teledte = cp_dtl.cpd_teledte
AND ord_dtl.ordd_teletp = cp_dtl.cpd_teletp
AND ord_dtl.ordd_prgcode = cp_dtl.cpd_prgcode
AND ord_dtl.ordd_pcode = cp_dtl.cpd_pcode
AND ord_dtl.ordd_version = cp_dtl.cpd_version
AND ord_dtl.ordd_spotlen = cp_dtl.cpd_spotlen)
FULL OUTER JOIN DB2.ADMINSA.inv_hdr A
ON (spot_dtl.spd_invno = A.invh_invno
AND spot_dtl.spd_sysrefno = A.invh_refno)
FULL OUTER JOIN DB2.ADMINSA.inv_hdr B
ON (spot_dtl.spd_exinvno = B.invh_invno
AND spot_dtl.spd_sysrefno = B.invh_refno)
FULL OUTER JOIN DB2.ADMINSA.inv_hdr C
ON (spot_dtl.spd_exinvno = C.invh_invno
AND spot_dtl.spd_sysrefno = C.invh_refno)
INNER JOIN DB2.ADMINSA.telecast_type telecast_type
ON (ord_dtl.ordd_teletp = telecast_type.tt_code)
LEFT OUTER JOIN TEST.TESTSU.programs_season programs_season
ON (platform = 'SAM'
AND program.pg_prgcode = programs_season.bmscode
AND cp_dtl.cpd_teledte BETWEEN programs_season.date_start AND programs_season.date_end)
INNER JOIN @StringAsArray saa
ON cp_dtl.cpd_cpno = saa.StringValue
WHERE
END