Sql Query

[javaranjith]

Hi..

Can anyone clear my doubt?

I just want to show 10 records in each page when my query selects 100 records. How can we do?

[TJ111]

"SELECT * FROM table LIMIT 10"
"SELECT * FROM table LIMIT 10 OFFSET 10"

[bubbisthedog]

"SELECT * FROM table LIMIT 10"
"SELECT * FROM table LIMIT 10 OFFSET 10"
That's assuming they're using MySQL, which they didn't specify. :(

[chazzy]

Depending on how your system is setup, you might want to consider pulling up all 100 records up front and then only display what's needed, a temp cache almost.

[svidgen]

Sorry to question you on this, but I need to have this clarified (partially for my own knowledge):

Depending on how your system is setup, you might want to consider pulling up all 100 records up front and then only display what's needed, a temp cache almost.

Isn't it recommended not to do that for any evolved (generally any well-known) DBMS?

That reason I've been given is that any evolved DBMS should already have an optimized algorithm for limiting and skipping results, saving the requesting application or script the following efficiency hits:


additional memory required to complete the request

once within the DBMS while the results are queued
once within in the requesting application/script
additional transfer time
and much additional post-query processing time

the requesting application cannot take advantage of indexes



This even gives the programmer the benefit of the doubt and assumes that he or she has a well optimized method for limiting and skipping results in his or her chosen programming language, which is often not the case. Thus, it is highly recommended against retrieving more results than are necessary.

Though this is just what I've read (in several places) and come to understand. Am I mistaken? Am I just not thinking of a web scenario where it would be useful to have a complete cache of all rows?

[chazzy]

it really depends on your approach. Discussions are always encouraged, it's good for me to see other people's perspectives.

First issue has to do with connection pooling. Let's say that in one request you run the sql against connection #1. This returns the first 25 results (for example). Then the second request goes against connection #2. It knows nothing about this possibly cached data on the DB side since it's a different connection.

Most limiting factors are only really optimized for a single table - show me a report of all "foos" on the system, where foos are stored in the table named "foo." This will perform quite well on paginated support. However, the paginated support is typically database dependent, there is nothing in the ANSI standard for this, so everyone does it differently. At least in java, there's a continuing trend to move away from anything that is specific to any particular DBMS to make the applications more portable.

Finally, you really have to look at cached data results. If you want to, let's say, look at the results as of right now, but it's going to take you a few minutes to go through everything meanwhile there are other people on the system doing things that might impact your result set. You're not concerned with their input, only the input as of this very second. Even if the database is optimized to return top n results, this new data will get in and you will end up getting mixed results.

I've seen most of these issues in action, since our system did decide to use the db's cache support. I get calls/emails from users all day who are complaining because they see the wrong thing.

[svidgen]

Some of that sounds a little peculiar and sketchy to me ...

What DBMS are you running? And do you experience these issues with a typical web application or a load-once type of application like a Java application or applet?