Avoid using functions in where clauses as it causes performance issues?
Vikaskubal
Whenever I execute a program, I have performance issues, where do I need to change the program to improve performance?
I am calling a table function in a where clause and I need to optimize this process without using strings.
CREATE PROC proc_productwise_report @cmp_id VARCHAR(max), @unitcode VARCHAR(max), @gr_code VARCHAR(max), @store_code VARCHAR(max), @from_dt VARCHAR(20), @to_dt VARCHAR(20)
AS
BEGIN
SELECT sh.cmp_id, d.unitcode, d.store_code, st.item_code AS product, d.item_code, im.item_desc, SUM(charge_qty) AS challan_qty
FROM ps_invenstatic sh
INNER JOIN ps_invenstaticdet st ON sh.cmp_id = st.cmp_id
AND sh.sys_no_id = st.sys_no_id
AND sh.doc_id = st.doc_id
AND sys_doc_type = 'PSCH'
INNER JOIN ps_invenissu h ON sh.cmp_id = h.cmp_id
AND sh.doc_type = h.ref_doc_type
AND sh.doc_no = h.ref_doc_no
AND h.prod_code = st.item_code
INNER JOIN ps_invenissudet d ON h.cmp_id = d.cmp_id
AND h.sys_no_id = d.sys_no_id
AND h.doc_id = d.doc_id
INNER JOIN ps_itemmas im ON sh.cmp_id = im.cmp_id
AND im.item_code = d.item_code
WHERE sh.cmp_id IN (
SELECT *
FROM utilfn_split(@cmp_id, ',')
)
AND d.unitcode IN (
SELECT *
FROM utilfn_split(@unitcode, ',')
)
AND im.gr_code IN (
SELECT *
FROM utilfn_split(@gr_code, ',')
)
AND d.store_code IN (
SELECT *
FROM utilfn_split(@store_code, ',')
)
AND h.doc_dt BETWEEN convert(DATETIME, @from_dt, 103)
AND convert(DATETIME, @to_dt, 103)
AND sh.Stat_Code <> 'CA'
GROUP BY sh.cmp_id, d.unitcode, d.store_code, st.item_code, d.item_code, im.item_desc
END
I need to avoid using functions in where clauses and solve performance issues.
Radu Georgius
You can use the results of SPLIT and INNER JOIN in the main query to build temporary tables in stored procedures.
CREATE PROC proc_productwise_report @cmp_id VARCHAR(max), @unitcode VARCHAR(max),
@gr_code VARCHAR(max), @store_code VARCHAR(max), @from_dt VARCHAR(20), @to_dt VARCHAR(20)
AS
BEGIN
SELECT *
INTO #cmp_ids
FROM utilfn_split(@cmp_id, ',');
SELECT *
INTO #unitcodes
FROM utilfn_split(@unitcode, ',');
SELECT *
INTO #gr_codes
FROM utilfn_split(@gr_code, ',');
SELECT *
INTO #store_codes
FROM utilfn_split(@store_code, ',');
SELECT
sh.cmp_id
, d.unitcode
, d.store_code
, st.item_code AS product
, d.item_code
, im.item_desc
, SUM(charge_qty) AS challan_qty
FROM ps_invenstatic sh
INNER JOIN ps_invenstaticdet st
ON sh.cmp_id = st.cmp_id
AND sh.sys_no_id = st.sys_no_id
AND sh.doc_id = st.doc_id
AND sys_doc_type = 'PSCH'
INNER JOIN ps_invenissu h
ON sh.cmp_id = h.cmp_id
AND sh.doc_type = h.ref_doc_type
AND sh.doc_no = h.ref_doc_no
AND h.prod_code = st.item_code
INNER JOIN ps_invenissudet d
ON h.cmp_id = d.cmp_id
AND h.sys_no_id = d.sys_no_id
AND h.doc_id = d.doc_id
INNER JOIN ps_itemmas im
ON sh.cmp_id = im.cmp_id
AND im.item_code = d.item_code
INNER JOIN #cmp_ids tci on sh.cmp_id = tci.[value]
INNER JOIN #unitcodes tuc on d.unitcode = tuc.[value]
INNER JOIN #gr_codes tgr on im.gr_code = tgr.[value]
INNER JOIN #store_codes tsc on d.store_code = tsc.[value]
WHERE h.doc_dt BETWEEN convert(DATETIME, @from_dt, 103)
AND convert(DATETIME, @to_dt, 103)
AND sh.Stat_Code <> 'CA'
GROUP BY sh.cmp_id
, d.unitcode
, d.store_code
, st.item_code
, d.item_code
, im.item_desc
END