Database pagination
-
Hi all, I'm actually designing an internet application, and I'd like to know how to paginate the information retrieved from the database. The problem is that I access a huge database (SQLServer 2000) and I don't want tho get all the records everytime I execute a query, I'd like to paginate the records and get them in different queries. I've thought of two methods to do that and I'd like to know which one is better, so if anybody knows it (or knows another way to solve it) please help me! 1. Use SQL queries of the type SELECT TOP x FROM aTable WHERE aTable.Id > @LastIdReceived The problem I find in this method, is that I'm accessing a huge table (about 900.000 records) and is a complicated query (with Joins to a lot of other tables) so I think that if I do it this way, each time I have to execute the full query (and there's also the fact that with this way I can't know before the total amount of records affected by the query, I could do a COUNT first, but this would be another query) 2. Create a temporary table of indexes with the query I need and then, retrieve the information I need by joining it with the real table (with the amount of records I need). The problem I see this way is that if there are a lot of users asking for queries at the same time, creating so many temporary tables might be inefficient. So, If somebody has ever faced this problem I'd like to know which way is better or if there's another way I've not thought about. Thanks.
-
Hi all, I'm actually designing an internet application, and I'd like to know how to paginate the information retrieved from the database. The problem is that I access a huge database (SQLServer 2000) and I don't want tho get all the records everytime I execute a query, I'd like to paginate the records and get them in different queries. I've thought of two methods to do that and I'd like to know which one is better, so if anybody knows it (or knows another way to solve it) please help me! 1. Use SQL queries of the type SELECT TOP x FROM aTable WHERE aTable.Id > @LastIdReceived The problem I find in this method, is that I'm accessing a huge table (about 900.000 records) and is a complicated query (with Joins to a lot of other tables) so I think that if I do it this way, each time I have to execute the full query (and there's also the fact that with this way I can't know before the total amount of records affected by the query, I could do a COUNT first, but this would be another query) 2. Create a temporary table of indexes with the query I need and then, retrieve the information I need by joining it with the real table (with the amount of records I need). The problem I see this way is that if there are a lot of users asking for queries at the same time, creating so many temporary tables might be inefficient. So, If somebody has ever faced this problem I'd like to know which way is better or if there's another way I've not thought about. Thanks.
Try this: http://www.4guysfromrolla.com/webtech/062899-1.shtml cheers, Chris Maunder (CodeProject)
-
Try this: http://www.4guysfromrolla.com/webtech/062899-1.shtml cheers, Chris Maunder (CodeProject)
I've read it and I think it's more less what I'd thought as my second option. The problem I see is maybe creating the temporary table won't be efficient when a lot of users are asking queries since a lot of temporary tables will be created on the server (and I expect that situation), am I wrong and it won't affect too much ? Maybe there isn't a better way to do it. thanks for everything.
-
I've read it and I think it's more less what I'd thought as my second option. The problem I see is maybe creating the temporary table won't be efficient when a lot of users are asking queries since a lot of temporary tables will be created on the server (and I expect that situation), am I wrong and it won't affect too much ? Maybe there isn't a better way to do it. thanks for everything.
The site that Chris suggested would provide an efficient program because the temporary tables are created in memory, and then destroyed as soon as the stored procedure ends. -- Andrew (first post in this forum :)).
-
Hi all, I'm actually designing an internet application, and I'd like to know how to paginate the information retrieved from the database. The problem is that I access a huge database (SQLServer 2000) and I don't want tho get all the records everytime I execute a query, I'd like to paginate the records and get them in different queries. I've thought of two methods to do that and I'd like to know which one is better, so if anybody knows it (or knows another way to solve it) please help me! 1. Use SQL queries of the type SELECT TOP x FROM aTable WHERE aTable.Id > @LastIdReceived The problem I find in this method, is that I'm accessing a huge table (about 900.000 records) and is a complicated query (with Joins to a lot of other tables) so I think that if I do it this way, each time I have to execute the full query (and there's also the fact that with this way I can't know before the total amount of records affected by the query, I could do a COUNT first, but this would be another query) 2. Create a temporary table of indexes with the query I need and then, retrieve the information I need by joining it with the real table (with the amount of records I need). The problem I see this way is that if there are a lot of users asking for queries at the same time, creating so many temporary tables might be inefficient. So, If somebody has ever faced this problem I'd like to know which way is better or if there's another way I've not thought about. Thanks.
First off, using a SQL statement such as "Select count(*) from here inner join this on here.id = this.id" will return a lot faster than actually having to return the actual results of the query. I would say that it is always a good idea to look at the size of your returning recordset when dealing with such large datasets. Second, there are a few pages from another site that I have used in the past for searches that will result in large recordset. I would recommend this first page because you are using SQL Server 2000 you can write your query as a stored procedure and page through it. The second link allows you to do the same thing with a generic recordset via a SQL statement built on the client-side. Good Luck, hope this helps. Links: 1. Paging Through Records Using A Stored Procedure 2. Paging Though Database Results N Records At A Time Nick Parker
-
The site that Chris suggested would provide an efficient program because the temporary tables are created in memory, and then destroyed as soon as the stored procedure ends. -- Andrew (first post in this forum :)).