Sql query performance problem!

Question

Hi
i have a query as follow :
declare @minDate char(10), @maxDate char(10)
set @minDate = '1399/11/01'
set @maxDate = '1399/11/30';
with cte1
as (
SELECT DISTINCT z.ZoneName, mc.MedicalCategorizationName
, CASE r.BasicInsuranceID WHEN 0 THEN N'آزاد' ELSE N'بیمه ای' END AS InsType
, CASE ms.ServiceTypeID WHEN 1 THEN N'ويزيت' ELSE N'خدمت' END AS ServiceType
, SUM(rs.Quantity) AS SumQty
, SUM(c.Hesab) AS SumHesab
,sum (r.TotalPrice) as SumTotalPrice
FROM dbo.Reception r
JOIN dbo.ReceptionServices rs ON r.ReceptionRowID = rs.ReceptionRowID
JOIN dbo.MedicalServices ms ON rs.ServiceID = ms.ServiceID
JOIN dbo.MedicalCategories mc ON ms.MedicalCategorizationID = mc.MedicalCategorizationID
JOIN dbo.Zones z ON r.ZoneID = z.ZoneID
JOIN dbo.Cash c ON r.ReceptionRowID = c.OwnerID
WHERE r.ReceptionDate BETWEEN @minDate AND @maxDate
AND NOT EXISTS (SELECT c2.ReceptionRowID FROM Cash c2 WHERE c2.IsEbtal=1 AND c2.ReceptionRowID = r.ReceptionRowID)
GROUP BY ms.ServiceID, z.ZoneName, mc.MedicalCategorizationName, r.BasicInsuranceID ,ms.ServiceTypeID
)
select c.ZoneName as N' بخش'
, c.ServiceType as N'سرويس' ,c.InsType as N'بيمه' ,sum (c.SumQty) as N'تعداد', sum (c.SumTotalPrice) as N'جمع كل' ,sum(c.SumHesab) as N'جمع دريافتي'
from cte1 c
group by c.ZoneName, c.ServiceType,c.InsType
order by c.ZoneName

This query takes almost 1 second to run. My problem is that when i create a procedure from above query, It takes a long time to execute (more than 1 minute).
What's the problem & how to solve that?
Thanks in advance

Answer 1

Hi @Hamed Vaziri ,

Welcome to Microsoft Q&A!

As mentioned by other experts, the most common problem for such behavior will be Parameters Sniffing problem.

When a stored procedure is first executed SQL Server looks at the input parameters and uses this as guidance to build the query plan. This is known as “parameter sniffing”.

This is good as long as the input parameters for the first invocation are typical for future invocations. But if that is not the case this will cause performance problems.

Your procedure may be like below:

CREATE PROCEDURE Myprocedure(@minDate char(10), @maxDate char(10))  
AS  
with cte1  
as (  
...  
WHERE r.ReceptionDate BETWEEN @minDate AND @maxDate  
...  
)  
select c.ZoneName as N' بخش'  
, c.ServiceType as N'سرويس' ,c.InsType as N'بيمه' ,sum (c.SumQty) as N'تعداد', sum (c.SumTotalPrice) as N'جمع كل' ,sum(c.SumHesab) as N'جمع دريافتي'  
from cte1 c  
group by c.ZoneName, c.ServiceType,c.InsType  
order by c.ZoneName  

You could try below several solutions and check whether any of them is helpful to you.

One: Replace parameters with local variables.

CREATE PROCEDURE Myprocedure(@minDate char(10), @maxDate char(10))  
AS  
declare @local_minDate char(10), @local_maxDate char(10)  
set @local_minDate = @minDate  
set @local_maxDate = @maxDate;  
  
with cte1  
as (  
...  
WHERE r.ReceptionDate BETWEEN @local_minDate AND @local_maxDate  
...  
)  
select c.ZoneName as N' بخش'  
, c.ServiceType as N'سرويس' ,c.InsType as N'بيمه' ,sum (c.SumQty) as N'تعداد', sum (c.SumTotalPrice) as N'جمع كل' ,sum(c.SumHesab) as N'جمع دريافتي'  
from cte1 c  
group by c.ZoneName, c.ServiceType,c.InsType  
order by c.ZoneName  

Two: Execute using WITH RECOMPILE.

EXEC Myprocedure @minDate = '1399/11/01',@maxDate = '1399/11/30' WITH RECOMPILE;  

Three: Query hint RECOMPILE

CREATE PROCEDURE Myprocedure(@minDate char(10), @maxDate char(10))  
AS  
with cte1  
as (  
...  
WHERE r.ReceptionDate BETWEEN @minDate AND @maxDate  
...  
OPTION (RECOMPILE);  
)  
select c.ZoneName as N' بخش'  
, c.ServiceType as N'سرويس' ,c.InsType as N'بيمه' ,sum (c.SumQty) as N'تعداد', sum (c.SumTotalPrice) as N'جمع كل' ,sum(c.SumHesab) as N'جمع دريافتي'  
from cte1 c  
group by c.ZoneName, c.ServiceType,c.InsType  
order by c.ZoneName  

You could also refer this article for more details and solutions.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

1) You have both DISTINCT and GROUP BY on the query - you don't need both as that just adds an additional sort which isn't needed.

Good catch!

But this is not only a performance issue. Potentially, the DISTINCT could eliminate rows where the three sums are all equal (and non-zero), which means that the final query will produce an incorrect result. (Unless there is something very special going on.)

Here is a cleaned-up version of the query, which only does aggregation in the final query, and the the CTE serves produce column aliases for the CASE expressions:

WITH cte1 AS (
   SELECT z.ZoneName,  
          CASE r.BasicInsuranceID WHEN 0 THEN N'آزاد' ELSE N'بیمه ای' END AS InsType,
          CASE ms.ServiceTypeID WHEN 1 THEN N'ويزيت' ELSE N'خدمت' END AS ServiceType
          rs.Quantity, c.Hesab, r.TotalPrice
   FROM   dbo.Reception r
   JOIN   dbo.ReceptionServices rs ON r.ReceptionRowID = rs.ReceptionRowID
   JOIN   dbo.MedicalServices ms ON rs.ServiceID = ms.ServiceID
   -- JOIN   dbo.MedicalCategories mc ON ms.MedicalCategorizationID = mc.MedicalCategorizationID
   JOIN   dbo.Zones z ON r.ZoneID = z.ZoneID
   JOIN   dbo.Cash c ON r.ReceptionRowID = c.OwnerID
   WHERE  r.ReceptionDate BETWEEN @minDate AND @maxDate
     AND  NOT EXISTS (SELECT c2.ReceptionRowID 
                      FROM   Cash c2 
                      WHERE  c2.IsEbtal=1 
                        AND  c2.ReceptionRowID = r.ReceptionRowID)
)
select c.ZoneName as N' بخش', c.ServiceType as N'سرويس', c.InsType as N'بيمه',
       sum (c.SumQty) as N'تعداد', sum (c.SumTotalPrice) as N'جمع كل', sum(c.SumHesab) as N'جمع دريافتي'
from cte1 c
group by c.ZoneName, c.ServiceType,c.InsType
order by c.ZoneName

I commented out one table which does not seemed to be used elsewhere in the query, but maybe that join serves as an existence filter.

I will have to admit that I am not sure that the even the modified query is correct. But I don't know how these tables relate to each other more exactly and with which cardinality.

And this may be worth sorting out. After all, it is utterly interesting to tune an incorrect query.

Answer 3

A couple of points to consider:

1) You have both DISTINCT and GROUP BY on the query - you don't need both as that just adds an additional sort which isn't needed.
2) You have specified columns in the GROUP BY that are not part of the SELECT list (ms.ServiceID). This can cause what appear to be duplicate rows - but in fact are not actual duplicates.
3) The outer query includes an additional GROUP BY statement - which probably isn't needed to get the totals. If you use the correct grouping in the first query - that is, remove ms.ServiceID and mc.MedicalCategorizationName from the GROUP BY and remove mc.MedicalCategorizationName from the SELECT - you should get the same results.

Another point to consider:

Instead of storing the Islamic date (I am assuming the date is from the type IIc administrative calendar) - you can store the JDN (Julian Day Number). The JDN would be an integer and allows for some date math - as well as the ability to easily convert between different calendars (Islamic, Julian, Gregorian).

Answer 4

We don't know how your procedure looks like, but I assume that in your procedure, @minDate and @maxDate are parameters and that changes things. Wen they are local variables, SQL Server has no knowledge about the values when it compiles the batch, but it makes a standard assumption about the hit rate. On the other hand, when they are parameters, SQL Server sniffs the parameter values, and uses these as guidance when compiling the plan. This is known as parameter sniffing. The cached plan is then reused.

It could be that the procedure was initially compiled for different parameter values, so that plan is not good for this interval. It could be just a case of a bad luck. That is, the blind assumption was better than the plan you got when the parameter values were sniffed.

I have an article Slow in the application., Fast in SSMS? where I discuss parameter sniffing in more detail and how you can troubleshoot it.