CODEWITHSUNDEEP
sqlsql-serverperformanceviewsubquery
Pieter_Daems
Pieter_Daems

Reputation: 1264

Subquery in view runs slow

Hello i have an issue with my view. The Subquery causes the view to run very slowly.

SELECT dbo.Calls.CallID
      ,dbo.Calls.StartTime
      ,dbo.Calls.EndTime
      ,dbo.Connections.Connectionname
      ,dbo.Repositorys.RepositoryName
      ,REPLACE(dbo.Calls.Querytime ,',' ,'.') AS Querytijd
      ,dbo.Calls.Uur
      ,dbo.Calls.DayOfMonth
      ,REPLACE(
           (
               SELECT MAX(Querytime)  AS MaxQueryTime
               FROM   dbo.Calls       AS C
               WHERE  (
                          DATEPART(yyyy ,StartTime)=DATEPART(yyyy ,dbo.Calls.StartTime)
                      )
                      AND (DATEPART(M ,StartTime)=DATEPART(M,dbo.Calls.StartTime))               
                      AND (DayOfMonth=dbo.Calls.DayOfMonth)
                      AND (Uur=dbo.Calls.Uur)
                      AND (
                              DATEPART(MINUTE ,dbo.Calls.StartTime)=DATEPART(Minute ,StartTime)
                          )
           )
          ,','
          ,'.'
       ) AS MaxQueryTime
FROM   dbo.Calls
       INNER JOIN dbo.Connections
            ON  dbo.Calls.ConnectionID = dbo.Connections.ConnectionID
       LEFT OUTER JOIN dbo.Repositorys
            ON  dbo.Connections.RepositoryID = dbo.Repositorys.RepositoryID

I basically want the maximum QueryTime where the year/month/day/hour/minute of the StartTime is the same.

Upvotes: 1

Views: 150

Answers (4)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

In SQLServer2005+ you can use OVER() clause

SELECT dbo.Calls.CallID, 
       dbo.Calls.StartTime, 
       dbo.Calls.EndTime,
       dbo.Connections.Connectionname,
       dbo.Repositorys.RepositoryName, 
       REPLACE(dbo.Calls.Querytime, ',', '.') AS Querytijd, 
       dbo.Calls.Uur, 
       dbo.Calls.DayOfMonth, 
       REPLACE(MAX(Querytime) OVER(PARTITION BY DATEPART(yyyy ,StartTime), 
                                                DATEPART(Minute ,StartTime), 
                                                DayOfMonth, Uur), ',', '.'
               ) AS MaxQueryTime
FROM dbo.Calls INNER JOIN dbo.Connections 
                 ON dbo.Calls.ConnectionID = dbo.Connections.ConnectionID 
               LEFT OUTER JOIN dbo.Repositorys 
                 ON dbo.Connections.RepositoryID = dbo.Repositorys.RepositoryID

Upvotes: 2

govinda
govinda

Reputation: 76

When i look at your Query .. you are calculating "MaxQueryTime" for each row. To Optimize this Query you can calculate "MaxQueryTime" once then using union Keyword you can combine to your result set. According to me "MaxQueryTime" will be same for all.

(SELECT REPLACE(MAX(Querytime),',','.')  AS MaxQueryTime
               FROM   dbo.Calls       AS C
               WHERE  (
                          DATEPART(yyyy ,StartTime)=DATEPART(yyyy ,dbo.Calls.StartTime)
                      )
                      AND (DayOfMonth=dbo.Calls.DayOfMonth)
                      AND (Uur=dbo.Calls.Uur)
                      AND (
                              DATEPART(MINUTE ,dbo.Calls.StartTime)=DATEPART(Minute ,StartTime)
                          )) union all

                ( SELECT dbo.Calls.CallID
      ,dbo.Calls.StartTime
      ,dbo.Calls.EndTime
      ,dbo.Connections.Connectionname
      ,dbo.Repositorys.RepositoryName
      ,REPLACE(dbo.Calls.Querytime ,',' ,'.') AS Querytijd
      ,dbo.Calls.Uur
      ,dbo.Calls.DayOfMonth

FROM   dbo.Calls
       INNER JOIN dbo.Connections
            ON  dbo.Calls.ConnectionID = dbo.Connections.ConnectionID
       LEFT OUTER JOIN dbo.Repositorys
            ON  dbo.Connections.RepositoryID = dbo.Repositorys.RepositoryID         )

Upvotes: 0

Santhosh
Santhosh

Reputation: 1791

SELECT dbo.Calls.CallID
  ,dbo.Calls.StartTime
  ,dbo.Calls.EndTime
  ,dbo.Connections.Connectionname
  ,dbo.Repositorys.RepositoryName
  ,REPLACE(dbo.Calls.Querytime ,',' ,'.') AS Querytijd
  ,dbo.Calls.Uur
  ,dbo.Calls.DayOfMonth
  ,REPLACE( QUERYTIME,',','.') AS MaxQueryTime
FROM   dbo.Calls
   INNER JOIN dbo.Connections
        ON  dbo.Calls.ConnectionID = dbo.Connections.ConnectionID
   LEFT OUTER JOIN dbo.Repositorys
        ON  dbo.Connections.RepositoryID = dbo.Repositorys.RepositoryID
LEFT JOIN 
(
SELECT StartTime, Uur, DayOfMonth, StartTime, MAX(QUERYTIME) AS QUERYTIME
FROM dbo.Calls       
GROUP BY StartTime, Uur, DayOfMonth, StartTime
) AS C
ON DATEPART(yyyy ,C.StartTime)=DATEPART(yyyy ,dbo.Calls.StartTime)
AND C.DayOfMonth=dbo.Calls.DayOfMonth
AND C.Uur=dbo.Calls.Uur
AND DATEPART(MINUTE ,dbo.Calls.StartTime)=DATEPART(Minute ,C.StartTime)

Upvotes: 0

govinda
govinda

Reputation: 76

Is it Query working ?

When i look at your Query "From" keyword are used twice in single Query. There are join of two tables only. if you want to make your result fast then filter both table first then make join so there will be less number of row in join which will optimize your result.

SELECT dbo.Calls.CallID, dbo.Calls.StartTime, dbo.Calls.EndTime, 
dbo.Connections.Connectionname, dbo.Repositorys.RepositoryName, 
REPLACE(dbo.Calls.Querytime, ',', '.') AS Querytijd, dbo.Calls.Uur, 
dbo.Calls.DayOfMonth, REPLACE((SELECT MAX(Querytime) AS MaxQueryTime 
FROM dbo.Calls AS C 
WHERE (DATEPART(yyyy, StartTime) = DATEPART(yyyy, dbo.Calls.StartTime))
 AND (DayOfMonth = dbo.Calls.DayOfMonth) AND (Uur = dbo.Calls.Uur) AND 
 (DATEPART(MINUTE, dbo.Calls.StartTime) = DATEPART(Minute, StartTime))), ',', '.') AS MaxQueryTime 

 FROM dbo.Calls INNER JOIN dbo.Connections ON dbo.Calls.ConnectionID = dbo.Connections.ConnectionID 
 LEFT OUTER JOIN dbo.Repositorys ON dbo.Connections.RepositoryID = dbo.Repositorys.RepositoryID

Upvotes: 0

Related Questions