Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
goknights
Starting Member
1 Post |
 Posted - 2009-08-07 : 08:21:16
|
| My understanding has always been that in regards to paging, it is best practice to use ROW_NUMBER() and return the minimum number of records needed to display each page of data. A co-worker of mine believes that filling a dataset is the way to go, as the impact of opening and closing a connection to the database server each time the user changes pages is greater than the processor/memory/disk io related impact of pulling all the data at once, not to mention the resources used on the application server in paging using a dataset. After researching this on-line, I can't find any mention of the impact of cycling a new connection every time the user pages the data. I always understood the golden rule to be close the db connection as soon as possible. So my question is... Is the opening and closing of database connections associated with using ROW_NUMBER() and ORDER BY really an issue to be concerned about and why?Thank you |
|
|
Sachin.Nand
2937 Posts |
Posted - 2009-08-07 : 08:55:37
|
quote:
Originally posted by goknights
A co-worker of mine believes that filling a dataset is the way to go
But how does he plan to hold the data during postback?PBUH |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-08-07 : 10:26:07
|
| sql side paging has been done long before row_Number() was available so that certainly isn't the only option. Applications usually take advantage of connection pooling even though the application code may be opening/closing connections. But even if it did need to establish a new connection it would be far more efficient than returning a thousand pages of data to a client when they only look at one or two pages.Be One with the OptimizerTG |
|
|
|
|
|
|
Paging and Connections