This entry won’t be much to anyone used to working with sql or refactoring or the With Clause, but sometimes I like to just walk thru how I started and how I ended when refactoring, and SQL can be refactored just like any other type of code.
I’m working on a SQL procedure where I need to be able to sort a set of items, and find the “next” one in the list. I figured out how to do that using two statements:
DECLARE @rowIndex int;
SELECT
@rowIndex = RowNumber + @IndexDelta
FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY [SheetId] DESC) AS [RowNumber],
[Uid]
FROM
[Sheets.SheetInfo.Functions.SheetList.Sheet.SelectAll](
@SheetType , @CreatedBy , NULL , @LocationUid ,
@ObserverUid , @FromSheetId , @ToSheetId , @FromDate , @ToDate
)
) AS temp
WHERE [Uid] = @SheetUid
SELECT
ISNULL([Uid], CAST('00000000-0000-0000-0000-000000000000' AS UNIQUEIDENTIFIER))
FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY [SheetId] DESC) AS [RowNumber],
[Uid]
FROM
[Sheets.SheetInfo.Functions.SheetList.Sheet.SelectAll](
@SheetType , @CreatedBy , NULL , @LocationUid ,
@ObserverUid , @FromSheetId , @ToSheetId , @FromDate , @ToDate
)
) AS temp
WHERE [RowNumber] = @rowIndexRunning the above code on my humongous load test database (3.5 gig in size), takes 5 seconds.
Well next thing up, is it seems to me we could Nest the entire structure and get rid of the intermediate @rowIndex.
SELECT
ISNULL([Uid], CAST('00000000-0000-0000-0000-000000000000' AS UNIQUEIDENTIFIER))
FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY [SheetId] DESC) AS [RowNumber],
[Uid]
FROM
[Sheets.SheetInfo.Functions.SheetList.Sheet.SelectAll](
@SheetType , @CreatedBy , NULL , @LocationUid ,
@ObserverUid , @FromSheetId , @ToSheetId , @FromDate , @ToDate
)
) AS temp
WHERE [RowNumber] = (
SELECT
RowNumber + @IndexDelta
FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY [SheetId] DESC) AS [RowNumber],
[Uid]
FROM
[Sheets.SheetInfo.Functions.SheetList.Sheet.SelectAll](
@SheetType , @CreatedBy , NULL , @LocationUid ,
@ObserverUid , @FromSheetId , @ToSheetId , @FromDate , @ToDate
)
) AS temp
WHERE [Uid] = @SheetUid
)Well that is simplified and got rid of the intermediate int, but still it seems a little “off” and hard to read
Now as you can see, I’m using a nested from structure where one of my from clauses is to a stored procedure that takes some parameters, and that proc is used in both parts of the query, in fact the first level “from” is identical for both parts
SELECT
ROW_NUMBER() OVER (ORDER BY [SheetId] DESC) AS [RowNumber],
[Uid]
FROM
[Sheets.SheetInfo.Functions.SheetList.Sheet.SelectAll](
@SheetType , @CreatedBy , NULL , @LocationUid ,
@ObserverUid , @FromSheetId , @ToSheetId , @FromDate , @ToDate
)
) AS tempWell rather then declaring this same item twice seems to me this is a good candidate for a “With” clause, and lets see if that cleans things up a bit…
WITH sheetsCte as (
SELECT
ROW_NUMBER() OVER (ORDER BY [SheetId] DESC) AS [RowNumber],
[Uid]
FROM
[Sheets.SheetInfo.Functions.SheetList.Sheet.SelectAll](
@SheetType , @CreatedBy , NULL , @LocationUid ,
@ObserverUid , @FromSheetId , @ToSheetId , @FromDate , @ToDate
)
)
SELECT
ISNULL([Uid], CAST('00000000-0000-0000-0000-000000000000' AS UNIQUEIDENTIFIER))
FROM
sheetsCte
WHERE [RowNumber] = (
SELECT
RowNumber + @IndexDelta
FROM sheetsCte
WHERE [Uid] = @SheetUid
)and in fact that works just GREAT, reads nicely and I think a good exercise in Sql refactoring.
