April 28, 2008 at 8:48 pm
which custom paging is better (e.g. more efficient, etc)
http://www.4guysfromrolla.com/webtech/042606-1.shtml
or
http://www.eggheadcafe.com/articles/20060109.asp
thanks!
matt
April 28, 2008 at 11:23 pm
[font="Verdana"]Second approach is much better than first one. It will decrease the network traffic tremendously.
By the way, if you would have been posted the question directly instead of provinding URLs, we could save our most of the time.
Mahesh[/font]
MH-09-AM-8694
April 29, 2008 at 9:36 am
sorry, here is the code:
CREATE PROCEDURE [dbo].[usp_PageResults_NAI]
(
@startRowIndex int,
@maximumRows int
)
AS
DECLARE @first_id int, @startRow int
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that
-- Get the first employeeID for our page of records
SET ROWCOUNT @startRowIndex
SELECT @first_id = employeeID FROM employees ORDER BY employeeid
-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows
SELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
e.DepartmentID = d.DepartmentID
WHERE employeeid >= @first_id
ORDER BY e.EmployeeID
SET ROWCOUNT 0
GO
--------- Versus ------------
CREATE PROC dbo.GetPagedData
@pageSize int,
@tablename varchar(100) ,
@primarykey-2 varchar(50) ,
@CurrentPage int ,
@WhereClause varchar(250)
AS
if(@WhereClause IS NULL or @WhereClause='') Set @WhereClause=' 1=1 '
Declare @sql nvarchar(4000)
declare @numrecs int
Set @numrecs=@pageSize*@currentPage
set @sql='SELECT TOP ' + cast(@pageSize as varchar(5))+' * FROM ' +@tablename
Set @sql =@sql + ' WHERE '+cast(@PrimaryKey as varchar(50))+ ' NOT IN (SELECT TOP '
set @sql=@sql+ cast(@numrecs as varchar(5))
set @sql=@Sql+ ' ' +@primarykey +' FROM ' +@tableName + ' WHERE '+@whereClause +
' ORDER BY ' +cast(@primarykey as varchar(50)) +' ) '
set @Sql=@sql + ' AND '+ @whereClause
Set @Sql=@Sql + ' ORDER BY '+cast(@primarykey as varchar(50))
--print @sql
EXEC sp_executeSql @sql
Set @sql='Select count(*) FROM ' +@tablename + ' WHERE ' +@whereclause
EXEC sp_executeSql @sql
Which is better for ASP.NET custom paging??
April 30, 2008 at 9:11 am
There is not that much difference between them, performance-wise.
The first one is clearly older as it uses the deprecated SET ROWCOUNT XX (this should be changed to TOP XX instead).
The second one does more and is more flexible as it uses Dynamic SQL, however it is a severe Injection target, so I would be cautious about using it.
And frankly, I wouldn't want to be using either one when trying to look at the last page of a million row table, but I am not sure how that can be made better without making changes to the source table to begin with.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 30, 2008 at 9:12 am
Mahesh Bote (4/28/2008)
[font="Verdana"]Second approach is much better than first one. It will decrease the network traffic tremendously.[/font]
I'm not sure that I follow this, Mahesh. Could you explain why?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 30, 2008 at 9:40 am
another article compares four methods:
http://www.codeproject.com/KB/aspnet/PagingLarge.aspx
Conclusion: "The methods performed in the following order, starting from the best one - RowCount, Cursor, Asc-Desc and Subquery."
April 30, 2008 at 9:43 am
someone in another forum said the dynamic sql version (the 2nd one) was clearly faster/better
April 30, 2008 at 11:25 am
matt6749 (4/30/2008)
another article compares four methods:http://www.codeproject.com/KB/aspnet/PagingLarge.aspx
Conclusion: "The methods performed in the following order, starting from the best one - RowCount, Cursor, Asc-Desc and Subquery."
OK, I skimmed that article, at lot of good work, but I see a couple of errors and a whole lot of assumptions that don't seem correct. I don't have time to test things right now though, maybe tonight...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 30, 2008 at 11:28 am
matt6749 (4/30/2008)
someone in another forum said the dynamic sql version (the 2nd one) was clearly faster/better
Similar claim was made here. Point me to it and I will take a look at it, however, I need some kind of evidence or reasoning to evaluate it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 30, 2008 at 11:33 am
thanks Barry - I'm having trouble with all this stuff - i would think there has to be a generally best way to code custom paging in asp.net, but I'm getting all sorts of conflicting opinions
April 30, 2008 at 12:02 pm
I've always stayed away from dynamic sql inside a stored procedure as I thought that SQL server wasn't able to cache the execution plan, so the static strucutre would be more optimized from the server's perspective. Its also easier to read and maintain if necc. As for performance of the tow choices, I'd pick option 1 with a few tweaks.
April 30, 2008 at 1:44 pm
matt6749 (4/30/2008)
thanks Barry - I'm having trouble with all this stuff - i would think there has to be a generally best way to code custom paging in asp.net, but I'm getting all sorts of conflicting opinions
It's a tough problem, especially in the general case (which is typically the case that you have for ASP.net paging).
I suspect that there should be a better way to do it using the new "Over"-type functions in SQL 2005, but I am not too handy with them. Maybe someone who knows them better could comment.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 30, 2008 at 7:01 pm
My random and ad-hoc tests leads me to believe that this is about as good as you can do
Create Proc spTableRows_Window(@startingRow int, @rows int)
AS
--NOTE: some stuff stolen from Jeff Moden and Matt Miller
Select * From ExampleTable
Where LookupCol IN(
Select LookupCol from
(select LookupCol, row_number() over (order by LookupCol) RN
from ExampleTable) r
Where rn between @startingRow and @startingRow+@rows
)
Still it is highly variable depending on:
A) whether LookupCol is a Clustered Index
B) whether LookupCol is indexed at all
C) how many more columns you want to return other than LookupCol
D) how big ExampleTable actually is
E) and how far in @startingRow actually is.
for different variations of these, you can almost always make a custom-specific version that is faster.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 30, 2008 at 7:43 pm
thanks a lot Barry!
April 30, 2008 at 7:46 pm
rbarryyoung (4/30/2008)
There is not that much difference between them, performance-wise.The first one is clearly older as it uses the deprecated SET ROWCOUNT XX (this should be changed to TOP XX instead).
The second one does more and is more flexible as it uses Dynamic SQL, however it is a severe Injection target, so I would be cautious about using it.
And frankly, I wouldn't want to be using either one when trying to look at the last page of a million row table, but I am not sure how that can be made better without making changes to the source table to begin with.
I've actually used something similar to the second one on a million row table... and it's nasty fast (< 500 mms). The new trick in 2k5 would, of course, to use TOP (XX) but with the "RECOMPILE" option to keep the wrong execution plan from being used. I was actually on the thread where we discussed all this and I'll try to find it when I get a minute...
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply