• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 600
  • Last Modified:

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
0
barnarp
Asked:
barnarp
1 Solution
 
Venci75Commented:
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.
0
 
barnarpAuthor Commented:
yes,

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

Any idea?

0
 
rjackmanCommented:
which JDBC Driver r u using?
Arrays are faster than Vector coz vector takes time to reallocate when it is full
cheers
RJ
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
barnarpAuthor Commented:
yes,

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

Any idea?

0
 
barnarpAuthor Commented:
using classes12 oracle 805 client, oracle 806 server.
0
 
pepenietoCommented:
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.


0
 
barnarpAuthor Commented:
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
0
 
Venci75Commented:
try something like that:


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

public class MyThread extends Thread {
  private Vector data = new Vector(20000);
  private boolean loaded = false;
  private String sql;
  private int rowsProcessed = 0;

  private Thread caller = null;
  private int callerInt;

  public MyThread(String sql) {
      this.sql = sql;
  }

  public Vector getData() {
      return data;
  }
  // use this method for getting the vector
  public synchronized Vector getData(int start, int count) {
      int maxNum = start + count;
      if (maxNum > rowsProcessed) {
          caller = Thread.currentThread();
          caller.wait();
          callerInt = maxNum;
      } else return data;
  }
  public boolean isLoaded() {
      return loaded;
  }
  public synchronized void addVectorRow(ResultSet rs) {
      rowsProcessed++;
      Object obj = "row# " + rowsProcessed;
      // .. creating the object
      data.add(obj);
      if (callerInt > 0 && callerInt < rowsProcessed) {
          callerInt = 0;
          caller.notify();
          caller = null;
      }
  }
  public void run() {
      // .... construct the result set
      ResultSet rs;
      while (rs.next()) {
          addVectorRow(rs);
      }
  }
}


to use this class:

MyThread loader = new MyThread("select ....");
loader.start();
Vector data = loader.getData(0, 20);

0
 
sakanCommented:
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.

0
 
barnarpAuthor Commented:
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

0
 
barnarpAuthor Commented:
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  
0
 
Venci75Commented:
Yes - you are right - this is exactly what you should do
0
 
barnarpAuthor Commented:
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?


0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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