CODEWITHSUNDEEP
sqlsql-serverperformancesubquery
Lucifer
Lucifer

Reputation: 2357

Sql nested queries execution performance

Following is my nested SQL query:

SELECT M.UserID, SUM(M.Browser)
FROM
(
    SELECT UserID, X.Browser
    FROM
    (
        SELECT UserName, PCMLogEventID, MAX(Browser) AS Browser
        FROM [PCMDBSERVER].[MISTestPCM_Raw].[dbo].[PCM_Log_FilterSwitchData] 
        WHERE [DateTime] BETWEEN '6/12/2013 12:00:00 AM' AND '6/12/2013 11:59:59 PM'
        GROUP BY UserName, PCMLogEventID
    ) X
    INNER JOIN ( 
        SELECT * 
        FROM PCM_Stat_UserRepository 
        WHERE MachineID='All' 
    ) Y
    ON X.UserName = Y.UserName
) M
GROUP BY M.UserID

The execution time for the inner query -with the select clause (Select UserID, X.Browser), only takes 1 second to execute, returning just 197 rows. However when I execute the entire nested query it take almost 6 minutes to return the result. Could anyone help me understand why does it take such a long time ?

EDIT: Actually PCMLogEventID is needed. Because the data in PCM_Log_FilterSwitchData is something like this: UserName | PCMLogEventID | Browser abc | 111 | 0.9 abc | 111 | 1.2 abc | 222 | 1.2 abc | 222 | 3.5 . . Thus i'm first taking the MAX by grouping UserName & PCMLogEventID, and then the SUM of it.

Upvotes: 0

Views: 1142

Answers (2)

Lucifer
Lucifer

Reputation: 2357

This is what worked for me. I have changed the order: First MAX, then SUM, and then JOIN.

    Select Y.UserID, P.Browser
    FROM
    (
        SELECT DISTINCT X.UserName, SUM(X.Browser) as Browser
        FROM
        (
            SELECT UserName, PCMLogEventID, MAX(Browser) AS Browser
            FROM [PCMDBSERVER].[MISTestPCM_Raw].[dbo].[PCM_Log_FilterSwitchData] 
            WHERE [DateTime] BETWEEN '6/12/2013 12:00:00 AM' AND '6/12/2013 11:59:59 PM'
            GROUP BY UserName, PCMLogEventID
        ) X
        GROUP BY X.UserName
        ) P
        INNER JOIN
        ( SELECT * FROM PCM_Stat_UserRepository WHERE MachineID='All' ) Y
    ON P.UserName = Y.UserName

Upvotes: 1

Devart
Devart

Reputation: 122042

Possible this be helpful for you -

SELECT M.UserID, Browser = SUM(X.Browser)
FROM
(
    SELECT 
          UserName
        , Browser = MAX(Browser) 
    FROM [PCMDBSERVER].[MISTestPCM_Raw].[dbo].[PCM_Log_FilterSwitchData] 
    WHERE [DateTime] BETWEEN '20130613 12:00:00' AND '20130613 23:59:59'
    GROUP BY UserName
) X
JOIN (
    SELECT 
          UserName
        , UserID 
    FROM dbo.PCM_Stat_UserRepository 
    WHERE MachineID = 'All' 
) M ON X.UserName = Y.UserName
GROUP BY M.UserID

Upvotes: 0

Related Questions