1

I'm building an ajax application and I have a choice to make on how to structure a data paging functionality. Basically, the user is going to work with 1,000 - 2,000 records but only 20 records are loaded simultaneously from the DB, sent to the client, and then cached in the local session storage. These records are json strings that contain an object's property (basically, a customer record).

Here are the two choices, let's assume we're working with 1,500 records and that each page is 20 records long. The dilemma is where to store the 1,500 IDs of the records.

Option 1: The IDs of the 1,500 records are stored as a list of int on the server session, and serialized and deserialized for each request. The client is unaware of the record IDs of each page and just requests a page number. On the server, when the user requests page X, we look at the list of int and determine the IDs of the records for page X, load those records and send the client the json strings with these IDs. The downside here is that we're carrying a list of 1,500 ints on the server session. The upside is that when we're requesting a page, we're only sending the server an int.

Option 2: The IDs of the 1,500 records are sent to the client when the user logs in and then that list is stored in the local storage session. When the user requests page X, a javascript function determines which record IDs are on page X and sends the server a json serialized list of 20 ints. The server then returns the records with those IDs. The upside is that the server side session is much lighter and that should increase performance (no need to read/write/serialize that list at every request) and probably more scalable. The downside is that we're sending the server a list of 20 ints every time we're requesting a page.

I'm leaning for option 2: is this the best way to do it?

Note 1) that I'm requiring users to support HTML5 local storage and 2) the server-side implementation will be azure and WCF.

frenchie
  • 51,731
  • 109
  • 304
  • 510
  • Why does the client (or the server for that matter) need to store these ID's at all? Can't you just store the current page number and get the next page? Seems like you're doing way more work than you need to, but maybe I'm missing something. – CodingGorilla Mar 23 '12 at 20:49
  • Do you use SQL for the data ? or they are stored some where else ? and if you use SQL is necessary to cache the data ? if not then you can use the direct paging commands of SQL and avoid to keep anything but the page number and the page size. – Aristos Mar 23 '12 at 20:49
  • store the 1500 ids in the database?!? client requests a page number? boom done. – dotjoe Mar 23 '12 at 20:51
  • If the data is universal for all users, using Application.Cache would seem reasonable. If the data is likely to be more than 5MB, you might run in to hassle with the available local storage ( http://stackoverflow.com/questions/7267354/javascript-memory-and-html5-localstorage-limitations-on-smartphones ). And if the SQL Server is the new 2012 version, it might be reasonable to hit the database every time ( https://sqlblog.org/2010/11/10/sql-server-v-next-denali-using-the-offset-clause-paging ). – Andrew Morton Mar 23 '12 at 20:51
  • The 1500 IDs are stored in a table in the form of a string. I read that field and now I have the IDs. I don't want to be rereading this field from that table each time the user is looking for data: I'm looking to store it either in the user's server-side session or in the user's local storage. The records are json strings stored in a table and with the record ID as the primary key. – frenchie Mar 23 '12 at 22:20
  • 1
    Have you measured the impact of the extra query for the server session? That's what I'd lean towards, but I'd want to see data either way before deciding. – Jon Skeet Mar 24 '12 at 07:53

1 Answers1

0

Perhaps I am misunderstanding but I don't really understand your approach. If you know the page length and you know the page number that is desired, why can't you send these along as parameters to the server in your ajax call and then limit it to this in your sql statement for the db. What I mean to say is what is the point of knowing these IDs in the first place. For example look at how the jquery datatables plugin performs pagination here:http://www.datatables.net/examples/server_side/server_side.html . As you can see they use the method of posting the page number and page location to the server. Then, its pretty easy to limit the rows you want returned based on this (at least in Oracle, MySQL, and MS SQL). Unless I am completely missing the point in which case please let me know.

user1288802
  • 83
  • 5
  • Because if I know the IDs, then I can say "get me the records with IDs x,y,z...". If I don't know the IDs, before I can even request the records, I need to say "go get the list of all the IDs and figure out which records are on page X" before I can even request the records. Knowing the IDs saves one query and that's why I'm caching them; now I need to figure out where it'd be best to store them: server-side session or local storage. – frenchie Mar 24 '12 at 00:17
  • As everyone seems to feel above as well...its still 20 records per page...simple solutions, simple solutions. – user1288802 Mar 26 '12 at 20:34