Need to improve performance when pulling data from SQL Server tables -OR- Can I store a resultest in the session object?

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?
jkurantAsked:
Who is Participating?
 
Anthony PerkinsCommented:
mccarl,

Thank you. for clarifying  now that you put it that way, I can see where the author may have got carried away.
0
 
Scott PletcherSenior DBACommented:
You can do "SELECT TOP (10) * FROM ...", and obviously you should.

If you know a key value, take the highest one on the current page and do this:

"SELECT TOP (10) * FROM ... WHERE key_value > last_read_key_value ORDER BY key_value"
0
 
jkurantAuthor Commented:
I had been doing select TOP but that was even slower in some cases, and I don't understand why. Also, the table may not have any   key fields or multiple key fields and I think it would be very hard to construct a where clause based on multiple fields.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott PletcherSenior DBACommented:
It won't be slower if you have an index keyed by the search column.

Without an index, a simple SELECT * without an order by may be faster, but you will get the rows in a random order, so the "next 10" rows won't have any meaning: you could repeat rows and/or skip rows from one group of rows to the next.
0
 
jkurantAuthor Commented:
I said, " Since I don't know what indexes the table might have or not have, I can't write better queries." and "... data it will be showing are from arbitrary tables with whatever fields they contain." So, your suggestions are not applicable, @ScottPletcher. It was nice of you to reply, but that did not help.

It may be that I already have the best solution.
0
 
gurpsbassiCommented:
I'm confused.
You say you have an app that is querying a database but you don't know what indexes are available.
How can this be?

Surely you can see the database or have some schema definition for it? How else can you expect to write SQL against it?
0
 
jkurantAuthor Commented:
the app is a front end to whatever is in the database. the database can change while the code in the app must not. so, i don't know when i am coding the app what indexes may exist or even what fields exist or even which tables exist. that said, most tables have primary keys and if my solution requires primary keys on all tables, then i can have those set up.

so, when the user opens an table in the app and the app displays the first 50 rows, it still had to do a SELECT * FROM TableName, which takes a long time just to run on the server when the table has millions of rows. I was hoping to take that hit just once. So, when the user clicks Next and the app needs to show the user the next 50 rows, I am doing the same SELECT * FROM TableName and displaying rows 51-100.

I was hoping there would be a way to save the recordset or something else in the session object that would eliminate the need to run that query again.
0
 
jkurantAuthor Commented:
also, i can query the database to find out what fields are in it, and that is exactly what i do. i query the database to find out what tables there are, let the user select one, then query the database to get the fields in the table so I can display them to the user. I also let the user set a "filter" using a UI and builds a WHERE clause used in the query. So the application doesn't know anything about the tables in the database other than the few that are there for user access control.
0
 
jkurantAuthor Commented:
That is, I was hoping to save something other than the entire contents of the table in memory, such as an open recordset, but I can't really imagine how that would work in between trips to the server.
0
 
Anthony PerkinsCommented:
also, i can query the database to find out what fields are in it, and that is exactly what i do.
If you are allowing the application to query the "database" (I sespect you mean the table) to get the columns (as an aside this is typically not done for security reasons), than perhaps you should support querying the table for the indexes and only allow those columns that are indexed in the WHERE clause.
0
 
jkurantAuthor Commented:
@Anthony Perkins: I don't think the user would like it if I didn't show them all the columns in the table.
0
 
Anthony PerkinsCommented:
I don't think the user would like it if I didn't show them all the columns in the table.
Fair enough.  Of course, then you will have to explain to them about performance and how they will need to go for frequent coffee breaks while the query is running.
0
 
mccarlIT Business Systems Analyst / Software DeveloperCommented:
@jkurant,

I just wanted to double check one thing, I'm not 100% that you understood Anthony's point...

@Anthony Perkins: I don't think the user would like it if I didn't show them all the columns in the table.

I don't think he means to not show the data/content for all columns, just that you restrict the possible columns that can be used in the WHERE clause to only those that have indexes. You can still show the data for ALL columns though!
0
 
jkurantAuthor Commented:
So the answer is: there is no way to speed up the processing given all of my constraints.

Since I must close out this question before I can ask another, I have given everyone who replied some credit.
0
 
Anthony PerkinsCommented:
I have given everyone who replied some credit.
And that very generous of you, I cannot tell you what a difference it makes  to my four small kids to know they are going to eat this weelk.
0
 
jkurantAuthor Commented:
Were you being sarcastic, Anthony Perkins? I didn't know what else to do. No one really addressed my question which was more about saving state between sessions and doing this in Java than it was about writing a faster query.
0
 
Anthony PerkinsCommented:
I didn't know what else to do.
It is all covered in the EE Guidelines (What grade should I award?).  
Here is the relevant part:
C should only be given for an incomplete solution that does not fully address or answer the question. A C grade should be awarded only after the asker has replied to all expert comments, provided all requested information, tried all suggested solutions, given the experts ample time to reply, and received clarification about the answer given. The asker must justify giving a C grade and give the experts an opportunity to improve it.  If the answer provided is incomplete or unsatisfactory enough to get a C grade, the asker should consider deleting the question entirely.
0
 
jkurantAuthor Commented:
Oh, I was wondering what you were getting at. I did not understand the meaning of the grade. I thought the grade I assigned might guide future users of Expert's Exchange in telling them how relevant the answers might be to the question I asked. But apparently it has more to do with giving credit to the people who responded with useful solutions. Well, the problem is that no one really did that.

My question is mainly about not having to run the long-running query over the whole table again, once I have already run it once and am just getting more records. It was about breaking the results into chunks and not re-doing all of the database work each time the user goes to another page. Also, you ignored my constraints. I cannot tell the user he can only filter (search) on fields that are indexed, for instance.

So, the user wants to see the first 10 records of a table with a million rows. I still have to SELECT * FROM TABLE and show just the first 10, because SELECT TOP 10 is even slower! When the user wants rows 10-20 I have to run the entire query SELECT * FROM TABLE again, skip over 10 rows and display the next 10. I was hoping there was some way to avoid doing this, that I could somehow save a database resultset object between page refreshes and use it for the next 10 records.

The last two sentences of my question are:

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?

No one really addressed that.

Now, this begs the question, what happens when there is no answer? What grade do I give if I ask a question to which there is no answer, or the answer is that it cannot be done? I think if someone tried to address the issue of saving the resultset or some other means of avoiding the big select, then I would give a better grade, even if the ultimate answer is that you cannot save a resultset between page refreshes.
0
 
Anthony PerkinsCommented:
Now, this begs the question, what happens when there is no answer?
That is all covered in the help as in What if my question does not receive an answer?

or the answer is that it cannot be done?
Again, covered in the help as in The experts told me “you can not do that”. What do I do now?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.