SQL Performance with Distinct and Count

Question

I have a stored procedure which contain so many SET statements. That is taking so long to execute. What I can do for increase the performance. One statement I have included here.

SET @VisitedOutlets=(select count (distinct CustomerId) from dbo.VisitDetail  
                        where RouteId = @intRouteID   
                        and CONVERT(VARCHAR(10),VisitDate,111) between CONVERT(VARCHAR(10),@FromDate,111)  
                        and CONVERT(VARCHAR(10),@ToDate,111));

@Tom Then remove the `convert` function from where clause. Also create index on `Routeid` and `visitdate` — Pரதீப், Jul 28 '15 at 09:25
Do you have any index on CustomerID? Adding a NC index on RouteID including customerId will increase the performance for this query. — Abhay Chauhan, Jul 28 '15 at 09:28
If I remove 'convert ' data no returning properly. in DB value sore in like '2015-07-11 00:00:00.000'. But from program date passing with time. — Tom, Jul 28 '15 at 09:30
@Tom - Then use this `VisitDate BETWEEN CONVERT(date, @FromDate) AND CONVERT(date, @ToDate))` — Pரதீப், Jul 28 '15 at 09:33
Im using SQL 2005. So cant use that. Error : Type date is not a defined system type. — Tom, Jul 28 '15 at 09:38
Try `CONVERT(datetime, convert(VARCHAR(10), VisitDate, 111))` Dates will be quicker to sort than strings — Matt Allwood, Jul 28 '15 at 11:00
Also, as you state, that's just one statement. Are you sure that is the slow statement? You'll need to do more digging to find out which of your 'many SET statements' are the slow ones and can optimise from there. Split up your SP as far as possible and profile the individual calls — Matt Allwood, Jul 28 '15 at 11:02

score 0 · Answer 1 · edited May 23 '17 at 12:30

I think your problem comes from the fact that you are using variables in your query. Normally, the optimizer will ... optimize (!) the query for a given (hard coded) value (let's say id = 123) for instance, whereas it cannot optimize since it is a variable.

Let's take a great example from here :

OK,

You are the Optimizer and the Query Plan is a vehicle.

I will give you a query and you have to choose the vehicle.

All the books in the library have a sequential number

My query is Go to the library and get me all the books between 3 and 5

You'd pick a bike right, quick, cheap, efficient and big enough to carry back 3 books.

New query.

Go to the library and get all the books between @x and @y.

Pick the vehicle.

Go ahead.

That's what happens. Do you pick a dump truck in case I ask for books between 1 and Maxvalue? That's overkill if x=3 and y=5. SQL has to pick the plan before it sees the numbers.

So your problem is that the optimizer cannot do its job correctly. To allow him doing his job, you can make him recompile, or update statistics. See here, here, and here.

So my 2 solutions to your problem would be :

Recompile : OPTION(RECOMPILE)
Update statistics : EXEC sp_updatestats

score 0 · Answer 2 · edited Jul 29 '20 at 18:42

Your query is essentially:

select @VisitedOutlets= count(distinct CustomerId)
from dbo.VisitDetail  
where RouteId = @intRouteID and 
      CONVERT(VARCHAR(10), VisitDate, 111) between
          CONVERT(VARCHAR(10), @FromDate, 111) and CONVERT(VARCHAR(10), @ToDate, 111);

I think this query can be optimized to take advantage of indexes. One major problem is the date comparison. You should not be doing any conversion for the comparison on VisitDate. So, I would rewrite the query as:

select @VisitedOutlets= count(distinct CustomerId)
from dbo.VisitDetail vd
where vd.RouteId = @intRouteID and 
      vd.VisitDate >= cast(@FromDate as date) and
      vd.VisitDate < dateadd(day, 1, cast(@ToDate as date))

For this query, you want an index on VisitDetail(RouteId, VisitDate, CustomerId). I would also store the constants in the appropriate format, so conversions are not needed in the query itself.

between is dangerous when using dates. Here is an interesting discussion on the topic by Aaron Bertrand.

SQL Performance with Distinct and Count

2 Answers2