SQL Server Query Optimizing (function)

Question

I have added the non- clustered indexes to the query. is there any way i can reduce the execution time by doing any changes to the following Query? (SQL Funtion)

Alter function [dbo].[FnDashSyncSummaryCountnew](@date datetime)  
  
returns @table table  
(  
ExecAccId int,  
AreaSalesManagerAccId int,  
SellingType varchar(20),  
SalesRepCount int,  
Initial int,  
Miday int ,  
DayEnd int,  
InvoiceCount int,  
InvoicedSR int ,  
OrderPlacedSR int,  
ScheduleCall int,  
ProductiveCall int,  
UnProductive int,  
OrderCount int  
)  
  
as  
begin  
  
  
declare @Productive table  
(  
SalesRepAccId int,  
CustomerId int,  
SellingType bit  
)  
  
insert into @Productive  
select * from  
(  
select SalesRepAccId, CustomerId, PreSellingRoute  
from InvoiceHeader ih inner join SalesRepAcc s on s.Id=ih.SalesRepAccId  
where convert(varchar,InvoiceDate,23)=convert(varchar,@date,23) and ih.Deleted=0 and s.PreSellingRoute=0  
union  
select SalesRepAccId, CustomerId, PreSellingRoute  
from OrderRequestHeader oh inner join SalesRepAcc s on s.Id=oh.SalesRepAccId  
where convert(varchar,TransactionDate,23)=convert(varchar,@date,23) and oh.Deleted=0 and s.PreSellingRoute=1  
)s  
  
  
declare @SyncDetails table  
(  
ExecAccId int,  
AreaSalesManagerAccId int,  
SalesRepAccId int,  
SalesRepName varchar(50),  
Initial varchar(50),  
Miday varchar(50),  
DayEnd varchar(50),  
PreSellingRoute bit,  
Battery int,  
StartBatteryLevel int  
)  
  
insert into @SyncDetails  
select * from FnDashSyncSummary(@date)  
  
  
insert into @table  
select ExecAccId, AreaSalesManagerAccId, SellingType, sum(SalesRepCount)SalesRepCount,  
sum(Initial)Initial, sum(Miday)Miday, sum(DayEnd)DayEnd, sum(InvoiceCount)InvoiceCount,  
sum(Invoiced)Invoiced, sum(OrderPlaced)OrderPlaced, sum(ScheduleCall)ScheduleCall,  
sum(ProductiveCall)ProductiveCall, sum(UnProductive)UnProductive, sum(OrderCount)OrderCount  
from  
( --t2  
select (select top 1 ExecAccId from Route r where r.SalesRepAccId=t1.SalesRepAccId and ExecAccId is not null)ExecAccId,  
(select top 1 AreaSalesManagerAccId from Route r where r.SalesRepAccId=t1.SalesRepAccId)AreaSalesManagerAccId,  
case when SellingType=1 then 'Pre Selling' else 'Direct Seliing' end as SellingType,  
sum(SalesRepCount)SalesRepCount, sum(Initial)Initial, sum(Miday)Miday, sum(DayEnd)DayEnd, sum(InvoiceCount)InvoiceCount,  
sum(Invoiced)Invoiced, sum(OrderPlaced)OrderPlaced, sum(ScheduleCall)ScheduleCall,  
sum(ProductiveCall)ProductiveCall, sum(UnProductive)UnProductive, sum(OrderCount)OrderCount  
from  
( --t1  
select SalesRepAccId, SellingType, count(distinct CustomerId)ProductiveCall, 0'UnProductive',  
0'ScheduleCall', 0'Invoiced', 0'InvoiceCount', 0'OrderPlaced', 0'OrderCount', 0'SalesRepCount', 0'Initial', 0'Miday', 0'DayEnd'  
from @Productive  
group by SalesRepAccId, SellingType  
  
union  
  
select SalesRepAccId, PreSellingRoute, 0, count(distinct CustomerId)UnProductive,  
0, 0, 0, 0, 0, 0, 0, 0, 0  
from VisitDetail vd inner join SalesRepAcc s on vd.SalesRepAccId=s.Id  
where convert(varchar,VisitDate,23)=convert(varchar,@date,23) and CustomerId not in (select distinct CustomerId from @Productive)  
group by SalesRepAccId, PreSellingRoute  
  
union  
  
select r.SalesRepAccId, s.PreSellingRoute, 0, 0, count(c.Id)ScheduleCall, 0, 0, 0, 0, 0, 0, 0, 0  
from (select Id, RouteId from Customer where Active=1)c inner join (select distinct v.RouteId from VisitHeader v  
where convert(varchar,VisitDate,23)=convert(varchar,@date,23) and v.RouteId is not null)v on v.RouteId=c.RouteId  
inner join Route r on c.RouteId=r.Id  
inner join SalesRepAcc s on s.id=r.SalesRepAccId  
group by r.SalesRepAccId , s.PreSellingRoute  
  
union  
  
select SalesRepAccId, PreSellingRoute, 0, 0, 0, 1'Invoiced',  
count(ih.InvoiceNo)InvoiceCount, 0, 0, 0, 0, 0, 0  
from InvoiceHeader ih inner join SalesRepAcc s on ih.SalesRepAccId=s.id  
where convert(varchar,InvoiceDate,23)=convert(varchar,@date,23) and ih.PreOrder=0 and ih.Deleted=0  
group by SalesRepAccId, PreSellingRoute  
  
union  
  
select SalesRepAccId, PreSellingRoute, 0, 0, 0, 0, 0, 1'OrderPlaced',  
count(oh.OrderRequestId)OrderCount, 0, 0, 0, 0  
from OrderRequestHeader oh inner join SalesRepAcc s on oh.SalesRepAccId=s.id  
where convert(varchar,TransactionDate,23)=convert(varchar,@date,23) and oh.Deleted=0  
group by SalesRepAccId, PreSellingRoute  
  
union  
  
select SalesRepAccId, PreSellingRoute, 0, 0, 0, 0, 0, 0, 0, sum(SalesRepCount)SalesRepCount,  
sum(Initial)Initial, sum(Miday)Miday, sum(DayEnd)DayEnd  
from  
(  
select SalesRepAccId, PreSellingRoute, count(distinct SalesRepAccId)SalesRepCount,  
0'Initial', 0'Miday', 0'DayEnd'  
from @SyncDetails  
group by SalesRepAccId, PreSellingRoute  
  
union  
  
select SalesRepAccId, PreSellingRoute, 0, count(distinct SalesRepAccId)Initial, 0, 0  
from @SyncDetails  
where Initial<>''  
group by SalesRepAccId, PreSellingRoute  
  
union  
  
select SalesRepAccId, PreSellingRoute, 0, 0, count(distinct SalesRepAccId)Miday, 0  
from @SyncDetails  
where Miday<>''  
group by SalesRepAccId, PreSellingRoute  
  
union  
  
select SalesRepAccId, PreSellingRoute, 0, 0, 0, count(distinct SalesRepAccId)DayEnd  
from @SyncDetails  
where DayEnd<>''  
group by SalesRepAccId, PreSellingRoute  
)t  
group by SalesRepAccId, PreSellingRoute  
)t1  
group by SalesRepAccId, SellingType  
)t2  
group by ExecAccId, AreaSalesManagerAccId, SellingType  
order by AreaSalesManagerAccId  
  
return  
  
end  
  
  
  
  
GO  

Answer 1

You have a few of these:

convert(varchar,TransactionDate,23)=convert(varchar,@apl ,23)

If you have created an index on TransactionDate that is not going to help, because the index is on date or datetime values (or whatever data type have) and then you convert to strings which sorts differently, and therefore the index cannot be used.

If you instead write this as:

 where  cast(TransactionDate AS date) =  cast(@date as date)  

It is possible for SQL Server to use the index on TransactionDate and VisitorDate.

Answer 2

Hi @Melisaa
Here are some tips might be useful:

1. Replace a complex statement with multiple simple statements.
2. Make sure all mentioned columns are indexed. Refer to the optimization points mentioned in Erland's answer.
3. Find out any possible to change from hash join to EXISTS.
4. Update statistics.
5. Check whether it is possible to remove the sort(group by/order by/Top).

Best regards,
LiHong

Answer 3

You can rewrite the above as

   select SalesRepAccId, PreSellingRoute,   
                count(distinct SalesRepAccId)SalesRepCount) AS totcount  
                count(distinct case when Initial <> '' then SalesRepAccId)SalesRepCount end) AS Initial,  
                count(distinct case when Miday <> '' then SalesRepAccId)SalesRepCount end) AS MiDay,  
                count(distinct case when DayEnd <> '' then SalesRepAccId)SalesRepCount end) AS DayEnd,  
         from @SyncDetails  
         group by SalesRepAccId, PreSellingRoute  

Well, you need to keep the last few lines that are part of the main query.