March 16, 2008 at 4:32 am
I'm using a similar SP for Paging and Sorting. I use a slightly different sorting routine, "@OrderBy" is supplied as a integer.
Since I'm developing in C# / NET, I'm using Enum's as sorting parameter, which is more friendly when defining a "SortBy" parameters on Methods (no typo's, no need for remembering column names etc.)
public enum SortBy
{
CodeID = 1,
CategoryID = 2,
Name = 3,
Description = 4,
SourceCode = 5,
IsActive = 6,
IsDeleted = 7,
DateCreated = 8,
DateDeleted = 9,
DateModified = 10
}
The problem is, that i haven't been able to find a solution, where i can get record count from the WITH statement, so I've ended up with an extra SELECT statement for the record count, which I do not find optimal (performance wise) :unsure:. Anyone been able to "crack" this one?
Example 1 (which im curreltly using):
CREATE PROCEDURE [dbo].[sproc_Code_GetList]
@PageNumber Int,
@PageSize Int,
@OrderBy Int,
@OrderType Int,
@IsDeleted Bit,
@RecordCount Int OUTPUT
AS
Declare @RowStart Int
Declare @RowEnd Int
IF @PageNumber > 0
BEGIN
SET @PageNumber = @PageNumber - 1;
SET @RowStart = @PageSize * @PageNumber + 1;
SET @RowEnd = @RowStart + @PageSize - 1;
SET NOCOUNT ON;
WITH [ListEntries] AS (
SELECT
[CodeID],
[CategoryID],
[Name],
[Description],
[SourceCode],
[IsActive],
[IsDeleted],
[DateCreated],
[DateDeleted],
[DateModified],
ROW_NUMBER() OVER (
ORDER BY
CASE WHEN @OrderBy = 1 AND @OrderType = 1 THEN [CodeID] END,
CASE WHEN @OrderBy = 1 AND @OrderType = 2 THEN [CodeID] END DESC,
CASE WHEN @OrderBy = 2 AND @OrderType = 1 THEN [CategoryID] END,
CASE WHEN @OrderBy = 2 AND @OrderType = 2 THEN [CategoryID] END DESC,
CASE WHEN @OrderBy = 3 AND @OrderType = 1 THEN [Name] END,
CASE WHEN @OrderBy = 3 AND @OrderType = 2 THEN [Name] END DESC,
CASE WHEN @OrderBy = 4 AND @OrderType = 1 THEN [Description] END,
CASE WHEN @OrderBy = 4 AND @OrderType = 2 THEN [Description] END DESC,
CASE WHEN @OrderBy = 5 AND @OrderType = 1 THEN [SourceCode] END,
CASE WHEN @OrderBy = 5 AND @OrderType = 2 THEN [SourceCode] END DESC,
CASE WHEN @OrderBy = 6 AND @OrderType = 1 THEN [IsActive] END,
CASE WHEN @OrderBy = 6 AND @OrderType = 2 THEN [IsActive] END DESC,
CASE WHEN @OrderBy = 7 AND @OrderType = 1 THEN [IsDeleted] END,
CASE WHEN @OrderBy = 7 AND @OrderType = 2 THEN [IsDeleted] END DESC,
CASE WHEN @OrderBy = 8 AND @OrderType = 1 THEN [DateCreated] END,
CASE WHEN @OrderBy = 8 AND @OrderType = 2 THEN [DateCreated] END DESC,
CASE WHEN @OrderBy = 9 AND @OrderType = 1 THEN [DateDeleted] END,
CASE WHEN @OrderBy = 9 AND @OrderType = 2 THEN [DateDeleted] END DESC,
CASE WHEN @OrderBy = 10 AND @OrderType = 1 THEN [DateModified] END,
CASE WHEN @OrderBy = 10 AND @OrderType = 2 THEN [DateModified] END DESC
) AS RowNumber
FROM
[Code]
WHERE
[IsDeleted] = @IsDeleted
)
SELECT
[CodeID],
[CategoryID],
[Name],
[Description],
[SourceCode],
[IsActive],
[IsDeleted],
[DateCreated],
[DateDeleted],
[DateModified]
FROM
[ListEntries]
WHERE
RowNumber BETWEEN @RowStart AND @RowEnd
ORDER BY
RowNumber
SELECT @RecordCount = COUNT([CodeID]) FROM [Code] WHERE [IsDeleted] = @IsDeleted
RETURN
END
Example 2 (Uses a temporary table):
CREATE PROCEDURE [dbo].[sproc_Code_GetList]
@PageNumber Int,
@PageSize Int,
@OrderBy Int,
@OrderType Int,
@IsDeleted Bit,
@RecordCount Int OUTPUT
AS
Declare @RowStart Int
Declare @RowEnd Int
IF @PageNumber > 0
BEGIN
SET @PageNumber = @PageNumber - 1;
SET @RowStart = @PageSize * @PageNumber + 1;
SET @RowEnd = @RowStart + @PageSize - 1;
SET NOCOUNT ON;
WITH [ListEntries] AS (
SELECT
[CodeID],
[CategoryID],
[Name],
[Description],
[SourceCode],
[IsActive],
[IsDeleted],
[DateCreated],
[DateDeleted],
[DateModified],
ROW_NUMBER() OVER (
ORDER BY
CASE WHEN @OrderBy = 1 AND @OrderType = 1 THEN [CodeID] END,
CASE WHEN @OrderBy = 1 AND @OrderType = 2 THEN [CodeID] END DESC,
CASE WHEN @OrderBy = 2 AND @OrderType = 1 THEN [CategoryID] END,
CASE WHEN @OrderBy = 2 AND @OrderType = 2 THEN [CategoryID] END DESC,
CASE WHEN @OrderBy = 3 AND @OrderType = 1 THEN [Name] END,
CASE WHEN @OrderBy = 3 AND @OrderType = 2 THEN [Name] END DESC,
CASE WHEN @OrderBy = 4 AND @OrderType = 1 THEN [Description] END,
CASE WHEN @OrderBy = 4 AND @OrderType = 2 THEN [Description] END DESC,
CASE WHEN @OrderBy = 5 AND @OrderType = 1 THEN [SourceCode] END,
CASE WHEN @OrderBy = 5 AND @OrderType = 2 THEN [SourceCode] END DESC,
CASE WHEN @OrderBy = 6 AND @OrderType = 1 THEN [IsActive] END,
CASE WHEN @OrderBy = 6 AND @OrderType = 2 THEN [IsActive] END DESC,
CASE WHEN @OrderBy = 7 AND @OrderType = 1 THEN [IsDeleted] END,
CASE WHEN @OrderBy = 7 AND @OrderType = 2 THEN [IsDeleted] END DESC,
CASE WHEN @OrderBy = 8 AND @OrderType = 1 THEN [DateCreated] END,
CASE WHEN @OrderBy = 8 AND @OrderType = 2 THEN [DateCreated] END DESC,
CASE WHEN @OrderBy = 9 AND @OrderType = 1 THEN [DateDeleted] END,
CASE WHEN @OrderBy = 9 AND @OrderType = 2 THEN [DateDeleted] END DESC,
CASE WHEN @OrderBy = 10 AND @OrderType = 1 THEN [DateModified] END,
CASE WHEN @OrderBy = 10 AND @OrderType = 2 THEN [DateModified] END DESC
) AS RowNumber
FROM
[Code]
WHERE
[IsDeleted] = @IsDeleted
)
SELECT
[CodeID],
[CategoryID],
[Name],
[Description],
[SourceCode],
[IsActive],
[IsDeleted],
[DateCreated],
[DateDeleted],
[DateModified],
[TotalRows]
INTO
#ResultSet
FROM
[ListEntries],
(SELECT COUNT(CodeID) As TotalRows FROM [ListEntries]) As RowCounter
WHERE
RowNumber BETWEEN @RowStart AND @RowEnd
ORDER BY
RowNumber
SELECT TOP(1) @RecordCount = TotalRows FROM #ResultSet
SELECT
[CodeID],
[CategoryID],
[Name],
[Description],
[SourceCode],
[IsActive],
[IsDeleted],
[DateCreated],
[DateDeleted],
[DateModified]
FROM
#ResultSet
DROP TABLE #ResultSet
RETURN
END
Best regards,
Carsten Petersen, Denmark
March 16, 2008 at 5:25 am
Did you try the "COUNT(*) OVER(PARTITION BY '')" option suggested by Matt, earlier in this thread? This will give you the recordcount.
.
March 16, 2008 at 5:37 am
jacob sebastian (3/16/2008)
Did you try the "COUNT(*) OVER(PARTITION BY '')" option suggested by Matt, earlier in this thread? This will give you the recordcount.
I tried 🙂 The problem is (example 1), you can't output columns, and at the same time define/set a variable. So this would require an extra select statement, which is not possible, since only one select is possible with CTE.
Best regards,
Carsten Petersen, Denmark
March 16, 2008 at 6:18 am
Try this
;WITH cte AS (
SELECT
name,
count(*) OVER (PARTITION BY '') cnt,
ROW_NUMBER() OVER (ORDER BY name) AS recID
FROM sys.tables
)
SELECT name, RecID, cnt
FROM cte WHERE RecID BETWEEN 5 AND 10
.
March 16, 2008 at 6:23 am
jacob sebastian (3/16/2008)
Try this;WITH cte AS (
SELECT
name,
count(*) OVER (PARTITION BY '') cnt,
ROW_NUMBER() OVER (ORDER BY name) AS recID
FROM sys.tables
)
SELECT name, RecID, cnt
FROM cte WHERE RecID BETWEEN 5 AND 10
Thats correct. The problem is, that i would like to define the record count on the @RecordCount (OUT) parameter, and not in the result (if possible).
Something like:
;WITH cte AS (
SELECT
name,
count(*) OVER (PARTITION BY '') cnt,
ROW_NUMBER() OVER (ORDER BY name) AS recID
FROM sys.tables
)
SELECT name, RecID, @RecordCount = cnt
FROM cte WHERE RecID BETWEEN 5 AND 10
Which will result in:
"A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations"
Best regards,
Carsten Petersen, Denmark
March 16, 2008 at 8:44 am
Carsten Petersen (3/16/2008)
Thats correct. The problem is, that i would like to define the record count on the @RecordCount (OUT) parameter, and not in the result (if possible).
I have looked at this scenario quite a bit in my DB as well and I don't think there is an elegant solution. You may be able to cheat with a clr udt.
declare @cheater MyUDT
...cte stuff
where ...
or (rowNum = 1 and @cheater.BogusFunction(cte.countCol) = 1)
so BogusFunction() is only invoked one time (when rowNum = 1) and always returns 0 so it doesn't modify your results set. It would store off the input value to a variable that can be retrieved after the fact.
Thoughts?
March 16, 2008 at 9:38 am
What I've come up with until now. Any comments on it (performance etc.)?
CTE Returns Primary Key, Row Number and Total Record Count to @PagerTable, and is joined with Source table.
@RecordCount is defined in @PagerTable.
CREATE PROCEDURE [dbo].[sproc_Code_GetList]
@PageNumber Int,
@PageSize Int,
@OrderBy Int,
@OrderType Int,
@IsDeleted Bit,
@RecordCount Int OUTPUT
AS
Declare @RowStart Int
Declare @RowEnd Int
Declare @PagerTable TABLE (PT_ID INT, PT_ROWNO INT, PT_COUNT INT)
IF @PageNumber > 0
BEGIN
SET @PageNumber = @PageNumber - 1;
SET @RowStart = @PageSize * @PageNumber + 1;
SET @RowEnd = @RowStart + @PageSize - 1;
SET NOCOUNT ON;
WITH [ListEntries] AS (
SELECT TOP (@RowEnd)
[CodeID], /* PRIMARY KEY */
ROW_NUMBER() OVER (
ORDER BY
CASE WHEN @OrderBy = 1 AND @OrderType = 1 THEN [CodeID] END,
CASE WHEN @OrderBy = 1 AND @OrderType = 2 THEN [CodeID] END DESC,
CASE WHEN @OrderBy = 2 AND @OrderType = 1 THEN [CategoryID] END,
CASE WHEN @OrderBy = 2 AND @OrderType = 2 THEN [CategoryID] END DESC,
CASE WHEN @OrderBy = 3 AND @OrderType = 1 THEN [Name] END,
CASE WHEN @OrderBy = 3 AND @OrderType = 2 THEN [Name] END DESC,
CASE WHEN @OrderBy = 4 AND @OrderType = 1 THEN [Description] END,
CASE WHEN @OrderBy = 4 AND @OrderType = 2 THEN [Description] END DESC,
CASE WHEN @OrderBy = 5 AND @OrderType = 1 THEN [SourceCode] END,
CASE WHEN @OrderBy = 5 AND @OrderType = 2 THEN [SourceCode] END DESC,
CASE WHEN @OrderBy = 6 AND @OrderType = 1 THEN [IsActive] END,
CASE WHEN @OrderBy = 6 AND @OrderType = 2 THEN [IsActive] END DESC,
CASE WHEN @OrderBy = 7 AND @OrderType = 1 THEN [IsDeleted] END,
CASE WHEN @OrderBy = 7 AND @OrderType = 2 THEN [IsDeleted] END DESC,
CASE WHEN @OrderBy = 8 AND @OrderType = 1 THEN [DateCreated] END,
CASE WHEN @OrderBy = 8 AND @OrderType = 2 THEN [DateCreated] END DESC,
CASE WHEN @OrderBy = 9 AND @OrderType = 1 THEN [DateDeleted] END,
CASE WHEN @OrderBy = 9 AND @OrderType = 2 THEN [DateDeleted] END DESC,
CASE WHEN @OrderBy = 10 AND @OrderType = 1 THEN [DateModified] END,
CASE WHEN @OrderBy = 10 AND @OrderType = 2 THEN [DateModified] END DESC
) AS RowNumber,
COUNT([CodeID]) OVER (PARTITION BY '') As RecordCount
FROM
[Code]
WHERE
[IsDeleted] = @IsDeleted
)
/* Insert CTE Entries into @PagerTable */
INSERT INTO @PagerTable (PT_ID, PT_ROWNO, PT_COUNT)
SELECT [CodeID], [RowNumber], [RecordCount] FROM [ListEntries] WHERE RowNumber BETWEEN @RowStart AND @RowEnd ORDER BY RowNumber
/* Return result from @PagerTable joined with Source table */
SELECT
[CodeID],
[CategoryID],
[Name],
[Description],
[SourceCode],
[IsActive],
[IsDeleted],
[DateCreated],
[DateDeleted],
[DateModified]
FROM
@PagerTable PT INNER JOIN
[Code] ON PT.[PT_ID] = [Code].[CodeID]
WHERE
PT_ROWNO BETWEEN @RowStart AND @RowEnd
ORDER BY
PT_ROWNO
/* Get @RecordCount from @PagerTable */
SELECT TOP(1) @RecordCount = [PT_COUNT] FROM @PagerTable
RETURN
END
Best regards,
Carsten Petersen, Denmark
March 16, 2008 at 3:23 pm
Adrian Hains (3/16/2008)
Carsten Petersen (3/16/2008)
Thats correct. The problem is, that i would like to define the record count on the @RecordCount (OUT) parameter, and not in the result (if possible).
I have looked at this scenario quite a bit in my DB as well and I don't think there is an elegant solution. You may be able to cheat with a clr udt.
In case anyone is curious, this cheater udt approach does not appear to work. The instance of the udt is not modified when I call the method.
So like
declare @myUdtVar IntValueStoreType
set @myUdtVar = -1
select @myUdtVar.Value --returns -1 as expected
select @myUdtVar.StoreAndEchoInt(12). --returns 12 as expected
select @myUdtVar.Value --returns -1 again, so changes to private int from StoreAndEchoInt() were not persisted.
I assume what is happening is the variable is serialized after each assignment, and deserialized before each query. So it doesn't change in memory unless sql expected it to be an assigment operation. That's my guess anyhow.
You could always use an unsafe clr udf and store the value out somewhere, but that is a lot more then I want to get into.
I don't really like using table variables or temp tables unless I really need to, since I don't have a beefy disk subsystem backing tempdb. So I will probably continue to just query the CTE twice or else return the row count in an extraneous column (not so bad if you always expect very few columns).
March 16, 2008 at 3:29 pm
Carsten Petersen (3/16/2008)
What I've come up with until now. Any comments on it (performance etc.)?CTE Returns Primary Key, Row Number and Total Record Count to @PagerTable, and is joined with Source table.
@RecordCount is defined in @PagerTable.
Wouldn't handling all of these ordering cases prohibit you from keeping a good cached plan for the SP? I would assume that if you order by CodeId you would use a different index than if ordering by DateDeleted. Does this cause the SP to recompile whenever a different ordering is requested? Or is there just a different copy stored for different invocation parameters?
March 17, 2008 at 8:47 am
Adrian Hains (3/16/2008)
Carsten Petersen (3/16/2008)
What I've come up with until now. Any comments on it (performance etc.)?CTE Returns Primary Key, Row Number and Total Record Count to @PagerTable, and is joined with Source table.
@RecordCount is defined in @PagerTable.
Wouldn't handling all of these ordering cases prohibit you from keeping a good cached plan for the SP? I would assume that if you order by CodeId you would use a different index than if ordering by DateDeleted. Does this cause the SP to recompile whenever a different ordering is requested? Or is there just a different copy stored for different invocation parameters?
Looks to me that this would cause recompiles. As a matter of fact - I would probably lean towards defining the SP to ALWAYS recompile, so that it doesn't attempt to use a bad plan.
Bottom line - if you're trying to get two separate things (i.e a number and a recordset) out of the SP - you'll have to make 2 passes through the data.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 30, 2008 at 7:10 am
Hi Sebestian
Did you have a chance to look at these :
1. Find the total number of records affected by the current filters
2. Support for sorting ascending and descending (usually, as a result of clicking on the header of a grid/table at the front end)
3. Some ASP.NET code which shows how this code would work with an ASP.NET grid control (includes how to display custom page numbers at the front end etc..)
VN
May 30, 2008 at 11:03 pm
You can find the total number of rows by using 'COUNT(*) OVER(PARTITION BY '')'. I think some one has already mentioned this earlier in this thread.
You can sort rows ASC/DESC based on a certain column, by generating the ROW_NUMBER() conditionally.
I wanted to put up a follow-up article which shows the ASP.NET part of this. However, got busy with some other stuff and missed it. I will try to present a few examples shortly.
Also, Please note that some times, generating a dynamic query and executing it with 'sp_executesql' might give you better performance (rather than using a conditional static query). I would suggest one should try both approaches and go with the one that gives better performance based on the given query requirement.
.
May 31, 2008 at 10:15 am
jacob sebastian (5/30/2008)
You can find the total number of rows by using 'COUNT(*) OVER(PARTITION BY '')'. I think some one has already mentioned this earlier in this thread.You can sort rows ASC/DESC based on a certain column, by generating the ROW_NUMBER() conditionally.
I wanted to put up a follow-up article which shows the ASP.NET part of this. However, got busy with some other stuff and missed it. I will try to present a few examples shortly.
Also, Please note that some times, generating a dynamic query and executing it with 'sp_executesql' might give you better performance (rather than using a conditional static query). I would suggest one should try both approaches and go with the one that gives better performance based on the given query requirement.
While you certainly CAN use the windowed function to find the count, it unfortunately seems to be a LOT less performant than a derived table approach. It seems to evaluate the count(*) row by row, so in large recordsets re-running that calculation a few million times versus just once will take a huge toll.
I was surprised just how badly this can perform on large sets....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 3, 2009 at 11:08 am
Apologies for resurrecting a long dead thread...
One of my devs has brought http://www.4guysfromrolla.com/webtech/042606-1.shtml to my attention.
It seems to work extremely well - needing only a page number and a number of items to display as parameters.
However it works based on a feature (as far as I can tell undocumented) of Rowcount and select @variable =
I'd appreciate any feedback you chaps have on this.
February 3, 2009 at 11:16 am
Of course that example only works in the case where you can use a sequential numeric column like an identity column to pull the page of data you want. In my experience this only happens 1% of the time, so I end up having to use the ROWCOUNT() function to number my results which can be ordered in many different ways, then filter based it.
Viewing 15 posts - 31 through 45 (of 61 total)
You must be logged in to reply to this topic. Login to reply