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 |
|
TurdSpatulaWarrior
Starting Member
36 Posts |
 Posted - 2003-04-25 : 11:12:49
|
| I am currently in the early stages of developing a forum in Microsoft .NET for the company I work for. There have been numerous posts here and on various .NET forums regarding database paging with a few different solutions of varying degrees of practicality. The simple answer would be to use the .NET datagrid to accomodate paging, however the datagrid has a good bit over overheard and is limited to some extent in the amount of appearance customization it posesses, plus I hate the datagrid anyway :). Additionaly, the datagrid's caching abilities are great if you are accessing data that doesn't change with high frequency, however one of the forums that will be moved to the new .NET forum upon completion averages right at 2000 posts per day. Caching would be nearly a moot point with this. Here are the solutions that I am currently mulling over:Please note that some of the syntax might be off a little as I just wrote all this through without checking it too thoroughly.Option 1: Using a sequential id field in addition to the standard primary key id field. This would be maintained via a trigger and would enable me to SELECT FROM [table] WHERE (SequentialIDField BETWEEN X AND Y). This option seems pretty efficient, however it would rule out different types of sorting on the various forum pages. Additionally, there would be tons of hairy trigger updates going on in order to keep the most recently posted to topic at the top of the list; so this solution will not work in all spots.Option 2: The other solution would be to use a subquery. For example:SELECT TOP [ResultsPerPage] ID, [SomeOtherField], [and another field bla bla]FROM [Table]WHERE ID IN(SELECT TOP [ResultsPerPage * PageNumber] ID FROM [Table] ORDER BY [SortField] DESCENDING)ORDER BY [SortField] ASCENDINGThis works, however what happens when someone chooses to view all the posts in a forum from the beginning, then goes to read page 2,000? That's a lot of unecesary records being selected in the subquery.Option 3: Use a temporary table to select the specific rows you need. Something like:CREATE TABLE #Temp (ID int, SequentialID int IDENTITY (1, 1))INSERT INTO #Temp(ID)SELECT TOP [PageNumber * ResultsPerPage] ID FROM [Table] WHERE ([Search Criteria]) ORDER BY [Some Sort]SELECT ID, [Other fields and poo]FROM [Table] TINNER JOIN #Temp ON #Temp.ID = T.IDWHERE (#Temp.SequentialID BETWEEN [ResultsPerPage*PageNumber-ResultsPerPage+1] AND [ResultsPerPage*PageNumber])DROP TABLE #TempIt works, but even more overhead. A couple hundred people on a forum at once all creating a temp table? Even though it's a small efficient temp table with only two numeric fields, I'm sure it would chew some proecssor. This solution is one currently being used by a commercial .NET forum (which I will leave anonymous) I was considering using until I found out they were accomodating paging withtout the datagrid by using this method.The way I'm thinking right now, I will probably be using option # 2 (the subquery method) for the page that the equivolent to this forum's forum.asp page, since most regular forum users will only be reading on the first few pages anyway, which means it won't be too often the database will have to select thousands of record id's in the subquery.For the page that is the equivolent to this forum's topic.asp page, I'm currently using a sequential id field (option # 1) that is set during the insert. Each topic has it's own set of sequential id's, and there is a field holding the number of posts per topic, so that I can select the specific records I need based on the page number, and whether the sort is by last post ascending or desceding. This eliminates the ability to sort topics by anything other than the time posted, but who really even would want to view things in any other order on that page?For the search page, I am currently resigned to using the bloody Microsloth datagrid. Using a subquery would mean, in a sense, executing two possible heavy queries instead of one. There's no way to have any sort of built in sequential number scheme on that. Temporary tables would work I suppose, but I have efficieny concerns as mentioned before. Hey I got it!!! I should use cursor based paging!! Just kidding....Anyway, I figured I'd put my thoughts down in writing and possibly get some insights from people with greater knowledge than myself (prolly most everyone reading this :) ). |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-04-26 : 05:34:39
|
| How about Option 4:Select the data into a real table, with a GUID for the search ID and a column for the datetime that the search was done. You can then page from that table quickly, so long as you know the GUID, which is easy enough. Use a job to periodically clean up that table by deleting old search results.If the actual search query is very expensive, that's the best way to do it. If the original search isn't too bad, then you're better off with something like your option 3, but using a table variable rather than a temp table.Cheers-b |
 |
|
|
TurdSpatulaWarrior
Starting Member
36 Posts |
Posted - 2003-04-28 : 11:24:34
|
| That's a good idea for the search results. I think the widely used vBulletin forums do something similar. Thanks for your feedback. |
|
|
|
|
|
|
|
|
Database paging debate