How to improve speed of the query using SQL Server?

Question

I have created a stored procedure to get data. In this stored procedure, I have returned 1 table and table stores the data above 1 lakh + data. So right now I have run the stored procedure that time I get the data in above 1 minute time take. I want just with in 1 second get data. I have set also SET NOCOUNT ON; and also create missing index. Still I am getting same time for the get data.

This is my query:

DECLARE @CurMon int
DECLARE @year nvarchar(max)
SELECT  @CurMon = month(getdate())
SELECT  @year = year(getdate())     

SELECT 
    FORMAT(dateadd(MM, T.i, getdate()),'MMM-yy') AS DateColumn, 
    ISNULL(uf.TotalCount, 0) as TotalCount
FROM 
    (VALUES (12-@CurMon),(11-@CurMon),(10-@CurMon),(9-@CurMon),(8-@CurMon),(7-@CurMon),(6-@CurMon), (5-@CurMon), (4-@CurMon), (3-@CurMon), (2-@CurMon), (1-@CurMon)) AS T(i)
OUTER APPLY
    (SELECT DISTINCT
         COUNT(datepart(MM,UF.InsertDateTime)) OVER (partition by datepart(MM,UF.InsertDateTime)) AS TotalCount
               FROM dbo.UserFollowers UF
               INNER JOIN dbo.Users U on U.UserId = UF.FollowerId
               WHERE DATEDIFF(mm,UF.InsertDateTime, DATEADD(mm, T.i, GETDATE())) = 0 and UF.IsFollowed = 1 
            ) uf
            order by DATEPART(MM,convert(datetime,FORMAT(dateadd(MM, T.i, getdate()),'MMMM') +'01 '+@year,110))

i am also try some other query for the improve speed of query but still i am getting same time. here this query also print.

declare @StartDate datetime = dateadd(year , datediff(year , 0, getdate() )  , 0)
declare @tempT2 table 
    (
        MNo int,
        [Month] datetime,
        NextMonth datetime)

 ;with Months as (
select top (12) 
 MNo = row_number() over (order by number)
 ,[Month] = dateadd(month, row_number() over (order by number) -1, @StartDate)
, NextMonth = dateadd(month, row_number() over (order by number), @StartDate)
 from master.dbo.spt_values
)

insert into @tempT2
select * from Months

select 
m.MNo
 , Month = format(m.Month, 'MMM-yy')
  , tally = count(UF.InsertDateTime)
   from @tempT2 m
 left join dbo.UserFollowers UF
 INNER JOIN dbo.Users U on U.UserId = UF.FollowerId
on UF.InsertDateTime >= m.Month
  and UF.InsertDateTime < m.NextMonth where  UF.IsFollowed = 1 
group by  m.MNo,format(m.Month, 'MMM-yy')
order by MNo

here this is my both query i have try but still i am not getting success for the improve the speed of the query. and sorry but i can not see here my execution plan of the query actually i have not permission for that.

Please use **internationally understandable** units of measure : thousands, millions, billions ... — marc_s, Aug 29 '17 at 07:28
questions seeking performance help should include DDL,DML Of the tables involved along with test data..if your test data is large,try scripting out schema and stats for the table(`right click database->generate scripts->select specific database objects->in next screen select advanced and choose Script statistics)` and paste it in question..With this info any one repro the same issue you are facing.Otherwise it becomes very difficult to answer your question .Pasting server version also helps — TheGameiswar, Aug 29 '17 at 08:16
@TheGameiswar but i can't permission for that generate script and execution plan show here. you want some data sample and my o/p want so here i can do that? just this query take more and more time for the getting data on server. — Edit, Aug 29 '17 at 08:26
@TheGameiswar i have also try remove order by from the query and check but still take same time. also remove case from the select. — Edit, Aug 29 '17 at 08:27
@Edit -1 since you didn't either read marcs comment, or just didn't care — James Z, Aug 29 '17 at 16:11

score 1 · Accepted Answer · answered Aug 29 '17 at 12:52

You can gain a little bit of performance by switching to a temporary table instead of a table variable, and by getting rid of format():

declare @StartDate datetime = dateadd(year , datediff(year , 0, getdate() )  , 0)
create table #Months (
    MNo int not null primary key
  , Month char(6) not null
  , MonthStart datetime not null
  , NextMonth datetime not null
)
;with Months as (
select top (12) 
    MNo = row_number() over (order by number)
  , MonthStart = dateadd(month, row_number() over (order by number) -1, @StartDate)
  , NextMonth  = dateadd(month, row_number() over (order by number), @StartDate)
 from master.dbo.spt_values
)
insert into #Months (MNo, Month, MonthStart, NextMonth)
select
    MNo
  , Month = stuff(convert(varchar(9),MonthStart,6),1,3,'') 
  , MonthStart
  , NextMonth
from Months;

select 
   m.MNo
 , m.Month
 , tally = count(UF.InsertDateTime)
from @tempT2 m
  inner join dbo.Users U 
    on UF.InsertDateTime >= m.MonthStart
   and UF.InsertDateTime < m.NextMonth
  inner join dbo.UserFollowers UF
    on U.UserId = UF.FollowerId 
   and UF.IsFollowed = 1 
group by  
    m.MNo
  , m.Month
order by MNo

After that, you should evaluate the execution plan to determine if you need a better indexing strategy.

If you still need it to go faster, you could create an actual calendar table and look into creating an indexed view. An indexed view can be a chore get it to behave correctly depending on your sql server version, but will be faster.

Reference:

thanks you so much for the helping and that's amazing solution. — Edit, Aug 30 '17 at 06:08

How to improve speed of the query using SQL Server?

1 Answers1