Slow Stored Procedure In ASP.NET But Fast In SSMS

Question

I have stored procedure with runs fast in SQL Management Studio but it is 10 times slower in ASP.NET application. What is the reason of this strange behaviour and idea to fix it ?

This is my Stored Procedure:

Create procedure [dbo].[CRFarmersGet]
  @Company        NVARCHAR(200) = NULL,
  @FName        NVARCHAR(50) = NULL,
  @SName        NVARCHAR(50) = NULL,
  @LName        NVARCHAR(50) = NULL,
  @PID            NVARCHAR(50) = NULL,
  @RegProvince    NVARCHAR(50) = NULL,
  @RegCity        NVARCHAR(50) = NULL,
  @ActCity  NVARCHAR(50) = NULL,
  @ActCommune  NVARCHAR(50) = NULL,
  @ActProvince  NVARCHAR(50) = NULL,
  @SCATTUAct NVARCHAR(50) = NULL,
  @Culture NVARCHAR(50) = NULL,
  @CultureCode NVARCHAR(50) = NULL,
  @Contracts SMALLINT = 0
WITH ENCRYPTION
As
BEGIN
    SET @Company = NULLIF(RTRIM(LTRIM(@Company)), '')
    SET @FName = NULLIF(RTRIM(LTRIM(@FName)), '')
    SET @SName = NULLIF(RTRIM(LTRIM(@SName)), '')
    SET @LName = NULLIF(RTRIM(LTRIM(@LName)), '')
    SET @PID = NULLIF(RTRIM(LTRIM(@PID)), '')
    SET @RegProvince = NULLIF(RTRIM(LTRIM(@RegProvince)), '')
    SET @RegCity = NULLIF(RTRIM(LTRIM(@RegCity)), '')
    SET @ActCity = NULLIF(RTRIM(LTRIM(@ActCity)), '')
    SET @ActCommune = NULLIF(RTRIM(LTRIM(@ActCommune)), '')
    SET @ActProvince = NULLIF(RTRIM(LTRIM(@ActProvince)), '')
    SET @SCATTUAct = NULLIF(RTRIM(LTRIM(@SCATTUAct)), '')
    SET @Culture = NULLIF(RTRIM(LTRIM(@Culture)), '')
    SET @CultureCode = NULLIF(RTRIM(LTRIM(@CultureCode)), '')


      select 
             f.[ID]
            ,f.[PID]
            ,f.[PIDType]
            ,f.[DerecognitionDate]
            ,f.[Company]
            ,f.[FName]
            ,f.[SName]
            ,f.[LName]
            ,l.ProvinceID as RegProvinceID
            ,l.Province as RegProvince
            ,l.CommuneID as RegCommuneID
            ,l.Commune as RegCommune
            ,f.[RegCityID]
            ,l.City as RegCity
            ,f.[AddrReg]
            ,f.[Sync]
            ,f.[D_Modify]
            ,f.[U_Modify]
            ,f.[Version]
      from CRFarmersT f
      join (select CityID, City, c.CommuneID, Commune, m.ProvinceID, Province from CitiesT c join MunicipalitiesT m on c.CommuneID = m.CommuneID join ProvincesT pr on m.ProvinceID = pr.ProvinceID) l on l.CityID = f.RegCityID
      LEFT JOIN dbo.CRFarmersDetailsT AS cfdt ON cfdt.FarmerID = f.ID
      LEFT JOIN (select CityID, City, c.CommuneID, Commune, m.ProvinceID, Province from CitiesT c join MunicipalitiesT m on c.CommuneID = m.CommuneID join ProvincesT pr on m.ProvinceID = pr.ProvinceID) fdl on fdl.CityID = cfdt.ActCityID
      LEFT JOIN dbo.CRCultures AS cc ON cc.ID = cfdt.CultureID
      LEFT JOIN dbo.CRContracts AS cc2 ON cc2.FarmerDetailID = cfdt.ID
     where 
        (f.Company like + '%' + rtrim(ltrim(@Company)) + '%' OR @Company IS NULL)
        AND (f.fName like + '%' + rtrim(ltrim(@FName)) + '%' OR @FName IS NULL)
        AND (f.SName like + '%' + rtrim(ltrim(@SName)) + '%' OR @SName IS NULL)
        AND (f.LName like + '%' + rtrim(ltrim(@LName)) + '%' OR @LName IS NULL)
        AND (f.PID like + '%' + rtrim(ltrim(@PID)) + '%' OR @PID IS NULL)
        AND (l.ProvinceID like + '%' + rtrim(ltrim(@RegProvince)) + '%' OR @RegProvince IS NULL)
        AND (l.City like + '%' + rtrim(ltrim(@RegCity)) + '%' OR @RegCity IS NULL)
        AND (fdl.City like + '%' + rtrim(ltrim(@ActCity)) + '%' OR @ActCity IS NULL)
        AND (fdl.Commune like + '%' + rtrim(ltrim(@ActCommune)) + '%' OR @ActCommune IS NULL)
        AND (fdl.Province like + '%' + rtrim(ltrim(@ActProvince)) + '%' OR @ActProvince IS NULL)
        AND (cfdt.[SCATTU-Activity] like + '%' + rtrim(ltrim(@SCATTUAct)) + '%' OR @SCATTUAct IS NULL)
        AND (cc.Name like + '%' + rtrim(ltrim(@Culture)) + '%' OR @Culture IS NULL)
        AND (cc.Code like + '%' + rtrim(ltrim(@CultureCode)) + '%' OR @CultureCode IS NULL)
        AND (@Contracts = 1 AND cc2.ID IS NOT NULL OR @Contracts = 0)
        AND exists(select 1 from dbo.UserRights('00000000-0000-0000-0000-000000000000') where RightID in (600, 601, 602, 612, 613))
        GROUP BY f.[ID]
            ,f.[PID]
            ,f.[PIDType]
            ,f.[DerecognitionDate]
            ,f.[Company]
            ,f.[FName]
            ,f.[SName]
            ,f.[LName]
            ,l.ProvinceID 
            ,l.Province 
            ,l.CommuneID 
            ,l.Commune 
            ,f.[RegCityID]
            ,l.City
            ,f.[AddrReg]
            ,f.[Sync]
            ,f.[D_Modify]
            ,f.[U_Modify]
            ,f.[Version]
      Order By PID
END
Go

Could be one of two reasons: Reason A: You're trying to implement optional parameters which almost always introduces parameter sniffing. Do some research on "parameter sniffing with optional parameters". Reason B: Your web code isn't doing what you think - use SQL Profiler to capture exactly what it is doing on the database — Nick.Mc, Jul 29 '15 at 05:55
There is an awful lot of string matching going on there. I would also look at putting you null checks before the string match as I believe that in SQL that OR is a short cut condition; i.e. if the first condition is true it will stop evaluating. E.g. try: cc.`@Culture IS NULL OR Name like + '%' + rtrim(ltrim(@Culture)) + '%' ` etc — Jon P, Jul 29 '15 at 06:07
Note my above comment is a general comment, not an attempt to explain what is happening in .net vs native execution. — Jon P, Jul 29 '15 at 06:08
Also what makes you think it is the stored proc that is running longer and not something else you are doing with the result set once it hits .net? — Jon P, Jul 29 '15 at 06:09

score 0 · Answer 1 · edited May 23 '17 at 11:58

Probably SSMS and ASP.NET are using a different execution plan. One thing to try is to add a With Recompile as in:

Create procedure [dbo].[CRFarmersGet]
  @Company        NVARCHAR(200) = NULL,
  @FName        NVARCHAR(50) = NULL,
  @SName        NVARCHAR(50) = NULL,
  @LName        NVARCHAR(50) = NULL,
  @PID            NVARCHAR(50) = NULL,
  @RegProvince    NVARCHAR(50) = NULL,
  @RegCity        NVARCHAR(50) = NULL,
  @ActCity  NVARCHAR(50) = NULL,
  @ActCommune  NVARCHAR(50) = NULL,
  @ActProvince  NVARCHAR(50) = NULL,
  @SCATTUAct NVARCHAR(50) = NULL,
  @Culture NVARCHAR(50) = NULL,
  @CultureCode NVARCHAR(50) = NULL,
  @Contracts SMALLINT = 0
WITH ENCRYPTION, RECOMPILE

(I am not sure what the syntax is for having both Encryption and Recompile)

Here is a related question and an interesting read

Slow Stored Procedure In ASP.NET But Fast In SSMS

1 Answers1