Returning only limited data from database.

I am using java as programming language and PostgreSQL as my database.

Is it possible to request the database to return only a certain number of records (100 only) based on a Select Query ?


In other words, consider this example :
SELECT name, age, depname from EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.dept_id = DEPARTMENT.id;

This returns 1500 records.

Is there a way to tell the RDBMS to return only first 100 records ? Also, can i ask it to return next 100 records etc ?

The reason i need this feature is that some of my SQL Queries might return 500,000 records. I wonder if the resultset object in java will be able to handle that much data !

(One thought : What is the behaviour in LDAP. Are such features supported there.)
LVL 1
JituAsked:
Who is Participating?
 
kelfinkConnect With a Mentor Commented:
I don't know much about performance impacts, but I would assume that the database does have to go through most of the work of processing rows 1..19, even if you just want to get back 20-29.

All the databases I use have some form of limit. Unfortunately they all have different implementations.

On MS Sql Server, the equivalent is : SELECT TOP N ...
On Oracle, you have to use : ...WHERE ROWNUM <= N
On mySql, you use LIMIT, but it comes at the end of the statement.  Unfortunately, ANSI never defined a routine way to pick and choose portions of the result set.
0
 
kelfinkCommented:
Sure.

SELECT * FROM mytable  ORDER BY mytable.id
LIMIT 10 OFFSET 20;

The OFFSET is optional, and can be used to select a portion of the data set after the first row.  THe example selects the 20th through 29th rows.
0
 
JituAuthor Commented:
Kelfink that was excellent :)

Do you know which databases support LIMIT/OFFSET feature in SQL. ?

What i am trying is to implement paging for a user on a report, the data for which comes from the database. Right not i am using postgres database, but in future we may use other databases.

Are there any severe performance implications to using this.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
nico5038Commented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - Answered by: kelfink  
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
JituAuthor Commented:
Thanks. Sorry for the delay in getting back.
0
 
nico5038Commented:
Thanks for finalizing the Q ;-)

Nic;o)
0
 
partner0Commented:
In various RDBMS, you only have the TOP or equivalent (SQL Server) or none (Oracle), making me think that all the DB that implement a mechanism for selecting (n to n+x) like mySQL does select TOP n+x and then discard TOP n... Which is worth even that selecting TOP n+x from a performance standpoint...

In fact, how could RDBMS reach result n+1 without reaching result n first, unless adding a search condition?

The most honest are Oracle where you explicitly add search conditions to do that... No magic from a performance standpoint...

Maybe, if you have ressource consuming search conditions, a way to enhance perf is to perform paging on client side, by passing a list of all matching ID to the client at the begining, then retreiving information by subset of IDs, saving the load of re-compute all search contidions each time you page...
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.