Power Platform Community Forum Thread Details

I have a structured procedure that works great in SQL SMSS. The execution takes around 5 seconds. When I invoke the procedure from powerapps, through a SQL structured procedure action, the run-time varies from 5s to several minutes. Same data in and out. The SQL server is VERY lightly loaded, in fact there may not be anyone at all using the sever when I'm invoking the procedure. Why is there such a wide variance in performance?

This procedure is quite simple. It takes a long text string of numbers separated by a comma, creates a table from this, then joins with a view in SQL. The resulting table is the desired result.

[dbo].[FilterByPartNumbers]
    @PartNumbers NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    -- Create a temporary table for parsed part numbers
    CREATE TABLE #ParsedPartNumbers (
        PartNumber NVARCHAR(255) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL PRIMARY KEY
    );

    -- Populate the temporary table
    INSERT INTO #ParsedPartNumbers (PartNumber)
    SELECT DISTINCT TRIM(value) COLLATE Latin1_General_CI_AS_KS_WS
    FROM STRING_SPLIT(@PartNumbers, ',');

    -- Select data directly from dbo.View_all_pdm_latest_pa
    SELECT 
        pp.PartNumber,
        v.*,
        CASE 
            WHEN v.PartNumber IS NULL THEN 1
            ELSE 0
        END AS Acc_Only
    FROM #ParsedPartNumbers pp
    LEFT JOIN dbo.View_all_pdm_latest_pa v
        ON pp.PartNumber = v.PartNumber COLLATE Latin1_General_CI_AS_KS_WS;

    -- Drop the temporary table
    DROP TABLE #ParsedPartNumbers;

    SET NOCOUNT OFF;
END;

Flow action: