SQL Server存储过程执行停滞，单独运行查询正常的问题排查与解决

问题现象 #

• 单独执行存储过程内的查询语句，可在1~5秒内返回1000+至10000+行数据
• 执行完整存储过程时，表变量的创建及数据插入步骤会出现长时间停滞

相关代码片段 #

存储过程声明 #

USE [ERP_Database]
GO

/****** Object:  StoredProcedure [dbo].[PROC_Export_Data]    Script Date: 09/09/2022 10:31:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[PROC_Export_Data]
    @ReferenceDate AS DATE,
    @SocietyCodeAS VARCHAR(MAX), 
    @Export BIT
AS
BEGIN
    [Here are the queries]

第一处表变量插入代码 #

DECLARE @PTransco AS TABLE (IDProduit INT, IDFournisseur INT, ReferenceFournisseur VARCHAR(100), IDUniteAchat INT, CoefficientVentePartenaire NUMERIC(19,6) )
INSERT INTO @PTransco (IDProduit, IDFournisseur, ReferenceFournisseur, IDUniteAchat, CoefficientVentePartenaire)
SELECT P.ID, IFA.IDFournisseur, MAX(IFA.ReferenceFournisseur), MAX(IFA.IDUniteAchat), MAX(IFAU.CoefficientVentePartenaire)
FROM Product P
    INNER JOIN Supplier IFA ON IFA.IDProduit = P.ID
    INNER JOIN ThirdParty F ON F.ID = IFA.IDFournisseur
    LEFT OUTER JOIN Supplier_U IFAU ON IFAU.ID = IFA.ID
WHERE F.CodeTiers IN (SELECT CodeTiers FROM @ListeTiersSociete) --Another local variable
GROUP BY P.ID, IDFournisseur
UNION
select P.ID, NULL, '999999', '280', 1 from TaxParam_Head PTE
    inner join ThirdParty T on T.ID = PTE.IDSociete
    inner join TaxParam_Product PTP on PTP.IDEntete = PTE.ID
    inner join Product P on P.ID=PTP.IDProduit
where T.CodeTiers IN (SELECT CodeTiers FROM @ListeTiersSociete)

第二处出问题的表变量插入代码 #

DECLARE @BonsDateExport AS TABLE (IDBon INT, CodeSociete VARCHAR(50), DateBon DATE, DateExport DATE, DateFiltre DATE)
    
INSERT INTO @BonsDateExport (IDBon, CodeSociete, DateBon)
SELECT EF.ID, SOC.CodeTiers, EF.DateOperation
 FROM InvoiceLine LF
        inner join InvoiceHeader EF on EF.ID=LF.IDFacture
        INNER JOIN OrderHeader EB on LF.NumeroBon = EB.NumeroBon
        INNER JOIN UTIL_FILTER_PartnerOrder F ON F.ID = EB.ID
        INNER JOIN ThirdParty SOC ON SOC.ID = EB.IDSociete
        INNER JOIN DocumentType TD ON TD.ID = EB.IDTypeDocument
        LEFT OUTER JOIN OrderHeader_U EBU ON EBU.ID = EB.ID
WHERE SOC.CodeTiers IN (SELECT CodeTiers FROM @ListeTiersSociete)
    AND TD.CodeTypeDocument IN ('BLF', 'BLD')
    AND EBU.IDEventExportDesBLPartenaires IS NULL

排查过程与结论 #

• 初始怀疑查询语句存在错误，但单独执行所有查询均能快速返回结果
• 进一步排查发现，问题实际出在第二处表变量的INSERT语句：该语句的SELECT部分单独执行仅需1秒即可返回1000+行数据，但执行完整INSERT时会停滞
• 最终定位到问题根源为存储过程开头创建的仅含1行数据的局部变量@ListeTiersSociete，推测与参数嗅探有关，移除该变量相关逻辑后问题解决

内容的提问来源于stack exchange，提问作者Falyna