473,325 Members | 2,671 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

strategies for paging

hello, what are the strategies when designing tables that needs
paging?
in the past i used to use

select top 200 * from table
where id not in (select top 100 id from table)
with SQL 2005, would u guys recommend using CTE and/or ROW_NUMBER?

or any other advice?

thanks

Sep 28 '07 #1
4 1646
On Fri, 28 Sep 2007 03:12:25 -0700, Nick Chan wrote:
>hello, what are the strategies when designing tables that needs
paging?
in the past i used to use

select top 200 * from table
where id not in (select top 100 id from table)
with SQL 2005, would u guys recommend using CTE and/or ROW_NUMBER?

or any other advice?
Hi Nick,

With SQL Server 2005, I'd definitely consider the CTE + ROW_NUMBER
approach. The odds are very high that this will perform better than any
other technique - though it's still odds; you'll have to run your own
tests to find out what REALLY is best on your hardware, your data, etc.

Many other techniques are described at the page below; unfortunately it
has not yet been updated for SQL Server 2005 :-((

http://databases.aspfaq.com/database...recordset.html
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Sep 28 '07 #2
>or any other advice? <<

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This is a more basic programming principle than just
SQL and RDBMS.

Sep 29 '07 #3
Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This is a more basic programming principle than just
SQL and RDBMS.
Even a hobbyist would no better.

Google search, "rogerson", it returns 1,710,000 rows, are you seriously
saying you would pass all 1.7 million rows from the SQL Server to the middle
tier or client only to take the first 10?

Do you not think it makes more resource sense to select just the page of
results you need and pass that back from the SQL Server instead? That would
be 10 rows instead of 1.7 million going across that network link to the
middle tier.
in the back end. This is a more basic programming principle than just
SQL and RDBMS.
Not sure what principles you are drawing that statement from but in my
client server training I was taught to do the processing where it is most
appropriate for resource and maintainability reasons.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"--CELKO--" <jc*******@earthlink.netwrote in message
news:11**********************@d55g2000hsg.googlegr oups.com...
>>or any other advice? <<

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This is a more basic programming principle than just
SQL and RDBMS.
Sep 29 '07 #4
thanks all for the help!

On Sep 29, 3:51 am, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALIDwrote:
On Fri, 28 Sep 2007 03:12:25 -0700, Nick Chan wrote:
hello, what are the strategies when designing tables that needs
paging?
in the past i used to use
select top 200 * from table
where id not in (select top 100 id from table)
with SQL 2005, would u guys recommend using CTE and/or ROW_NUMBER?
or any other advice?

Hi Nick,

With SQL Server 2005, I'd definitely consider the CTE + ROW_NUMBER
approach. The odds are very high that this will perform better than any
other technique - though it's still odds; you'll have to run your own
tests to find out what REALLY is best on your hardware, your data, etc.

Many other techniques are described at the page below; unfortunately it
has not yet been updated for SQL Server 2005 :-((

http://databases.aspfaq.com/database...ugh-a-recordse...

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

Oct 2 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: ck388 | last post by:
For some reason when I enable the callback feature of the gridview I still get a page refresh, that is it seems like there is a postback that occurs, not a callback which is just supposed to update...
6
by: Shawn | last post by:
Hi. I already have a datagrid where I'm using paging. I have a stored procedure that fills a temp table with 200-500 rows and then sends back 10 records at the time. When I go to page 2 the SP...
2
by: asad | last post by:
Hello friends, i am designing a ASP.NET page where i want to use custom paging bcoz data is too heavy so pls tell me how can i use custom paging in ASP.NET Thanks
2
by: farhad13841384 | last post by:
Hi , I Hope You fine. I have some problem with this code for paging in asp.net this bottom code work correctly without any error but when I try to place separate code in .VB file then error is...
0
by: anonieko | last post by:
This approach I found very efficient and FAST when compared to the rowcount, or Subquery Approaches. This is before the advent of a ranking function from DB such as ROW_NUMBER() in SQL Server...
2
by: rn5a | last post by:
In a shopping cart app, a ASPX page retrieves the order details & personal details of a user from a MS-Access database table depending upon the username of the user. The order details of a...
3
by: Ronald S. Cook | last post by:
I was told that if calling lots of records from the database (let's say 100,000), that the GridView's paging feature would automatically "handle" everything. But the 100,000 records are still...
5
by: Donald Adams | last post by:
Hi, I will have both web and win clients and would like to page my data. I could not find out how the datagrid control does it's paging though I did find some sample code that says they do it...
0
by: origami.takarana | last post by:
Intrusion Detection Strategies ----------------------------------- Until now, we’ve primarily discussed monitoring in how it relates to intrusion detection, but there’s more to an overall...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.