Link to home
Start Free TrialLog in
Avatar of barnarp
barnarp

asked on

JSP - Database Query and Vector

hi,

I am running a database query which returns 7000 records with 4 fields each.

The flow is as follows:

Page 1:

Click on link to Page 2

Page 2:

Accesses bean which runs query and stores records in vector.
Vector is passed back to Page 2.
First 20 element of vector is displayed.

The scenario is a back/forward  through records and display 20 results at a time.

My problem is that getting the results into the vector takes very long (13-15 secs).
So the initial 20 results seem to take forever to load, thereafter everything is fast because it only reads the vector.

Is there a faster way to do this ? As far as I know, a vector is the quickest.

Any help to get the execution time down will be appreciated.

Regards

Pierre
Avatar of Venci75
Venci75

You can use a different thread to store the results in the Vector. When this thread is ready with the needed record (for the first page - the first 20) - you can start displaying the results.
Avatar of barnarp

ASKER

yes,

Have thought of that, but have no idea how to handle threads in jsp!

Any idea?

which JDBC Driver r u using?
Arrays are faster than Vector coz vector takes time to reallocate when it is full
cheers
RJ
Avatar of barnarp

ASKER

yes,

Have thought of that, but have no idea how to handle threads in jsp!

Any idea?

Avatar of barnarp

ASKER

using classes12 oracle 805 client, oracle 806 server.
barnarp,

Vectors are synchronized, so is much better in a multi-threaded system to use ArrayList.

If you´re using oracle, you can retrieve the first 20 records using:

select * from <table> where rownum<20

But this is only usefull when you only want the 20 first records.


Avatar of barnarp

ASKER

hi,

I am trying to build the following array without success:

List rows = new Vector();
Statement qry = null;
ResultSet rs = null;
ResultSetMetaData rsmeta = null;

 String querystr = "select...";
 
 qry = conn.createStatement();
 rs = qry.executeQuery (querystr);
 rsmeta = rs.getMetaData();


if (rs.next())
{
int rowcount = rsmeta.getColumnCount();
     do {
          List row = new Vector();

for (int i = 1; i <= rowcount ; i++) {
row.put(rs.getObject(i));

}
rows.put(row);
       } while (rs.next());
       
}

I get a compiler error about the ".put" method.

I dont know which method can be used with a list object.

Please help
ASKER CERTIFIED SOLUTION
Avatar of Venci75
Venci75

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,
I have addition from pepenieto's comment. I use the way that query only the records we're going to use. Oracle has "minus" to minus recordset like this...

select * from <table> where rownum<101
minus
select * from <table> where rownum<81

this query will return record number 81-100

So, you can retrieve any records that u want.

Avatar of barnarp

ASKER

Thanks everyone for your input.

All your comments really helped me alot.

I think under the circumstances, a seperate thread will work best, due to slow performance of our databases.

I have changed my vector to a array in the meantime, and could already see a performance increase.

Regards

Pierre

Avatar of barnarp

ASKER

Venci75,

I am sending a DB Connection to the thread class as well.

Where do I execute the query?

In this block?

public MyThread(String sql,Connection conn) {
     this.sql = sql;
--> qry = conn.createStatement();
--> qryrs = qry.executeQuery (sql);
--> qryrsmeta = qryrs.getMetaData();
 }

Can't I add this code:

if (qryrs.next())
{
int rowcount = qryrsmeta.getColumnCount();
     do {
          row = new ArrayList();
 
for (int i = 1; i <= rowcount ; i++) {
row.add(qryrs.getString(i));
}

Rows.add(row);
       } while (qryrs.next());
}

to this block:

public void run() {
     // .... construct the result set
     ResultSet rs;
     while (rs.next()) {
         addArrayList(rs);
     }

and leave out this block:

public synchronized void addArrayListRow(ResultSet rs)

Please help me construct this thread. I can even start a new question thread and allocate you the points.

Regards

Pierre  
Yes - you are right - this is exactly what you should do
Avatar of barnarp

ASKER

thanks,

My thread class now looks like this:

import java.util.*;
import java.sql.*;

public class MyThread extends Thread {
 private ArrayList Rows ;
 private ArrayList Row  ;
 private String sql;
 public Connection connect;


 public MyThread(String sql,Connection conn) {
     this.sql = sql;
     this.connect = conn;
 }

public void run()
{
 
 try
 {execSQL();
}
catch(Exception e)
{
     }

 
       
}    
     
public void execSQL() throws ClassNotFoundException,SQLException,Exception
{
 Statement qrygetlabor = null;
 ResultSet qrygetlaborrs = null;
 ResultSetMetaData qrygetlaborrsmeta = null;

 qrygetlabor = connect.createStatement();
 qrygetlaborrs = qrygetlabor.executeQuery (sql);
 qrygetlaborrsmeta = qrygetlaborrs.getMetaData();


 if (qrygetlaborrs.next())
 {
 
 
    int rowcount = qrygetlaborrsmeta.getColumnCount();
     do {
          Row = new ArrayList();

 for (int i = 1; i <= rowcount ; i++)
    {
 Row.add(qrygetlaborrs.getString(i));
    }
 Rows.add(Row);}
          while (qrygetlaborrs.next());
}
}
 
 
 
 public ArrayList getData()
  {
     return Rows;
  }
}


As you can see, i simplified it to return the whole arraylist, and hioe it will work.

I can however not get to test it because of a compile error:

org.apache.jasper.JasperException: Unable to compile class for JSP

Generated servlet error:
E:\tomcat\work\localhost\_\Issues$jsp.java:331: Class org.apache.jsp.MyThread not found.

in

MyThread loader = new MyThread("select laborcode,name from labor order by name",conn);
               
??

I have copied MyThread.class in every tomcat/JRE and JDK directories!

classpath issue?