避免在 where 子句中使用函数，因为它会导致性能问题? [英] Avoid function in where clause,due to that its causing performance issue?

问题描述

每当我执行程序时遇到性能问题，我需要在哪里更改程序以提高性能?

Whenever I'm executing the procedure I got performance issue, where do I need to change the procedure to increase the performance?

我在 where 子句中调用了 table 函数，我需要在不使用字符串的情况下优化此过程.

I have called table function in where clause I need to optimize this procedure without using string.

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

我需要避免在 where 子句中使用函数并解决性能问题.

I need to avoid function in where clause and resolve the performance issue.

推荐答案

您可以在存储过程中使用 SPLIT 和 INNER JOIN 的结果在主查询中构建临时表.

You can build temporary tables in your stored procedure with the result of the SPLIT and INNER JOIN those temporary tables in your main query.

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 

这篇关于避免在 where 子句中使用函数，因为它会导致性能问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！