CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012sqlperformance
Mando
Mando

Reputation: 11712

T-SQL Run multiple queries against a subquery

I have a simple data model for users and their requests:

User
- id,
- name

Request
- id,
- createdAt,
- completedAt,
- status
- userId (FK to user)

I'm trying to run a query which collects some stats for every user. The issue is that I have to run the same subquery to fetch user requests for every parameter I select. As instead, I want to run it once and then calculate some stats over it.

select
 u.id as UserId,
 (select count(*) 
  from Requests r 
  where userId = u.id 
      and timestamp > @dateFrom) as Total,
 (select count(*) 
  from Requests r 
  where userId = u.id 
      and timestamp > @dateFrom
      and status = N'Completed') as Completed,
 (select status
  from Requests r 
  where userId = u.id 
      and timestamp > @dateFrom
      and status != N'Completed') as ActiveStatus,
  (select datediff(second, createdAt, completedAt)
   from Requests r 
   where userId = u.id 
      and timestamp > @dateFrom
      and status == N'Completed') as AvgProcessingTime
from User u

Obviously, this query is very slow and I need to optimize it. I tried join, apply, rank, nothing worked out well for me (read as I wasn't able to complete the query for all required stats).

What is the best approach here from the performance stand point?

Upvotes: 0

Views: 97

Answers (2)

Maverick Sachin
Maverick Sachin

Reputation: 883

I'm not sure about this query cause I haven't run it on my machine, but you can give it a try and make some changes accordingly if needed --

SELECT U.ID AS USERID
,COUNT(R.ID) AS TOTAL
,SUM(CASE 
        WHEN R.[STATUS] = N'COMPLETED'
            THEN 1
        END) AS [COMPLETED]
,CASE 
    WHEN R.[STATUS] <> N'COMPLETED'
        THEN [STATUS]
    END AS [ACTIVE STATUS]
,CASE 
    WHEN R.[STATUS] = N'COMPLETED'
        THEN DATEDIFF(SECOND, CREATEDAT, COMPLETEDAT)
    END AS [AVG PROCESSING TIME]
FROM USERS U
LEFT JOIN REQUESTS R ON U.ID = R.USERID
WHERE TIMESTAMP > @DATEFROM

Upvotes: 1

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

try this using Left Join and aggregation

There could be a couple of issues here but let me know how you go.

select
 u.id as UserId
 ,count(r.UserId) [Total]
 ,sum(iif(r.status = N'Completed',1,0)) [Completed]
 ,sum(iif(r.status <> N'Completed',1,0)) [ActiveStatus]
 ,avg(iif(r.status = N'Completed', datediff(second, createdAt, completedAt),0)) [AvgProcessingTime]
from User u
left join Request R
where timestamp > @datefrom
and r.userId = u.id
group by 
u.id

Upvotes: 1

Related Questions