SQL Stored Procedure runs forever, but query alone ends normally-CodePudding

I'm trying to create a stored procedure on a SQL Server database used by our ERP, but I encounter a weird behavior.

When running the query within the procedure, I am able to fetch 10,000 rows in about 5 seconds.

When I try to run the procedure, it runs forever while trying to create a table variable:

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)

The procedure is declared as follows:

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]

Have you ever encountered that kind of behavior? Is there something wrong in my declaration?

I first envisioned that my queries were wrong, so I ran them separately, but the results were almost instantaneous.
Running all my queries out of the procedure work as expected (results are fetched).

EDIT : Thanks to @Stu, I realized the error is on the following query :

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

The part that hangs is the insert. Note that the SELECT fetches 1000 lines in less than a second when ran on its own. The other fields are updated later depending on the previously inserted values.

EDIT 2 : I managed to make it work. The hanging came from the query on the local variable @ListeTiersSociete created in the beginning of the procedure and containing only 1 row.

From what I understood from this other question, it may or may not have something to do with parameter sniffing. I checked with the end users, and I can safely remove this particular part.

Thanks a lot for your time. I'll add this as an answer right away.

CodePudding user response：

I would suggest to replace Table variable with Local Temp Table,

CREATE TABLE #BonsDateExport   (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

CodePudding user response：

The issue seemed to come from this particular line:

WHERE SOC.CodeTiers IN (SELECT CodeTiers FROM @ListeTiersSociete)

Or more accurately, from the definition of this local variable (even though the resulting table only contained 1 row) :

DECLARE @ListeTiersSociete AS TABLE (ID INT, CodeTiers VARCHAR(100))
    INSERT INTO @ListeTiersSociete (ID, CodeTiers)
    SELECT SOC.ID, SOC.CodeTiers
    FROM ThirdParty SOC
        INNER JOIN dbo.STR_Split(@LocalListeSoc, '|') LCS ON LCS.Value = SOC.CodeTiers
    WHERE SOC.CodeTiers IN ('SDL')--('NVL', 'SDL', 'LAC')

(The procedure is supposed to be expanded to take into account more societies in the near future)

Removing this variable and using the input variable directly after restraining it to a single value in the ERP made the procedure run smoothly.

Thanks a lot for your help.