August 18, 2008 at 6:59 am
I know this question might be hard to answer. I was hoping to get a general opinion.
I came across the following t-sql in an asp.net book:
CREATE PROCEDURE dbo.GetPagedMovies2005
(
@StartRowIndex INT,
@MaximumRows INT
)
AS
WITH OrderedMovies AS
(
SELECT
Id,
ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber
FROM Movies
)
SELECT
OrderedMovies.RowNumber,
Movies.Id,
Movies.Title,
Movies.Director
FROM
OrderedMovies
JOIN Movies
ON OrderedMovies.Id = Movies.Id
WHERE
RowNumber BETWEEN (@StartRowIndex + 1) AND (@startRowIndex + @maximumRows + 1)
Normally I would have done something simliar with one exception. My CTE would have listed all of the fields from the select clause instead of just the Start and Max Rows. The plus side to my method is that I don't need the join between Movies and OrderMovies. The down side of my method is that if I need to change the selected fields I have to make the same change in two places.
The method from the asp.net book seems cleaner and easier to maintain. I was wondering if the asp.net books method is the preferred method and if the join would negatively impact a selection of say 300,000 rows?
Thanks
August 18, 2008 at 7:12 am
The easiest way to see the result is to run it both ways and compare the execution plans. I tend to run both in a single batch - this will give you the difference in performance pretty clearly since it will list the queries cost percentage of the entire batch.
I believe the additional join back to the movies table will result in a slower query. I do not think the optimizer will be able to remove the extra join based on this syntax, so you will end up with a self join that will increase the overall cost.
Remember, examples are examples. They are not necessarily optimized all that well and are not written specifically for your situation. Use them as a guide rather than a bible.
August 18, 2008 at 7:46 am
Thanks. That's was very good advice.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply