CODEWITHSUNDEEP
sql-server
Edit
Edit

Reputation: 393

How to improve speed of the query using SQL Server?

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.

Upvotes: 0

Views: 125

Answers (1)

SqlZim
SqlZim

Reputation: 38023

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:

Upvotes: 1

Related Questions