October 22, 2007 at 2:43 am
Hi All,
At the moment we are developing a search page on website.
I was wondering if anyone had advice with regards to paging the result set rather than return the whole set the web and then paging on the client side. The reason for this is that a search could return 700,000 rows and returning all of them may not be worth it if we only display 20 rows a page and the user only views pages 1 and 2.
Our search can be done either using dynamic sql with a where clause (not preferred, how it does seem fast than non-dynmaic code at the moment)
I have seen a query written before that returns certain rows from a table say for example:
20 rows starting from teh 100 row.
Any advice is much apprechiated.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
October 22, 2007 at 2:54 am
Hi
User can also view the pages 15,36,45 rt?.
If you are displaying only limited and fixed number of records then putting the paging logic in the db is correct.
But if the user can click on pages 4,5,6... and so on then you will end up with that many calls to your database. Keep this in mind.
"Keep Trying"
October 22, 2007 at 2:57 am
That is correct.
The user could click page 10 in which case we would need to show all the rows for that page.
So I guess the correct question is.
Would returning 700,000 rows to the website create more over head than say returning 20 rows to the website 20 times?
e.g calling the server everytime the user changes pages!
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
October 22, 2007 at 6:13 am
Returning only the records you need to display the page will almost always be the better choice.
The ASP.Net controls for Visual Studio 2005 and SQL 2005 have specific support for doing this. You should look at the gridview and in SQL 2005 BOL carefully and you will find them. If you don't, post back here and I will try to find you a link on the web.
The things to be careful with are sorting and filtering options messing up your paging.
October 23, 2007 at 2:20 am
Hi
Returning 20 rows 20 times is better than returning 700,000 rows at one go most of the time and i think that shud be your approach to this.
But do proper testing
"Keep Trying"
October 23, 2007 at 2:29 am
tats true returning required rows (pagewise ) is alwasy better than retuning all data rows if paging is used.
Go ahead
October 23, 2007 at 2:58 am
Hey Chris,
I would suggest to implement the paging concept in Stored Procedure to return 20 rows rather than all the rows.
This Stored Procedure should take one additional parameter like @page, which will pass as user inputs, as per that input the Stored procedure fetch the rows rather than all the rows.
Cheers!
Sandy.
--
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply