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 ===
Received on Wed Sep 20 2000 - 10:12:18 CDT