SQL Stored Procedure runs forever, but query alone ends normally

Question

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.

When the procedure hangs, use sp_whoisactive to find out what is blocking and on what resources. — Stu, Sep 09 '22 at 09:59
That statement and the definition don't appear to be related either. None of your parameters are used in the statement, so why define them? Also, do you need to use `UNION` rather than `UNION ALL`? It doesn't look like a duplicate row can be returned in the lower statement, as `IFA.IDFournisseur` cannot have a `NULL` value in the top statement, and you define the column in the same position as `NULL` in the bottom statement. Could there be duplicate rows returned within the statements themselves that you want to eliminate? — Thom A, Sep 09 '22 at 10:00
See [this article](https://www.sommarskog.se/query-plan-mysteries.html) by Erland Sommarskog on the myriad of reasons why queries can have different behaviors in different circumstances, and how to deal with them. Step 1 is always to compare actual query plans for clear differences. — Jeroen Mostert, Sep 09 '22 at 10:03
@GuidoG I just run this: EXEC dbo.PROC_Export_Data '09/09/2022', 'SDL', 0 — Falyna, Sep 09 '22 at 10:05
@Larnu I did not paste the whole procedure, only the bit that hangs. All three variables are used in the procedure; should I paste it all? The UNION ALL part was an oversight, thanks for pointing it out. — Falyna, Sep 09 '22 at 10:06
@Falyna *don't* use localized strings as date literals. Use the unambiguous `YYYYMMDD` format. As for `only the bit that hangs.` what if it hangs because some *previous* operation caused incompatible locks, thus blocking the query? Did you check `Activity Monitor` to see what's going on, why your query is blocked? — Panagiotis Kanavos, Sep 09 '22 at 10:29
@PanagiotisKanavos concerning the format, I used the same as my ERP (I have another procedure running smoothly that uses this format). I'll check the Activity Monitor right away, I'll keep you updated. — Falyna, Sep 09 '22 at 10:32
Dates have no format, they're binary values. It's almost *certain* that the ERP doesn't store dates as strings - that's a huge bug. There's no way to tell whether `4/7/2022` is April 7 or July 4th. How the ERP *displays* dates to users has nothing to do with how dates are actually handled or stored. What you assume works, uses the serves' date settings to parse that string into a date. That's a constant source of often hilarious but sometimes extremely serious problems. DON'T do that. The `YYYYMMDD` format isn't affected by locales — Panagiotis Kanavos, Sep 09 '22 at 10:38
`I have another procedure running smoothly that uses this format` no you don't. You haven't noticed the problems yet. Check [The Spanish family wrongly accused of child pornography due to a mistake reading a date](https://english.elpais.com/spanish_news/2020-09-08/the-spanish-family-wrongly-accused-of-child-pornography-due-to-a-mistake-reading-a-date.html). Fixing the bug is trivial - use date types and parameters, use unambiguous formats when you absolutely have to use a string, make sure clients call stored procedures using strongly typed parameters instead of strings — Panagiotis Kanavos, Sep 09 '22 at 10:41
For query performance issues, at a minimum we need to see your table *and index* definitions, and please share the fast and slow query plans via https://pastetheplan.com. This question is not answerable without that. — Charlieface, Sep 09 '22 at 10:52
@PanagiotisKanavos Running the procedure with the date in the aforementioned format makes no difference. — Falyna, Sep 09 '22 at 11:41
I would suggest replacing Table Variable with Single Hash Temp Table. Usually, when huge data is inserted into Table Variable it will impact the execution time. — Srinivasan Rajasekaran, Sep 09 '22 at 12:39

score 1 · Answer 1 · answered Sep 09 '22 at 12:40

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

score 0 · Accepted Answer · answered Sep 12 '22 at 06:59

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.

SQL Stored Procedure runs forever, but query alone ends normally

2 Answers2