[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

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.
0
kingasa
Asked:
kingasa
  • 3
  • 2
1 Solution
 
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
sameerjoshiCommented:
Good  answer Jod
0
 
JodCommented:
Thanks, sameerjoshi.
0
 
JodCommented:
Cest la vie...
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now