CODEWITHSUNDEEP
sqlsql-server
Rish
Rish

Reputation: 457

Is there any alternative for this query which would take lesser processing time

Source Table

CREATE TABLE #TEMPTABLE
(
     requestTime datetime2,
     NoOfSessions integer

)

INSERT INTO #TEMPTABLE
select  convert(nvarchar(16), TimeLog, 120) requestTime , COUNT(DISTINCT SessionId)As NoOfSessions
from  LogData
group by convert(nvarchar(16), TimeLog, 120) order by requestTime;


select  s1.requestTime
,       (
        select  sum(NoOfSessions)
        from    #TEMPTABLE s2
        where   dateadd(minute, -15, s1.requestTime) < s2.requestTime
                and s2.requestTime <= s1.requestTime
        ) as TotalNumberOfSessions
from    #TEMPTABLE s1


DROP TABLE #TEMPTABLE

Upvotes: 2

Views: 110

Answers (4)

egrunin
egrunin

Reputation: 25083

I don't think the temp table is the problem, but here's your code without it:

select  s1.requestTime
,   (
    select  sum(NoOfSessions)
    from
    (
        select dateadd(minute, datediff(minute, 0, TimeLog), 0), 
            count(distinct SessionId)
        from  LogData
        group by dateadd(minute, datediff(minute, 0, TimeLog), 0)
    ) s2
    where   dateadd(minute, -15, s1.requestTime) < s2.requestTime
            and s2.requestTime <= s1.requestTime
    ) as TotalNumberOfSessions
from 
(
    select dateadd(minute, datediff(minute, 0, TimeLog), 0), 
        count(distinct SessionId)
    from  LogData
    group by dateadd(minute, datediff(minute, 0, TimeLog), 0)
) s1

Upvotes: 0

usr
usr

Reputation: 171246

The temp table is not the problem. The problem is the correlated subquery causing an insane number of loop-join iterations.

Try reformulating the subquery as a join. Round the datetime value to minutes and join on the minutes value.

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

Try this and let me know. It might be some improvement for you. I added a primary key on requestTime in the temp table that can be used in the correlated sub query.

create table #TEMPTABLE
(
  requestTime smalldatetime primary key,
  NoOfSessions integer
)

insert into #TEMPTABLE
select dateadd(minute, datediff(minute, 0, TimeLog), 0), 
       count(distinct SessionId)
from  LogData
group by dateadd(minute, datediff(minute, 0, TimeLog), 0)


select  s1.requestTime
,       (
        select  sum(NoOfSessions)
        from    #TEMPTABLE s2
        where   dateadd(minute, -15, s1.requestTime) < s2.requestTime
                and s2.requestTime <= s1.requestTime
        ) as TotalNumberOfSessions
from    #TEMPTABLE s1

drop table #TEMPTABLE

Upvotes: 1

TomTom
TomTom

Reputation: 62159

  • Drop the TempTable and do it all in ONE select. This totally means you can get the first resuults a LOT faster of possible - SQL Server can in your case not deliver ONE line before the second select happens.
  • DO ttry to avoid the Convert. Why do you convert in the first palce?
  • Check query plan. YOu tell us nothing about indices and why you think it is slow - more info here may help with the answer.

In general, the problem likely is this:

where dateadd(minute, -15, s1.requestTime) < s2.requestTime

I am not sure this can result in an efficient query plan - but then you do not show us the plan.

Upvotes: 0

Related Questions