Re: Slow database....

One thing you can do is
truncate the table first before droping it. You may want to do frequent commit if you are inserting thousands of rows..

..Ashish

• Clinton Naude <clintonn_at_meb.co.za> wrote:
  > Hi all...
  >
  > I am stumped with this one and its driving me mad!!!
  >
  > One of our developers is running a script that is
  > slowing the database down
  > drastically. It actually even hangs it.
  > I have tried to change database parameters etc and
  > with no luck.
  >
  > Here are my database parameters and the script he is
  > running (its java),
  > what am I doing wrong?
  >
  > The archive logs also seem to be switching over
  > every 15 seconds when this
  > scripts is running...
  >
  >
  >
  > import java.sql.DriverManager;
  > import java.sql.Connection;
  > import java.sql.PreparedStatement;
  > import java.util.ArrayList;
  > import java.util.Date;
  >
  > public class Tatanka {
  >
  > public static void main(String args[])
  > {
  >
  > Connection con=null;
  > PreparedStatement st=null;
  > long tollerance = (long)1000;
  > int number = 20000;
  > try
  > {
  >
  >
  > Class.forName("oracle.jdbc.driver.OracleDriver");
  > con =
  > DriverManager.getConnection("jdbc:oracle:thin:@10.6.5.211:1521:online","test
  > user","sunos01");
  >
  > ArrayList drop = new ArrayList();
  > ArrayList create = new ArrayList();
  > ArrayList alter = new ArrayList();
  > ArrayList insert = new ArrayList();
  > ArrayList delete = new ArrayList();
  >
  > create.add(new String("create table
  > csemployee" +
  > " (username varchar2(40),"
  > +
  > " password varchar2(40),"
  > +
  > " firstname
  > varchar2(40)," +
  > " surname varchar2(40),"
  > +
  > " id varchar2(13))"));
  > alter.add(new String("alter table csemployee "
  > +
  > " add constraint
  > csemployee_pk primary key" +
  > " (username)"));
  >
  >
  > drop.add(new String("drop table csemployee"));
  >
  > for (int i =0 ; i < number ;i += 1)
  > {
  >
  > String todo = new String("insert into
  > csemployee " +
  > "values("
  > + "'" + new
  > String("pietieGets" + i)+ "'" + ","
  > + "'" + new String(new
  > StringBuffer("pietieGets"
  > + i).reverse().toString())+ "'"+","
  > + "'" + new
  > String("Piet" + i)+ "'"+ ","
  > + "'" + new
  > String("Pompies"+ i)+ "'"+ ","
  > + "'" + i+ "'"
  > + ")");
  > insert.add(todo);
  > delete.add(new String("pietieGets" + i));
  > if ((i % 1000) == 0 )
  > System.out.println("Created number: " + i
  > + " ---- " + todo);
  >
  > }
  >
  > System.out.println("dropping table... ");
  > // drop the tables
  > for (int i = 0 ; i < drop.size() ; i += 1 )
  > {
  > try
  > {
  > st =
  > con.prepareStatement((String)drop.get(i));
  > st.executeUpdate();
  >
  > System.out.println((String)drop.get(i) + "
  > succeeded!");
  > }
  > catch (Exception e)
  > {
  > System.out.println("Could not drop table:
  > "+e);
  > }
  >
  > }
  > System.out.println("droped table: ");
  >
  > // create the tables
  > for (int i = 0 ; i < create.size() ; i += 1 )
  > {
  > try
  > {
  > st =
  > con.prepareStatement((String)create.get(i));
  > st.executeUpdate();
  >
  > System.out.println((String)create.get(i) +
  > " succeeded!");
  > }
  > catch (Exception e)
  > {
  > System.out.println("Could not create
  > table: "+e);
  > }
  >
  > }
  > System.out.println("created table: ");
  >
  > // alter the tables
  > for (int i = 0 ; i < alter.size() ; i += 1 )
  > {
  > try
  > {
  > st =
  > con.prepareStatement((String)alter.get(i));
  > st.executeUpdate();
  >
  > System.out.println((String)alter.get(i) +
  > " succeeded!");
  > }
  > catch (Exception e)
  > {
  > System.out.println("Could not alter table:
  > "+e);
  > }
  >
  > }
  > System.out.println("altered table: ");
  >
  >
  > // insert into the tables
  > System.out.println("Going to do the
  > insert...");
  > for (int i = 0 ; i < insert.size() ; i += 1 )
  > {
  > Date date_start;
  > Date date_end;
  >
  > try
  > {
  > st =
  > con.prepareStatement((String)insert.get(i));
  > date_start = new Date();
  > st.executeUpdate();
  > st.close();
  > date_end = new Date();
  > if ((i % 1000) == 0)
  > {
  > System.out.println("Insert nr:" + i);
  > //con.commit();
  > }
  > double diff =
  > (double)date_end.getTime()-(double)date_start.getTime();
  > System.out.println("time pause (insert):
  > time ms - " + diff +
  > "--- item - " +i);
  > }
  > catch (Exception e)
  > {
  > System.out.println("Could not insert into
  > table: "+e);
  > }
  > }
  > System.out.println("insert complete.");
  >
  > System.out.println("Going to do the
  > delete...");
  >
  > // delete from the tables
  > for (int i = 0 ; i < delete.size() ; i += 1 )
  > {
  >
  === message truncated ===