Slow Insertion in SQL database

"Please leave a message at the beep, we will get back to you when your support contract expires."

Moderators: phlip, Moderators General, Prelates

User avatar
Afif_D
Posts: 184
Joined: Wed Oct 06, 2010 2:56 pm UTC

Slow Insertion in SQL database

Postby Afif_D » Sun Jun 08, 2014 2:36 pm UTC

We have some 365*4 files each containing 1.6 million lines of records. But the problem is that it takes very large amount of time(4 hrs approx) to complete insertion even for 1 file. The server has only 4 gigabytes of RAM. Here is the code. Pls help me out.

Code: Select all

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.io.*;
import java.util.*;
import java.math.*;
import java.text.*;

public class WriteToDatabase {

    public static void main(String[] args) throws IOException
     {

        Connection con = null;
        ResultSet rs = null;
        PreparedStatement pst =null;
   
        String url = "jdbc:postgresql://localhost/russel";
        String user = "russel";
        String password = "russel123";
        String filename="";
        try {

            con = DriverManager.getConnection(url, user, password);
            System.out.println("gps_data Dropped");
           
            System.out.println("Table Created");

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
            Calendar thisDate = new GregorianCalendar(2013,02,01);
            final int numberOfDays=306;
            BufferedReader br;
            ArrayList<String> data;
            for(int i=0;i<numberOfDays;i++)
            {
              sdf= new SimpleDateFormat("yyyy/MM/dd");
               String thisDateString=sdf.format(thisDate.getTime());
               String[] cols=thisDateString.split("/");
              pst=con.prepareStatement("DROP TABLE IF EXISTS gps_data"+cols[0]+cols[1]+cols[2]+";");
            pst.executeUpdate();
              String sql1 = "CREATE TABLE IF NOT EXISTS "+ "gps_data" +cols[0]+cols[1]+cols[2]+" (  I_LAT            FLOAT , " +" I_LONG       FLOAT , " +
                         " DT_MESSAGE    VARCHAR(30)  , " +
                         " VEHICLE_ID      VARCHAR(20), " +
                         "TRIP_NO INTEGER," +
                         "ODOMETER_READING FLOAT, "+
                         "S_WPNT1    VARCHAR(50), "+
                         "I_WPNT1_MT   INTEGER, "+
                         "S_WPNT2    VARCHAR(50), "+
                         "I_WPNT2_MT   INTEGER)";

              pst=con.prepareStatement(sql1);
              pst.executeUpdate();

               
                filename="/var/lib/pgsql/Year_2013/gps_data/GPS_Data"+cols[0]+cols[1]+cols[2]+".csv";
                System.out.println("Started creating table gps_data"+cols[0]+cols[1]+cols[2]+".csv");
                try
                {
               br = new BufferedReader(new FileReader(filename));
              }
                catch(FileNotFoundException f)
                {
                  System.out.println(filename +" not found. Continuing..");
                  continue;
                }
               String line;
               line=br.readLine();
               int counter=0,k=0;
               boolean exit=false;
             
               while(!exit)
               {
                data=new ArrayList<String>();
                //Reading 500 element
                k=0;
               while((line=br.readLine())!=null )
               {

                  data.add(line);
                  k++;
                  if(k==500)
                    break;
               }
               //System.out.println("Read 500 elements");
                if(line==null)
                  exit=true;
                for(i=0;i<data.size();i++)
                {
                  line=data.get(i);
                  String[] columns=line.split(",");
                  // String vehicleID=columns[5];
                  // int tripID=Integer.parseInt(columns[6]);
                  // float latitude=Float.valueOf(columns[2]);
                  // float longitude=Float.valueOf(columns[3]);
                  // float meterReading= Float.valueOf(columns[21]);
                  // String date_message=columns[4];
                  columns[16]=columns[16].replace("\'"," ");
                 // columns[16]=columns[16].replace("\'"," ");
                  columns[14]=columns[14].replace("\'"," ");
                //  columns[14]=columns[14].replace("\'"," "); 
                  String sql2="INSERT INTO gps_data"+cols[0]+cols[1]+cols[2]+" (I_LAT, I_LONG,DT_MESSAGE,VEHICLE_ID,TRIP_NO,ODOMETER_READING,S_WPNT1,I_WPNT1_MT,S_WPNT2,I_WPNT2_MT ) "+
                  "VALUES ( "+columns[2]+" , "+columns[3]+" , "+"\'"+columns[4]+"\'"+" , "+"\'"+columns[5]+"\'"+" , "+columns[6]+" , "+columns[21]+
                    ", \'"+columns[14]+"\'"+" , "+columns[15]+" , "+"\'"+columns[16]+"\'"+" , "+columns[17]+ " );"; 
                    //System.out.println(sql2);
                  pst=con.prepareStatement(sql2);
                  pst.executeUpdate();
                 
               }
              // System.out.println("Wrote 500 elements");
               counter+=500;
                  if(counter%1000==0)
                    System.out.println(counter);
             }
               System.out.println("File : "+filename+" Done ");
               thisDate.add(Calendar.DATE,1);
            }

        } catch (SQLException ex) {
            System.out.println(filename+"\n\n");
            Logger lgr = Logger.getLogger(WriteToDatabase.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);

        } finally {

            try {
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                Logger lgr = Logger.getLogger(WriteToDatabase.class.getName());
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }
        }
    }
}
Image

User avatar
Xenomortis
Not actually a special flower.
Posts: 1456
Joined: Thu Oct 11, 2012 8:47 am UTC

Re: Slow Insertion in SQL database

Postby Xenomortis » Sun Jun 08, 2014 3:35 pm UTC

Maybe it's occurred to you that doing 1.6 million separate inserts is a really bad way of inserting a large amount of data.

There should be a way of creating/updating a table from a .csv file - look for that.
Image

speising
Posts: 2365
Joined: Mon Sep 03, 2012 4:54 pm UTC
Location: wien

Re: Slow Insertion in SQL database

Postby speising » Sun Jun 08, 2014 3:58 pm UTC

i don't know what the overhead of prepare is, but i'm fairly sure it is more efficient to do it only once. see "parameterized query". also, this wouldn't be vulnerable to a little bobby tables attack.

User avatar
Thesh
Made to Fuck Dinosaurs
Posts: 6598
Joined: Tue Jan 12, 2010 1:55 am UTC
Location: Colorado

Re: Slow Insertion in SQL database

Postby Thesh » Sun Jun 08, 2014 7:05 pm UTC

Two things to look into:

1) Use parameterized queries, as mentioned (you only need to build the query itself once), and at the start of each 500 row block, start a begin transaction, and at the end of each block do a commit. The way it is now, you are committing 1.6 million individual transactions per file; this will reduce that to 3200 transactions per file, a significant performance boost.

Code: Select all

BEGIN;

INSERT INTO a (b, c, d) VALUES (1, 2, 3);
INSERT INTO a (b, c, d) VALUES (4, 5, 6);
INSERT INTO a (b, c, d) VALUES (7, 8, 9);

COMMIT;


2) Build an insert statement with 500 lines. While parameterized queries are really not possible at this point, it may be less overhead overall, and the begin and commit statements are not needed. Just make sure you build the string using a class especially designed for string building; concatenation adds a lot of overhead as well.

Code: Select all

INSERT INTO a (b, c, d)
VALUES
    (1,2,3),
    (4,5,6),
    (7,8,9);
Summum ius, summa iniuria.

bittyx
Posts: 194
Joined: Tue Sep 25, 2007 9:10 pm UTC
Location: Belgrade, Serbia

Re: Slow Insertion in SQL database

Postby bittyx » Thu Jun 12, 2014 5:14 pm UTC

Also take a look at this, which should be the fastest way to import a bunch of data into a MySQL table at once. It's easy to import a CSV file like this, and it should be much faster than what you're doing right now.


Return to “The Help Desk”

Who is online

Users browsing this forum: No registered users and 9 guests