CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresparameter-passing
Serdia
Serdia

Reputation: 4428

Why adding one more parameter to the Stored Procedure makes it very slow

I have a SP with 5 parameters @State,@StartDate,@EndDate,@AsOfDate,@SICCode. If I execute SP and supply parameters it returns the data ~ 1 sec. Then if I add another parameter @Coverage then it takes ~ 17 sec to execute it.

I looked at the Cache Plan with and without @Coverage parameter and it seems like it looks the same. I used tuning adviser and created all possible indexes.

If I run same code as SELECT statement and provide the same values as parameters in WHERE clause - then I got the result for ~1 sec. So why only when I add this exact one parameter @Coverage it slows down performance so significantly?

If it a parameter sniffing then how can I be sure that it is? Is any ways to check? 

ALTER PROCEDURE [dbo].[EarningPlazaCommercial] 
@State varchar(50),
@StartDate datetime,
@EndDate datetime,
@AsOfDate datetime,
@SICCode nvarchar(max),
@Coverage varchar(100)

AS
BEGIN
SET NOCOUNT ON;
    CREATE TABLE #PolicyNumbers  (PolicyNumber varchar(50))
    INSERT INTO #PolicyNumbers SELECT  PolicyNumber FROM PlazaInsuranceWPDataSet WHERE SICCode IN (SELECT * FROM [dbo].[StringOfStringsToTable](@SICCode,','))-- ('0161','0173','0179','0181','0191','0722','0752','0761','0782','1440','1441','1442','1521','1522','161','1611','1629','1711','172','1731','1742') 
    CREATE CLUSTERED INDEX IDX_C_PolicyNumbers_PolicyNumber ON #PolicyNumbers(PolicyNumber) 
    ; WITH Earned_to_date AS (
       SELECT Cast(@AsOfDate AS DATE) AS Earned_to_date
    ), policy_data AS (
        SELECT
            PolicyNumber,
            Coverage
    ,       Cast(PolicyEffectiveDate AS DATE) AS PolicyEffectiveDate
    ,       Cast(PolicyExpirationDate AS DATE) AS PolicyExpirationDate
    ,       WrittenPremium
    --,     --State
            FROM PlazaInsuranceWPDataSet piwp
            WHERE   EXISTS (SELECT PolicyNumber FROM #PolicyNumbers pn WHERE pn.PolicyNumber = piwp.PolicyNumber) 
                    AND Coverage IN (SELECT * FROM [dbo].[StringOfStringsToTable](@Coverage,','))
                    AND State IN (SELECT * FROM [dbo].[StringOfStringsToTable](@State,','))     
    )...

And this is the data I am working with:

enter image description here

Upvotes: 0

Views: 152

Answers (2)

Serdia
Serdia

Reputation: 4428

Instead of 

WHERE   EXISTS (SELECT PolicyNumber FROM #PolicyNumbers pn WHERE pn.PolicyNumber = piwp.PolicyNumber) 
                    AND Coverage IN (SELECT * FROM [dbo].[StringOfStringsToTable](@Coverage,','))
                    AND State IN (SELECT * FROM [dbo].[StringOfStringsToTable](@State,','))

I inserted all policy numbers filtered by all parameters in a #PolicyNumbers then used 

WHERE   EXISTS (SELECT PolicyNumber FROM #PolicyNumbers pn WHERE pn.PolicyNumber = piwp.PolicyNumber)

The result displays in ~1sec

Upvotes: 0

Deadsheep39
Deadsheep39

Reputation: 611

It's because you are working on string. Without indexes. With complicate logic.

You should organize your columns (make database normalization). You indexes.

You should use set theory instead of working with composed strings.

If you don't want to much change your qry, so change execution plan with rewriting to derivated qry.

select * 
from (select ...) t 
where Coverage (SELECT KeyCol FROM [dbo].[StringOfStringsToTable](@Coverage,','))

Pleas, try avoid use of *.

Upvotes: 1

Related Questions