QUOTE(Trogdor @ 31 Jul, 2008 - 09:38 AM)
Depends, if there is a foreign key to be checked, the table that contains it better have an index.
But as i said, time the results, then you know where all that processing time is spent.
CODE
String query;
int day_id,period_id,course_id,venue_id,user_id=0;
int count=0;
conn=database.createconnection();
long cinsert=System.currentTimeMillis();
TimeTable.insertcf(conn);
System.out.println("THE AMOUNT OF TIME IT TOOK TO INSERT THE CF IS "+(System.currentTimeMillis()-cinsert)+"milliSECONDS");
cinsert=System.currentTimeMillis();
/***
try{
PreparedStatement prep2=conn.prepareStatement("insert LOW_PRIORITY into " +
"tt_data(day_id,period_id,venue_id,course_id,session,runtype,tt_type,userid) VALUES(?,?,?,?,?,?,?,?)") ;
for(int i=0;i<tt.length;i++)
{ for(int j=0;j<tt[i].length;j++)
{ if(tt[i][j].isEmpty())
continue;
for(int k=0;k<tt[i][j].size();k++)
{count++;
prep2.setInt(1, i);
prep2.setInt(2, j);
prep2.setInt(3,tt[i][j].get(k).venue_id);
prep2.setInt(4,tt[i][j].get(k).course_id);
prep2.setInt(5,session);
prep2.setInt(6,runtype);
prep2.setString(7,tt_type);
prep2.setInt(8,tt[i][j].get(k).user_id);
prep2.addBatch();
if(count%200==0)
prep2.executeBatch();
}
}}
prep2.executeBatch();
System.out.println("THE AMOUNT OF TIME IT TOK TO INSERT TTDATA IS "+(System.currentTimeMillis()-cinsert)+" milliSECONDS");
prep2.close();
}
catch(SQLException ex)
{
ex.printStackTrace();
}
*
**/
query="insert LOW_PRIORITY into tt_data(day_id,period_id," +
"venue_id,course_id,session,runtype,tt_type,userid) VALUES";
for(int i=0;i<tt.length;i++)
{ for(int j=0;j<tt[i].length;j++)
{ if(tt[i][j].isEmpty())
continue;
for(int k=0;k<tt[i][j].size();k++)
{ count++;
if(count==1)
{
day_id=i;
period_id=j;
course_id=tt[i][j].get(k).course_id;
venue_id=tt[i][j].get(k).venue_id;
user_id=tt[i][j].get(k).user_id;
query=query+"("+day_id+","+period_id+","+venue_id +","+course_id +","+session+","+runtype+",'"+tt_type+"'"+","+user_id+")";
}
else
{day_id=i;
period_id=j;
course_id=tt[i][j].get(k).course_id;
venue_id=tt[i][j].get(k).venue_id;
user_id=tt[i][j].get(k).user_id;
query=query+",("+day_id+","+period_id+","+venue_id +","+course_id +","+session+","+runtype+",'"+tt_type+"'"+","+user_id+")";
}
}
}
}
query=query+";";
System.out.println("THE AMOUNT OF TIME IT TOK TO INSERT TTDATA IS "+(System.currentTimeMillis()-cinsert)+" milliSECONDS");
database.update(query, conn);
System.out.println("timetable inserted into database ");
database.closeconnection(conn);
return 1;
}
hello . have replaced the array values with variables. have also timed the speed of the database insertions of all the data. removing the component which displayed every insert made it a bit faster. i think the speed is now better.the inserts are also faster. i used a single insert update with multiple values
the comments in my code are prepared statements that are tried writing.it uses the batch prepared statements to send the queries to the database.i add several statements together and when it reaches a particular number it sends the queries to the database to be processed. however,the speed of the prepared statements inserts were slower than putting all the queries into one single insert statement with multiple values.do prepared statements get faster after you reach a particular threshold of the number of queries you want to send or am i missing something because i thought prepared statements were faster than normal statements
30 Jul, 2008 - 01:40 AM
| 