I've a website, which is connected to SQL Server Database, I've a well indexed Table with 6,000 Rows. i am displaying all 6,000 rows on my aspx webpage via complex queries. Here is what i am doing.
Generate a Datatable out of SqlDataAdapter. Now this Datatable has 6000 rows.
Bind the DataTable with the Repeater Control
Using PagesDatasource for 25 records per page
Now as you can see, even though i am paging, Sql query still returns 6000 records, so my website could be a little slow.
I was doing research and I read something about not to use DataTables on busy websites, and use List's or Dictionary object instead, Now how can i use dictionary object when i need to store around 8 columns in my output, and easiest way was to sotre them into data table. And Second thing was, use Paging based upon Sql Server (which is very complicated) with complex queries.
So is it ok to use Datatables on website (5,000 hits a day), if not then provide alternatice with example?
And what's the best way to display 6000 records with paging, Pagingdatasource seems a little slower.
Thanks
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
sybe how do you calclute how many total pages for paging? do you run a seperate SQL statement just to return the COUNT of all records? Also i do ORDER BY FIELD DESC and ASC (based upon user input), which will create problems wih your suggestion.
I was reading about SQL Row_number Function, which does exactaly the same thing as you mentioned.
How about Data Tables?
Thanks
> how do you calclute how many total pages for paging? do you run a seperate SQL statement just to return the COUNT of all records?
Yes.
> Also i do ORDER BY FIELD DESC and ASC, which will create problems wih your suggestion.
As I explained, you can pass the ordering along in the query. What would be the problem?
> I was reading about SQL Row_number Function, which does exactaly the same thing as you mentioned.
Yes, I read that too. I don't know why there is such a fuzz about it, because it offers little new and is only supported by few database engines. I remember that in the DOS area DBIII and DBIV also returned a row number. Anyway, I like code that has broad support.
Few optimized things you should know
1) if you are showing only data then use sqlDataReader since its fast..
2) for paging ,,, use Custom paging so it will fetch only current page's record only..( in other case what it does it fetches all records from database and then shows only record's visible page..
so forexample if u are on second page then also it fetches 6000 records and shows records form 25 to 50..
in Custom paging u need to pass couple of more parameters like currentpage,and pagingsize.. and in stored procedure u can return result using only those 2 parameters
Custom paging is flexible enough if you have 6 00 000 records also..
OR
====
2 )if only few records (6000 are also consider as few) then u can put your table inside cache..So dont need to go into database everytime.
> Yes, I read that too. I don't know why there is such a fuzz about it
Seriously, I agree with you 100%.
>if you are showing only data then use sqlDataReader since its fast
Makes sense, but i want to use a disconnected control, so i can play with the data within same page over and over again, with dataAdapter i can use Fill method to populate a datatable, what could i use with dataReader?
Can you please provide me a simple example of Custom Paging.
And Also a example of Cache output would be great.
Thanks in Advance
Business Accounts
Answer for Membership
by: sybePosted on 2009-03-11 at 22:05:48ID: 23864945
I like to use SQL-statements like this:
for page 1:
SELECT TOP 20 <fields>
FROM <table>
page 2:
SELECT TOP 20 <fields>
FROM <table>
WHERE <id> NOT IN (SELECT TOP 20 <id> FROM <table>)
page 3:
SELECT TOP 20 <fields>
FROM <table>
WHERE <id> NOT IN (SELECT TOP 40 <id> FROM <table>)
etc
Or as a formula depending on RecordsPerPage and PageNumber
i RecordsPerPage = 20
iPageNumber = 5
"SELECT TOP " & i RecordsPerPage & "<fields> "
"FROM <table>"
"WHERE <id> NOT IN (SELECT TOP " & (iRecordsPerPage * (iPageNumber - 1)) " & <id> FROM <table>)"
If you have any WHERE clauses and/or ORDER BY, they need to be also in the subquery
i RecordsPerPage = 20
iPageNumber = 5
"SELECT TOP " & i RecordsPerPage & "<fields> "
"FROM <table>"
"WHERE <id> NOT IN (SELECT TOP " & (iRecordsPerPage * (iPageNumber - 1)) " & <id> FROM <table> WHERE <clause> ORDER BY <fields>)"
"WHERE <clause>"
"ORDER BY <fields>"
Works on every database