sql

hello,

after excuting a SELECT command I'm getting back 1000 records out of 200,000. and it's take alot of time.

what can I do in order to get a smaller amount of records,
for example:
100 records out of the 1000 and then another  100
until I will reach 1000.
kingasaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

yowkeeCommented:
kingasa,
   If it's really slow, first check your select statement and whether those tables involved contained proper indexes. Besides, how you connect to database and retrieve records? Please provide more info. :)
0
sameerjoshiCommented:
Fetching first n number of rows from a table is easy but retriving next n rows can be difficult. Anyway to get you going, use the following query to fetch the first 'n' rows from your table.


select * from xyz where RowNum<n;


The above statement gives you only first 'n' rows but for next sets of 'n' rows you woiuld need to do bit more of research. If I get to know, I would surely inform you.



Enjoy.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JodCommented:
My answers are getting too long - by the time I finished answering two other people have also replied! Anyway, where was I....

There are a host of ways to approach this problem, as it is a common issue.

If you are using rownum then you need to be sure of two things:

  How do you get the next set of records?

  Has anyone changed the data while you are looking at it?

To get the next set of records and process records in blocks using rownum then do the following:

set up a JDBC prepared statement that accepts the min and max rownum figures as parameters

Something like:

   PreparedStatement pstmt =
     con.prepareStatement("Select * from mytable where rownum between ? and ?");

   pstmt.setInt(1, 1)
   pstmt.setInt(2, 100)
 

This will be resolved into:

Select * from mytable where rownum between 1 and 100

Then you can process the records returned in blocks by using:

   pstmt.setInt(1, 1)
   pstmt.setInt(2, 100)
   
to change the values of your minimum rownum and maximum rownum. So next time you do:

   pstmt.setInt(1, 100)
   pstmt.setInt(2, 200)

Which will be resolved into:

Select * from mytable where rownum between 100 and 200

and so on. Sticjk this in a loop and you have a basic format for processing records in blocks.

By the way, it is safer to overlap the recordsets by one, so that you can see the first record returned this time is the same as the last one returned last time - this way you can see that no one has added or removed a record that has affected your query. This can all get a bit complex with locking issues...

However, if you only want the first few records, then you can use:

  public void setMaxRows(int max) throws SQLException
 
  Sets the limit for the maximum number of rows
  that any ResultSet can contain to the given
  number.
  If the limit is exceeded, the excess rows
  are silently dropped.
 
(JDBC 2.0 supports setFetchSize which sounds promising...)  
 
Depending on how your records are organised you could just select only the records between certain values. For example, you could use maxrows to set the number of records you want and then keep track of where your query finished, then move the parameters to get the next block of records, still limited to maxrow number of rows of course.

If you want to really get down to it, you can use execute instead of executeQuery to return multiple result sets from a query. This works like this:

Executes a SQL statement that may return multiple results. Under some (uncommon) situations a single SQL statement may return multiple result sets and/or update counts. Normally you can ignore this unless you are (1) executing a stored procedure that you know may return multiple results or (2) you are dynamically executing an unknown SQL string. The methods execute, getMoreResults, getResultSet, and getUpdateCount let you navigate through multiple results. The execute method executes a SQL statement and indicates the form of the first result. You can then use getResultSet or getUpdateCount to retrieve the result, and getMoreResults to move to any subsequent result(s).

This can be used to get a stored database procedure to return your result sets in blocks, for example.

To use advanced cursor management, which is the real database solution to your problem then you will need to look more deeply into the facilities your database provides.

Let me know if you need more info...
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

sameerjoshiCommented:
Good  answer Jod
0
JodCommented:
Thanks, sameerjoshi.
0
JodCommented:
Cest la vie...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.