Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

is there a way to improve the performance of a recursive CTE like below, I am unsure if I can add indexes on joins when the joins are using ROW_NUMBER?

DECLARE @File_Name VARCHAR(8000),
        @Disk VARCHAR(5)
SET @File_Name = 'MARSQLUTILITY,AdventureWorksDW_Data'
SET @Disk = 'I:'
--Code to pull out deltas between collected IO stats.
;WITH IOPS   ([IO_STALL]
           ,[IO_STALL_READ_MS]
           ,[IO_STALL_WRITE_MS]
           ,[NUM_OF_READS]
           ,[NUM_OF_WRITES]
           ,[SIZE_ON_DISK_MB]
           ,[DBNAME]
           ,[NAME]
           ,[FILE_ID]
           ,[DB_FILE_TYPE]
           ,[DISK]
           ,[FILE_LOCATION]
           ,[TIMESTAMP]
           ,[ROW])
AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY FILE_LOCATION ORDER BY TIMESTAMP DESC) AS [ROW]
FROM dbo.DISKIOPS 
)

--Need to divide by the number of operations in that timeframe to get average wait time per operation.
--SELECT MAX(([IO2].[IO_STALL] - [IO1].[IO_STALL]) / (IO2.NUM_OF_READS + IO2.NUM_OF_WRITES - IO1.NUM_OF_READS - IO1.NUM_OF_WRITES))
SELECT [IO1].[TIMESTAMP],
       [IO1].[NAME],
      ([IO2].[IO_STALL] - [IO1].[IO_STALL]) / (IO2.NUM_OF_READS + IO2.NUM_OF_WRITES - IO1.NUM_OF_READS - IO1.NUM_OF_WRITES) AS Avg_Stall_Per_Operation
FROM IOPS IO1 JOIN IOPS IO2 ON IO1.ROW = (IO2.ROW+1)
WHERE IO1.NAME = IO2.NAME
--Need to make sure not dividing by 0 when there has been no operations
AND (IO2.NUM_OF_READS + IO2.NUM_OF_WRITES - IO1.NUM_OF_READS - IO1.NUM_OF_WRITES) > 0
AND IO1.Disk = @Disk
share|improve this question
1  
Version of SQL-Server? In 2012, LAG() or LEAD() could be used. –  ypercube Jul 10 '13 at 11:44
    
hi its 2005 unfortunately, although I wasn't aware of those functions so thanks for that. –  Tom Jul 10 '13 at 11:56
1  
That isn't a recursive CTE. What does the execution plan look like? joining on row_number = row_number + 1 at least on later versions seems to be able to use a merge join which means the underlying query is evaluated twice. You could always just materialise it into an indexed #temp table yourself that you self join on though. –  Martin Smith Jul 10 '13 at 12:17
1  
Actually is your join condition missing an equality condition on FILE_LOCATION anyway? Seems strange that you are joining using row_number possibly across partitions. –  Martin Smith Jul 10 '13 at 12:23
1  
@Tom - Even if you happen to know that for your data these two will have the same semantics without the equality condition on FILE_LOCATION you won't get a merge join plan that takes advantage of the order already provided by PARTITION BY FILE_LOCATION ORDER BY TIMESTAMP (you might get a merge join with additional sort operations) –  Martin Smith Jul 10 '13 at 14:50

2 Answers 2

CTEs don't allow you to add indexes, they behave similarly to views but they aren't a persistent object in the database so they can't have indexes (unlike views).

If the bottle neck is the JOIN operation you should resort to a temp table and add an index on the column you will be using to speed up the JOIN operation.

share|improve this answer

your are dealing with a normalization issue here. Instead of adding a timestamp to each row, create a parent table with an identity column and the timestamp column and then use that identity value when inserting into the DISKIOPS table. That way you have a "row number" persisted and do not need to calculate one on the fly. Now, identity is not guaranteed to be continuous but in your use case it probably always will be.

If you can't change the table layout put a clustered index on DISKIOPS(FILE_LOCATION,TIMESTAMP) That should help with the performance of that query.

Also, as @MartinSmith already mentioned, join on FILE_LOCATION, not on NAME. While it might be logically equivalent in your case, only the join on FILE_LOCATION will e able to use that index.

Finally, instead of the where clause to prevent a "division by zero", use this:

([IO2].[IO_STALL] - [IO1].[IO_STALL]) / NULLIF((IO2.NUM_OF_READS + IO2.NUM_OF_WRITES - IO1.NUM_OF_READS - IO1.NUM_OF_WRITES),0)

That causes the value to be NULL without excluding the entire row and more importantly without preventing index usage.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.