I have written a Java web application using servlets and JSPs that acts as a front-end to a SQL Server 2008 database. The app does not know what tables are in the database, but has a table that contains permissions so they app will know which roles can see which tables. So, there are a few tables it knows about, but the data it will be showing are from arbitrary tables with whatever fields they contain.
When the user opens up a table in the app, it fetches the first 10 or 20 or 2000 rows and displays them. Since I don't know what indexes the table might have or not have, I can't write better queries.
Let's say the user wants to see the first 10 rows of a table with millions of rows. I have to do a SELECT * FROM Table using its existing primary keys, if any, and then take the first 10 records. But when the user clicks Next, I show them the next 10 records, but I have to do another SELECT * FROM Table.
I would like to chunk the results, but can't find a way to do this generically. I have heard about OVER and CTE but it seems there have to be indexes for this to work, or at least I have to know which fields to use. And there may not be any indexes on the table. That said, any table with a large number of records does have a primary key.
Maybe the solution is to somehow save that open recordset? Can i store it in the session object? I can't see how this would work.
Any ideas on how to avoid the expensive SELECT * FROM TableName? Or a way to do it only once while the user is still on the same table?