SQL Server LEFT OUTER JOIN Query Performance

Question

I am experiencing a strange performance issue. I have a view based on a CTE. It's a view that I wrote years ago, and it has been running without issue. Suddenly, 4 days ago, the query that ran in 1 - 2 minutes, ran for hours before we identified the long running query and halted it.

The CTE produces a time-stamped list of transactions that an agent performs. I then Select from the CTE, left joining back to the CTE using the timestamp of the subsequent transaction to determine the length of time an agent spend on each transaction.

WITH [CTE_TABLE] (COLUMNS) AS
    (
    SELECT [INDEXED COLUMNS]
         ,[WINDOWED FUNCTION] AS ROWNUM
    FROM [DB_TABLE]
    WHERE [EMPLOYEE_ID] = 111213
    )

    SELECT [T1].[EMPLOYEE_ID]
        ,[T1].[TRANSACTION_NAME]
        ,[T1].[TIMESTAMP]          AS [START_TIME]
        ,[T2].[TIMESTAMP]          AS [END_TIME]
    FROM [CTE_TABLE] [T1]
         LEFT OUTER JOIN [CTE_TABLE] [T2] ON
            (
            [T1].[EMPLOYEE_ID] = [T2].[EMPLOYEE_ID]
            AND [T1].[ROWNUM]  = [T2].[ROWNUM] + 1
            )

In testing I filter for a specific agent. If it run the inner portion of the CTE it produces 500 records in less than a second. (When not filtering for a single agent, it produces 95K records in 14 seconds. This is the normal running timeframe.) If I run the CTE with a simple SELECT * FROM [CTE_TABLE], it also runs in less than a second. When I run it using an INNER JOIN back to itself, again, runs in less than a second. Finally, when I run it as a LEFT OUTER JOIN it takes over a minute and a half just for the 500 records of a single agent. I need the LEFT OUTER JOIN because the final record of the day is the agent's log-off the system, and it never has a record to join to.

The table that I pull from is over 22GB in size, and has 500 Million rows. Selecting the records from this table for a single day takes 14 seconds, or a single agent in less than a second, so I don't think the performance bottleneck comes from the source table. The bottleneck occurs in the LEFT OUTER JOIN back to the CTE, but I have always had the LEFT OUTER JOIN. Again, the very strange aspect is that this only began 4 days ago. I have checked space on the server, there is plenty. The CPU spikes to approx. 25% and remains there until the query ends running, either on its own, or halted by an admin.

I am hoping someone has some ideas as to what could have caused this. It appears to have cropped up from nowhere.

What version of SQL Server are you running? Looks like a good candidate for using LEAD/LAG if you're on 2012 upwards. — Gareth Lyons
– Gareth Lyons, Commented Jan 31, 2017 at 16:05

TheGameiswar · Accepted Answer · 2017-01-31 15:59:21Z

1

Again, the very strange aspect is that this only began 4 days ago

I recommend updating statistics on the tables involved and also try rebuilding indexes

The bottleneck occurs in the LEFT OUTER JOIN back to the CTE

CTE will not have any statistics,i would recommend materalizing the CTE into a Temp table to see if this helps

answered Jan 31, 2017 at 15:59

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

UncleJasper75 Over a year ago

Rebuilding the indexes worked! Thank you! If possible, why would that have impacted the performance of INNER JOIN Vs. LEFT OUTER JOIN of an in memory representation of the data?

TheGameiswar Over a year ago

Changing a join causes optimizer to take different path of scan or seek ,which is further based on stats available

TheGameiswar Over a year ago

@UncleJasper75: also please post execution plan ,going forward

TheGameiswar Over a year ago

if this data is totally present in memory,i dont see any issue with fragmentation

Collectives™ on Stack Overflow

SQL Server LEFT OUTER JOIN Query Performance

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related