MySQL JDBC Performance Issues
Hi, First off, let me admit, im a java newbie. I've been using java for about 6 months now, although im a fast learner, some things still elude me.
I've searched these forums and google high and low for an answer to my question before signing up and posting here, so i'm kind of at my wits end.
Let me explain my specs so noone blames lack of power for this issue.
System specs:
Linux Cent OS 4 2.6.19 SMP
JRE 1.6
MySQL 4.2.2 ICC Compiled Enterprise Edition
4x HyperThreaded Xeon 1.9Ghz cpus (Displays as 8)
16GB ECC Double-Parity Double-Buffered Memory. (Rediculous IBM proprietary memory)
Problem:
I first wrote this application in PHP as it my stong suit (that should tell you plenty). But the PHP CLI was eating up way to much CPU power/MySQL bandwith to execute this script, and taking way to long, so i started re-writing it in C++, well when my feeble C++ powers failed me, i went to Java. I know more Java then C++ but i picked C first because i thought it would be the fastest. Java, however, is slower then PHP when i write the identical logic, i dont know if i should blame Java or my assumption that i can copy logic from one language to another.
Here is the details:
I have 1 MySQL database and 2 Tables im pulling from, This mysql database is optimized by the people from MySQL, so i know its not the DB, Server, or Tables. 1 table (bfpax) has around 45k rows (45000), the second table (bfbdet) has around 100k rows (100000). What this program has to do, its objective, is:
1. Pull the indexed ID from table 1;
2. With that ID, pull other data from table 2 and table 1 for each ID;
3. Compile that data together for each ID
4. Insert compiled data into a HEAP table for fast access for each ID.
In PHP (pseudocode):
SELECT ID FROM bfpax;
Loop:
Foreach id pull various data pieces i.e. name, client code, address, zip, etc.
String format name: "A-GARY/COLE" becomes "Gary/Cole"
String format hotel: "ACUNMIP" becomes CUNMIP
Insert all formatted/fixed data into heap table
End loop.
In Java (pseudocode):
SELECT ID FROM cpax;
Loop:
Create object with ID as constructor variable (see below for object declaration) and add to an arrayList;
End Loop;
Create 6 threads to execute each of the objects methods for data collection/formatting
Start each thread using inner classes to loop through the object arraylist and execute dedicated methods per thread.
Thread 1: Pull air data
Thread 2: Pull destination
Thread 3: Pull hotel
Thread 4: Pull gateway
Thread 5: Pull the rest (price, dates, etc)
Thread 6: Start executing the update method
The above kind of failed, so i reduced the complexity by nuking the whole thread idea and now i just have a simple function that loops through the arraylist of objects and executes each method in order.
Booking Object:
publicclass Bookingextends ReportingConstants{
private ArrayList<String> myData =new ArrayList<String>();
privateint myBookNum;
privatestatic Connection myCon;
publicstaticint numBookings;
publicint updateCount = 0;
publicboolean isFinished =false;
public Booking(int booking_number, Connection conn){
if (booking_number > 9999){
myBookNum = booking_number;
}
numBookings++;
myCon = conn;
//Run loop to make sure myData has the proper spaces for insert, ensureCapacity didnt work.
for (int i = 0; i < 15; i++){
myData.add("");
}
myData.set(_BOOKNUM,String.valueOf(myBookNum));
if (Integer.valueOf(_ARGS[_DEBUG][1]) > 0){
System.out.println("Initialized booking number "+myBookNum+" count "+numBookings);
}
}
publicvoid getAir(){
ResultSet res;
ArrayList<String> total_air =new ArrayList<String>();
String airlines ="";
String query ="SELECT operator_id FROM EDITED.bfbdet WHERE record_type = 'A' AND item_desc LIKE '-%' AND booking_number = "+myBookNum+" AND operator_id IS NOT NULL";
if (Integer.valueOf(_ARGS[_DEBUG][1]) > 1){
System.out.println("Starting Air: "+query);
}
res = MySQL.sqlQuery(query,myCon,5);
try{
while(res.next()){
if (res.getString(1).length() > 1){
String id = res.getString(1).substring(0,2);
if (!total_air.contains(id)){
airlines += id+",";
}
total_air.add(id);
}
}
res.close();
MySQL.close();
}catch (Exception ex){
ex.printStackTrace();
}
myData.set(_AIR,airlines);
updateCount++;
}
publicvoid getDest(){
String query ="SELECT booking_code FROM EDITED.bfpax WHERE booking_number = "+myBookNum+" LIMIT 1";
ResultSet res;
String isSv ="Y";
String dest;
if (Integer.valueOf(_ARGS[_DEBUG][1]) > 1){
System.out.println("Starting Dest: "+query);
}
res = MySQL.sqlQuery(query,myCon,1);
try{
while (res.next()){
dest = res.getString(1).substring(1,4);
if (dest !="FRE"){
if (dest =="GYY"){
String realDest = res.getString(1).substring(4,2);
if (realDest =="GY"){
dest ="GYY";
}elseif (realDest =="MC"){
dest ="MCO";
}elseif (realDest =="FL"){
dest ="FLL";
}elseif (realDest =="IW"){
dest ="IWA";
}elseif (realDest =="PI"){
dest ="PIE";
}elseif (realDest =="LA"){
dest ="LAS";
}else{
dest ="GYY";
}
}else{
isSv ="N";
}
myData.set(_DEST,dest);
myData.set(_SV,isSv);
updateCount++;
}
}
res.close();
MySQL.close();
}catch (Exception ex){
ex.printStackTrace();
}
}
publicvoid getGateway(){
String query ="SELECT item_desc FROM EDITED.bfbdet WHERE (booking_number = '"+myBookNum+"' OR booking_number = ' "+myBookNum+"') AND item_desc LIKE '-%' ORDER BY booking_suffix ASC LIMIT 1";
if (Integer.valueOf(_ARGS[_DEBUG][1]) > 1){
System.out.println("Starting GW: "+query);
}
ResultSet res = MySQL.sqlQuery(query,myCon,1);
String gw;
try{
while (res.next()){
gw = res.getString(1).substring(1,3);
myData.set(_GW,gw);
}
updateCount++;
res.close();
MySQL.close();
}catch (Exception ex){
ex.printStackTrace();
}
}
publicvoid getHotel(){
String query ="SELECT operator_id FROM EDITED.bfbdet WHERE record_type = 'H' AND (booking_number = '"+myBookNum+"' OR booking_number = ' "+myBookNum+"') LIMIT 1";
if (Integer.valueOf(_ARGS[_DEBUG][1]) > 1){
System.out.println("Starting Hotel: "+query);
}
ResultSet res = MySQL.sqlQuery(query,myCon,1);
String hotel ="";
try{
while (res.next()){
hotel = res.getString(1).substring(0,6);
if (myData.get(_DEST) ==""){
myData.set(_DEST,res.getString(1).substring(0,3));
updateCount++;
}
}
myData.set(_HOTEL,hotel);
updateCount++;
res.close();
MySQL.close();
}catch (Exception ex){
ex.printStackTrace();
}
}
publicvoid getRest(){
String query ="SELECT client_code, passenger1_name, agentid, booked_date, dep_date, total_price, total_received, total_commission, number_pax FROM EDITED.bfpax WHERE booking_number = "+myBookNum+" LIMIT 1";
if (Integer.valueOf(_ARGS[_DEBUG][1]) > 1){
System.out.println("Starting Rest: "+query);
}
ResultSet res = MySQL.sqlQuery(query,myCon,1);
try{
while (res.next()){
myData.set(_AGENCY,res.getString(1));
if (res.getString(3) !="null"){
myData.set(_AGENT,res.getString(3));
}else{
myData.set(_AGENT,"");
}
myData.set(_PAXNAME,res.getString(2).replace("'",""));
myData.set(_BKDATE,String.valueOf(res.getDate(4)));
myData.set(_DEPDATE,String.valueOf(res.getDate(5)));
myData.set(_TPRICE,String.valueOf(res.getDouble(6)));
myData.set(_TRECV,String.valueOf(res.getDouble(7)));
myData.set(_TCOM,String.valueOf(res.getDouble(8)));
myData.set(_NUMPAX,String.valueOf(res.getInt(9)).trim());
//System.out.println("NUMPAX: |"+myData.get(_NUMPAX)+"|");
}
updateCount++;
res.close();
MySQL.close();
}catch(Exception ex){
ex.printStackTrace();
}
}
publicvoid storeData(){
if (!isFinished){
String query ="INSERT INTO "+tmpTable+" (`booking_number`, `destination`, `gateway`, `airline`, `hotel`, `agency`, `agent`, `booked_date`, `dep_date`, `total_price`, `total_received`, `total_commission`, `number_pax`, `passenger_name`, `is_skyvalue`) VALUES('"+myData.get(0)+"','"+myData.get(1)+"','"+myData.get(2)+"','"+myData.get(3)+"','"+myData.get(4)+"','"+myData.get(5)+"','"+myData.get(6)+"','"+myData.get(7)+"','"+myData.get(8)+"','"+myData.get(9)+"','"+myData.get(10)+"','"+myData.get(11)+"','"+myData.get(12)+"','"+myData.get(13)+"','"+myData.get(14)+"')";
if (Integer.valueOf(_ARGS[_DEBUG][1]) > 1){
System.out.println(query);
}
MySQL.sqlExec(query,myCon);
isFinished =true;
if (Integer.valueOf(_ARGS[_DEBUG][1]) > 0){
System.out.println("Booking number "+myBookNum+" is finished!");
}
}else{
//System.out.println("Not fully populated!");
}
}
}
Please dont laugh too hard heh, i know my code is sloppy and probably not optimized at all, i dont pretend to be a hardcore java guy, but i would love to learn. Im also posting below my static MySQL class so you can see what happens when i call sqlQuery();
publicabstractclass MySQL{
privatestatic ResultSet res;
privatestatic Statement stmt;
publicstatic ResultSet sqlQuery(String query,Connection con,int limit){
try{
stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(limit);
res = stmt.executeQuery(query);
}catch(Exception ex){
ex.printStackTrace();
res =null;
System.out.println("Cant Query!: "+query);
}
return res;
}
publicstaticvoid sqlExec(String query,Connection con){
Statement stmt;
try{
stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
stmt.executeUpdate(query);
stmt.close();
}catch(Exception ex){
ex.printStackTrace();
System.out.println("Cant Query!: "+query);
}
}
publicstaticvoid close(){
try{
stmt.close();
res.close();
}catch (Exception ex){
ex.printStackTrace();
}
}
}
If you notice, i require the connection to be passed when executing a query, i do this because even if i made a static connection state in the MySQL class, for some reason, it still gets duplicated every time its called. This way, i create 1 connection with the calling static main class, and pass that same conn back and forth through the whole script. I know in PHP this is the best way to do it, in Java it may require many different connections.
By the way, this is an update that runs once per hour, it does not have to worry about users or anything like that, it gets executed by cron on the hour.
PHP does this job of updating about 40000 entries in 9-12 minutes. for an average of 0.0135 seconds per entry, Java rolls around at 0.06 seconds per query, 6x slower, this puts execution time at about 40 minutes, obviously this is an issue if its supposed to run every hour. The java app does not tax the system, on top/htop it does not even make it to the first page so its not resources, ive looked at the verbose gc info, and i didnt see anything to clue me in there either.
I'm sure this is a simple error and my lack of knowledge with Java is what's holding me back, but if i cant figure this out ill have to go back to struggling with C++.
Thanks in advance, and im sorry for the horribly long post.
Thanks,
Dave
[20762 byte] By [
Zetasa] at [2007-11-26 19:03:37]
# 1
I didnt want to include this in the original post, so here is where my main is located:
public class ReportingUpdate extends ReportingConstants {
private ArrayList bookings;
private static String[] argList;
private static ArrayList<String> provArg = new ArrayList<String>();
public ArrayList notDone = new ArrayList();
private static Connection conn;
public int bookingsUpdated;
public static boolean isUpdating = false;
private static int _ERRORS = 0;
public static ArrayList doneBookings = new ArrayList();
public static void main(String[] args) {
if (args.length > 0) {
if (args[0].contains("-help")) {
printHelp();
} else {
setupArgs();
argList = args;
parseArgs();
}
}
if (_ERRORS == 0) {
new ReportingUpdate().go();
} else {
return;
}
}
private static void parseArgs() {
for (String s : argList) {
if (s.indexOf("-") != 0) {
printHelp();
break;
}
if (s.length() > 2) {
provArg.add(s.substring(1,2));
} else {
printHelp();
break;
}
}
for (int i = 0; i < _ARGS.length; i++) {
if (provArg.contains(_ARGS[i][0])) {
int pos = provArg.indexOf(_ARGS[i][0]);
ReportingConstants._ARGS[i][1] = argList[pos].substring(2);
}
}
//Commented out argument testing loop.
/*for (String[] s : _ARGS) {
System.out.println("Arg: "+s[0]+" Value: "+s[1]);
}
_ERRORS++;*/
}
public static void printHelp() {
System.out.println("\nReporting Update 1.1 (2007 February 1, compiled Feb 19 2007 11:12:24)");
System.out.println("");
System.out.println("Usage: java -jar /path/to/ReportingUpdate.jar [arguments]");
System.out.println("");
System.out.println("Arguments:");
System.out.println(" -d<level>\tDebug Level: 0-No Output(Default), 1-Start and finish output, 2-Functional Output(Lots of output)");
System.out.println(" -l<limit>\tBooking Limit. How many bookings will be updated, only use this for testing.");
System.out.println(" -a<0,1> \tBool choice to run the air update function, speed testing option. (0-0ff,1-On)\n\t\tIf this is not set, on is assumed");
System.out.println(" -e<0,1> \tSame as above, but for destination.");
System.out.println(" -g<0,1> \tSame as above, but for gateway.");
System.out.println(" -h<0,1> \tSame as above, but for hotel.");
System.out.println(" -r<0,1> \tSame as above, but for agency,agent,price & dates.");
System.out.println(" -u<0,1> \tSame as above, but for each bookings actual insert into the db.");
System.out.println("");
System.out.println("Example: java -jar /path/to/ReportingUpdate.jar -d1 -l500 -a0 -d0 -u0");
System.out.println("Run the reportingupdate with debug level 1, limit 500 bookings and do not run the air, destination or insert functions.\n");
_ERRORS++;
}
public void go() {
String limitString;
if (Integer.valueOf(_ARGS[_LIMIT][1]) > 0) {
limitString = "LIMIT "+_ARGS[_LIMIT][1];
} else {
limitString = "";
}
bookings = new ArrayList();
ResultSet res;
connect();
res = MySQL.sqlQuery("SELECT booking_number FROM EDITED.bfpax WHERE booking_number IS NOT NULL "+limitString,conn,Integer.valueOf(_ARGS[_LIMIT][1]));
try {
int i = 0;
while (res.next()) {
String booking = res.getString("booking_number");
booking = booking.trim();
if (booking.length() > 1) {
//System.out.println("Reading booking "+booking);
bookings.add(new Booking(Integer.valueOf(booking),conn));
}
i = 1;
}
startTheFire();
} catch(Exception ex) {
ex.printStackTrace();
}
}
private void startTheFire() {
/*Thread a = new Thread(new DoAir());
Thread b = new Thread(new DoDest());
Thread c = new Thread(new DoGW());
Thread d = new Thread(new DoHotel());
Thread e = new Thread(new DoRest());
Thread f = new Thread(new DoUpdate());
a.setName("Air");
a.start();
b.setName("Dest");
b.start();
c.setName("GW");
c.start();
d.setName("Hotel");
d.start();
e.setName("Rest");
e.start();
f.setName("Update");
f.start();
try {
f.sleep(20000);
} catch (Exception ex) {
ex.printStackTrace();
}*/
Iterator it = bookings.iterator();
while (it.hasNext()) {
Booking b = (Booking) it.next();
if (Integer.valueOf(_ARGS[_AIRARG][1]) > 0) {
b.getAir();
}
if (Integer.valueOf(_ARGS[_DESTARG][1]) > 0) {
b.getDest();
}
if (Integer.valueOf(_ARGS[_GWARG][1]) > 0) {
b.getGateway();
}
if (Integer.valueOf(_ARGS[_HOTELARG][1]) > 0) {
b.getHotel();
}
if (Integer.valueOf(_ARGS[_RESTARG][1]) > 0) {
b.getRest();
}
if (Integer.valueOf(_ARGS[_UPDATEARG][1]) > 0) {
b.storeData();
}
}
}
class DoAir implements Runnable {
public void run() {
Iterator it = bookings.iterator();
while(it.hasNext()) {
Booking tempBooking = (Booking) it.next();
tempBooking.getAir();
}
if (Integer.valueOf(_ARGS[_DEBUG][1]) > 1) {
System.out.println("Air is done!");
}
}
}
class DoDest implements Runnable {
public void run() {
Iterator it = bookings.iterator();
while(it.hasNext()) {
Booking tempBooking = (Booking) it.next();
tempBooking.getDest();
}
if (Integer.valueOf(_ARGS[_DEBUG][1]) > 1) {
System.out.println("Dest is done!");
}
}
}
class DoGW implements Runnable {
public void run() {
Iterator it = bookings.iterator();
while(it.hasNext()) {
Booking tempBooking = (Booking) it.next();
tempBooking.getGateway();
}
if (Integer.valueOf(_ARGS[_DEBUG][1]) > 1) {
System.out.println("Gateway is done!");
}
}
}
class DoHotel implements Runnable {
public void run() {
Iterator it = bookings.iterator();
while(it.hasNext()) {
Booking tempBooking = (Booking) it.next();
tempBooking.getHotel();
}
if (Integer.valueOf(_ARGS[_DEBUG][1]) > 1) {
System.out.println("Hotel is done!");
}
}
}
class DoRest implements Runnable {
public void run() {
Iterator it = bookings.iterator();
while(it.hasNext()) {
Booking tempBooking = (Booking) it.next();
tempBooking.getRest();
}
if (Integer.valueOf(_ARGS[_DEBUG][1]) > 1) {
System.out.println("The Rest is done!");
}
}
}
class DoUpdate implements Runnable {
public void run() {
while (bookingsUpdated <= Booking.numBookings) {
if (doneBookings.size() > 0) {
isUpdating = true;
Iterator it = doneBookings.iterator();
while (it.hasNext()) {
Booking b = (Booking) it.next();
b.storeData();
bookingsUpdated++;
it.remove();
}
isUpdating = false;
}
}
if (Integer.valueOf(_ARGS[_DEBUG][1]) > 1) {
System.out.println("Update is done!");
}
}
}
private void connect() {
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(_URL,_USER,_PASS);
} catch(Exception ex) {
System.out.println("Cannot Connect to: "+_URL);
ex.printStackTrace();
}
createTempTable();
}
private void createTempTable() {
String create_query = "CREATE TABLE IF NOT EXISTS `"+tmpTable+"` (`ID` INT( 5 ) NOT NULL AUTO_INCREMENT ,`booking_number` INT( 6 ) NULL ,`destination` CHAR( 3 ) NULL ,`gateway` CHAR( 3 ) NULL ,`airline` VARCHAR( 15 ) NULL ,`hotel` CHAR( 6 ) NULL ,`agency` VARCHAR( 15 ) NULL ,`agent` VARCHAR( 20 ) NULL ,`booked_date` VARCHAR( 10 ) NULL ,`dep_date` VARCHAR( 10 ) NULL ,`total_price` VARCHAR( 10 ) NULL ,`total_received` VARCHAR( 10 ) NULL ,`total_commission` VARCHAR( 10 ) NULL ,`number_pax` INT( 5 ) NULL ,`passenger_name` VARCHAR( 50 ) NULL,`is_skyvalue` CHAR( 1 ) NULL,PRIMARY KEY ( `ID` ),INDEX ( `booking_number` ),INDEX ( `agency` ) ) TYPE = memory;";
String trunc = "TRUNCATE TABLE `"+tmpTable+"`";
MySQL.sqlExec(create_query,conn);
MySQL.sqlExec(trunc,conn);
}
}
# 2
On a pretty quick look here a a few things I observe:
1. You have code like this:
AND (booking_number = '"+myBookNum+"' OR booking_number = ' "+myBookNum+"') LIMIT 1";
This appears redundant. I did not look too closely at all the statements, but perhaps you have multiple cases of such?
2. You are not using PreparedStatements where you can - and this shouldl speed things up.
3. You are hitting the same table multiple times per itteration, correct? ... can you knock this down to one hit per table per ID?
4. I did not go through the code carefully enough to ascertain for sure, but I would be skepticle of the way you are closing your Statements, ResultSets and the like ... are you sure you are not getting them out of sync ... probably better to re-organize to be sure you close exactly when and where you really need to.
5. I noticed that you are calling an DB insert a "query". Now I realize this is just naming ... but It's confusing in the least and could also be leading to errors.
HTH
~Bill
# 3
Thank you for your comments, i really appreciate it.
Ive already corrected your number 1, that single instance was just missed by accident, the previous developer who wrote the code to insert into that database included a space before the booking number in some bookings, so that is why at first i was looking for it with and without. But i learned you can just drop the quotes and spaces dont matter with int's.
Your number 2 i will try, many people said to do that the previous posts i read, i only didnt because the member who had the problem initially almost always replied and said preparedstatments didnt help.
The reason i hit the table multiple times is because each query is slightly different, there are a couple that i could compact, but the majority of them will have to stay seperate unless i go to some very complex SQL which i think might slow it down even more. My goal was, at least, many fast/easy/quick queries as apposed to a few fat slow queries. It does seem that maybe Java does not like many queries but fewer.
I suppose i will have to overhaul the code, its very messy, so so many different ideas have come and gone ive got unused variables, misnamed ones, unlinked methods, etc. I really just want to get it working first, but maybe doing some house cleaning would help.
Thanks again for your suggestions,
Dave
# 4
You are welcome. Each time you go against the DB you incurr a performance penalty, no matter what language "wrapper" you use. On the other hand, big ugly SQL will also slow you down - BIG TIME!
So, the best thing to do probably is to hone the SQL stand-alone, then add it into the Java code as PreparedStatements where you can. PPs are not only usually a bit faster because they are "prepared" only once between closings (IOW - you don't close them after each use like you do Statements), but they are safer too.
So to recap:
1. Streamline the SQL first by themselves.
2. Add them to the Java as PPs.
~Bill
# 5
Ok, i tried everything you suggested. It's actually quite a bit slower now with the prepared statements, also i pushed 2 of the query loops together. With my debug settings i can switch each function on or off, and i've gone down the line turning one off at a time, none of them off seem to make it go any faster.
This is very disappointing.
Maybe i should try straight-up procedural programming instead of creating objects... i really didnt want to do that, but maybe that is the answer.
Also, is there a special JRE i should be using to take advantage of for an MP system? Im just using the basic JRE 1.6 from sun's website and JDK/SDK 1.6 with NetBeans.
I'm starting to feel like im using Java in an unintended way...
Thanks,
Dave
# 6
Why are you using scrollable result sets when you only process them in a forward-only way?
I'm not complaining about the forward-only-only processing, I think that is exactly what you should do. But there's no point in telling the driver that you might want to scroll back and re-read records you have already processed, when you don't want that. Using forward-only result sets might improve things a little.
# 7
When you created those PreparedStatements did you just create them once, in your initialization, and then reuse them 10,000 times or whatever your test data was?
# 8
At first i was creating a new prepared statement for each object (each of the 40000). Then i realized after you said that, i should probably create them once as static vars and use them over and over, is this correct? Once i did that and ran the script again i saw no performance increase, grrr...
This is the updated code from the main calling class (static class):
//--Prepared Statements
public static PreparedStatement airStmt;
public static PreparedStatement destStmt;
public static PreparedStatement gwStmt;
public static PreparedStatement restStmt;
public static PreparedStatement updateStmt;
//--
public static void main(String[] args) {
new ReportingUpdate().go();
}
public void go() {
connect();
try {
airStmt = conn.prepareStatement("SELECT operator_id, record_type FROM EDITED.bfbdet WHERE (record_type = ? OR record_type = ?) AND item_desc LIKE ? AND booking_number = ? AND operator_id IS NOT NULL");
destStmt = conn.prepareStatement("SELECT booking_code FROM EDITED.bfpax WHERE booking_number = ? LIMIT 1");
gwStmt = conn.prepareStatement("SELECT item_desc FROM EDITED.bfbdet WHERE booking_number = ? AND item_desc LIKE ? ORDER BY booking_suffix ASC LIMIT 1");
restStmt = conn.prepareStatement("SELECT client_code, passenger1_name, agentid, booked_date, dep_date, total_price, total_received, total_commission, number_pax FROM EDITED.bfpax WHERE booking_number = ? LIMIT 1");
updateStmt = conn.prepareStatement("INSERT INTO "+tmpTable+" (`booking_number`, `destination`, `gateway`, `airline`, `hotel`, `agency`, `agent`, `booked_date`, `dep_date`, `total_price`, `total_received`, `total_commission`, `number_pax`, `passenger_name`, `is_skyvalue`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
} catch (Exception ex) {
ex.printStackTrace();
}
...
Code snippet from one of the functions from the instantiated object (40k times):
try {
ReportingUpdate.airStmt.setString(1,"A");
ReportingUpdate.airStmt.setString(2,"H");
ReportingUpdate.airStmt.setString(3,"-%");
ReportingUpdate.airStmt.setInt(4,myBookNum);
res = ReportingUpdate.airStmt.executeQuery();
} catch (Exception ex) { ex.printStackTrace(); }
Thanks again for everyones help.
-Dave
# 9
As I said in post #4: PPs are not only usually a bit faster because they are "prepared" only once between closings (IOW - you don't close them after each use like you do Statements),
... and as DrClap re-emphasized, you do NOT close and re-open PPs after each itteration ... that defeats the entire purpose (or at least 90% of it) ... Read the Tutorial about this @[url http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html]Using Prepared Statements[/url]
Note, making them static is not the same thing as creating them once. Static means once per class - creating PPs once for the run, is not the same thing - hense, they need not be static.
~Bill
