Re: [SPAM] - Re: JDBC HighLoad - Found word(s) XXX in the

Because every time you do pstmt_xxx = c.prepareStatement.... you are
over writing the previous one.

Dave

Stéphane RIFF wrote:

> Dave Cramer wrote:
>
>> Stephane,
>>
>> You are using this class as a singleton in a multithreaded
>> environment ???
>>
>>
>> Dave
>>
>> Stéphane RIFF wrote:
>>
>>> Stéphane RIFF wrote:
>>>
>>>> Hi i have a java aplication which connect to postgresql via jdbc.
>>>> This app make a lot of request per second.
>>>> The problem is that i get delayed records between the moment the
>>>> informations arrived via socket to java and
>>>> the moment it's recorded in the database. It seems that jdbc wait
>>>> before inserting/updating the db because
>>>> they are to much requests.
>>>> For testing i made a little app in c using libpq to see if problem
>>>> persist and there no delay.
>>>>
>>>> So i'd like to know a way to speed up jdbc if possible ???
>>>>
>>>>
>>> Here my connection class, is there something wrong ?
>>>
>>> import java.io.*;
>>> import java.util.*;
>>> import java.sql.*;
>>> import java.text.*;
>>> import javax.sql.DataSource;
>>> import org.postgresql.jdbc3.Jdbc3PoolingDataSource;
>>>
>>> import org.apache.log4j.Logger;
>>> import org.apache.log4j.PropertyConfigurator;
>>>
>>> public class SQLoader {
>>> private Jdbc3PoolingDataSource datasource ;
>>> //query object gps
>>> private PreparedStatement pstmt_gps ;
>>> //query object io
>>> private PreparedStatement pstmt_io ;
>>> //query object gps
>>> private PreparedStatement pstmt_ugps ;
>>> //query object io
>>> private PreparedStatement pstmt_uio ;
>>> //query object gps
>>> private PreparedStatement pstmt_hgps ;
>>> //query object io
>>> private PreparedStatement pstmt_hio ;
>>> //singleton class
>>> private final static SQLoader myRef = new SQLoader();
>>> //Log4j logger object
>>> private static Logger logger =
>>> Logger.getLogger(SQLoader.class.getName());
>>> //jdbc driver
>>> private String driver ;
>>> //jdbc connection string
>>> private String jdbcConnectionString ;
>>> //database user
>>> private String dbUser ;
>>> //database password
>>> private String dbPwd ;
>>> /**
>>> * Private constructor
>>> *
>>> * @since 1.0
>>> */
>>> private SQLoader() {}
>>> /**
>>> * Give access to the unique instance of this class
>>> *
>>> * @return The instance
>>> * @since 1.0
>>> */
>>> public static SQLoader getRef()
>>> {
>>> return myRef ;
>>> }
>>> public void connect(String driver,String dbc, String dbu,
>>> String dbp)
>>> {
>>> Connection m_conn = null;
>>> try {
>>> datasource = setupDataSource(dbc);
>>> }catch(Exception e){
>>> logger.fatal(e.toString());
>>> System.exit(-1);
>>> }
>>> }
>>> private void prepareQuery(Connection c)
>>> {
>>> try
>>> {
>>> //Construct predefined query
>>> String qry = "INSERT INTO gps_frame (" +
>>> "\"sbox_id\"," +
>>> "\"gps_date\"," +
>>> "\"badge_id\"," +
>>> "\"gmt_creation_date\"," +
>>>
>>> "\"wgs84_position\","+
>>> "\"speed\"," +
>>> "\"altitude\","+
>>> "\"heading\","+
>>> "\"validity\"," +
>>> "\"geom\")" +
>>> " VALUES( ?,?,?,?,?,?,?,?,?,?)";
>>> pstmt_gps = c.prepareStatement(qry);
>>>
>>> String qry1 = "INSERT INTO io_frame ("+
>>> "\"sbox_id\","+
>>> "\"gps_date\","+
>>> "\"io_type\","+
>>> "\"io_rank\","+
>>> "\"io_value\")"+
>>> " VALUES( ?,?,?,?,?)";
>>> pstmt_io = c.prepareStatement(qry1);
>>>
>>> String uqry = "UPDATE gps_frame SET "+
>>> "\"gps_date\"=?,"+
>>> "\"badge_id\"=?,"+
>>> "\"gmt_creation_date\"=?,"+
>>> "\"wgs84_position\"=?,"+
>>> "\"speed\"=?,"+
>>> "\"altitude\"=?,"+
>>> "\"heading\"=?,"+
>>> "\"validity\"=?,"+
>>> "\"geom\"=?"+
>>> " WHERE \"sbox_id\"=?";
>>> pstmt_ugps = c.prepareStatement(uqry);
>>>
>>> String uqry1 = "UPDATE io_frame SET "+
>>> "\"gps_date\"=?,"+
>>> "\"io_value\"=?"+
>>> " WHERE \"sbox_id\"=? AND \"io_rank\"=?
>>> AND io_type=?";
>>> pstmt_uio = c.prepareStatement(uqry1);
>>>
>>> qry = "INSERT INTO gps_frame_history (" +
>>> "\"sbox_id\"," +
>>> "\"gps_date\"," +
>>> "\"badge_id\"," +
>>> "\"gmt_creation_date\"," +
>>>
>>> "\"wgs84_position\","+
>>> "\"speed\"," +
>>> "\"altitude\","+
>>> "\"heading\","+
>>> "\"validity\"," +
>>> "\"geom\")" +
>>> " VALUES( ?,?,?,?,?,?,?,?,?,?)";
>>> pstmt_hgps = c.prepareStatement(qry);
>>>
>>> qry1 = "INSERT INTO io_frame_history ("+
>>> "\"sbox_id\","+
>>> "\"gps_date\","+
>>> "\"io_type\","+
>>> "\"io_rank\","+
>>> "\"io_value\")"+
>>> " VALUES( ?,?,?,?,?)";
>>> pstmt_hio = c.prepareStatement(qry1);
>>> }catch( java.sql.SQLException e)
>>> {
>>> logger.fatal(e.toString());
>>> System.exit(-1);
>>> }
>>> }
>>> /**
>>> * Disconnect from DB
>>> *
>>> * @since 1.0
>>> */
>>> public void disconnect()
>>> {
>>> try {
>>> shutdownDataSource(datasource);
>>> } catch(Exception e){
>>> logger.fatal(e.toString());
>>> System.exit(-1);
>>> }
>>> }
>>> public void saveTrame(String boxID, String badgeID, String
>>> gpsDate, double speed,
>>> String wgs84, double altitude,
>>> double azimuth,
>>> String validity, String geom,
>>> String sysDate, int[] input, int[] output)
>>> { Connection m_conn = null;
>>> try
>>> {
>>> m_conn = datasource.getConnection();
>>> m_conn.setAutoCommit(false);
>>> prepareQuery(m_conn);
>>> //set query values for update gps_frame
>>> pstmt_ugps.setString(1, gpsDate);
>>> pstmt_ugps.setString(2, badgeID);
>>> pstmt_ugps.setString(3, sysDate);
>>> pstmt_ugps.setString(4, wgs84);
>>> pstmt_ugps.setDouble(5, speed);
>>> pstmt_ugps.setDouble(6, altitude);
>>> pstmt_ugps.setDouble(7, azimuth);
>>> pstmt_ugps.setString(8, validity);
>>> pstmt_ugps.setString(9, geom);
>>> pstmt_ugps.setString(10, boxID);
>>> if(pstmt_ugps.executeUpdate()==0)
>>> { //if no frame already exists insert it
>>> pstmt_gps.setString(1, boxID);
>>> pstmt_gps.setString(2, gpsDate);
>>> pstmt_gps.setString(3, badgeID);
>>> pstmt_gps.setString(4, sysDate);
>>> pstmt_gps.setString(5, wgs84);
>>> pstmt_gps.setDouble(6, speed);
>>> pstmt_gps.setDouble(7, altitude);
>>> pstmt_gps.setDouble(8, azimuth);
>>> pstmt_gps.setString(9, validity);
>>> pstmt_gps.setString(10, geom);
>>> pstmt_gps.executeUpdate();
>>> for(int i = 0; i < input.length; i++)
>>> {
>>> pstmt_io.setString(1, boxID);
>>> pstmt_io.setString(2, gpsDate);
>>> pstmt_io.setString(3, "i");
>>> pstmt_io.setInt(4, (i+1));
>>> pstmt_io.setInt(5, input[i]);
>>> pstmt_io.executeUpdate();
>>> }
>>> for(int o = 0; o < output.length; o++)
>>> {
>>> pstmt_io.setString(1, boxID);
>>> pstmt_io.setString(2, gpsDate);
>>> pstmt_io.setString(3, "o");
>>> pstmt_io.setInt(4, (o+1));
>>> pstmt_io.setInt(5, output[o]);
>>> pstmt_io.executeUpdate();
>>> }
>>> }else
>>> { //if frame already exists in gps_frame update his io
>>> for(int i = 0; i < input.length; i++)
>>> {
>>> pstmt_uio.setString(1, gpsDate);
>>> pstmt_uio.setInt(2, input[i]);
>>> pstmt_uio.setString(3, boxID);
>>> pstmt_uio.setInt(4, (i+1));
>>> pstmt_uio.setString(5,"i");
>>> pstmt_uio.executeUpdate();
>>> }
>>> for(int o = 0; o < output.length; o++)
>>> {
>>> pstmt_uio.setString(1, gpsDate);
>>> pstmt_uio.setInt(2, output[o]);
>>> pstmt_uio.setString(3, boxID);
>>> pstmt_uio.setInt(4, (o+1));
>>> pstmt_uio.setString(5,"o");
>>> pstmt_uio.executeUpdate();
>>> }
>>> }
>>> //insert in hisory
>>> pstmt_hgps.setString(1, boxID);
>>> pstmt_hgps.setString(2, gpsDate);
>>> pstmt_hgps.setString(3, badgeID);
>>> pstmt_hgps.setString(4, sysDate);
>>> pstmt_hgps.setString(5, wgs84);
>>> pstmt_hgps.setDouble(6, speed);
>>> pstmt_hgps.setDouble(7, altitude);
>>> pstmt_hgps.setDouble(8, azimuth);
>>> pstmt_hgps.setString(9, validity);
>>> pstmt_hgps.setString(10, geom);
>>> pstmt_hgps.executeUpdate();
>>>
>>> for(int i = 0; i < input.length; i++)
>>> {
>>> pstmt_hio.setString(1, boxID);
>>> pstmt_hio.setString(2, gpsDate);
>>> pstmt_hio.setString(3, "i");
>>> pstmt_hio.setInt(4, (i+1));
>>> pstmt_hio.setInt(5, input[i]);
>>> pstmt_hio.executeUpdate();
>>> }
>>> for(int o = 0; o < output.length; o++)
>>> {
>>> pstmt_hio.setString(1, boxID);
>>> pstmt_hio.setString(2, gpsDate);
>>> pstmt_hio.setString(3, "o");
>>> pstmt_hio.setInt(4, (o+1));
>>> pstmt_hio.setInt(5, output[o]);
>>> pstmt_hio.executeUpdate();
>>> }
>>> m_conn.commit();
>>> }
>>> catch(java.sql.SQLException e){
>>> String msg = e ;
>>> logger.warn(msg);
>>> }
>>> finally {
>>> try { m_conn.close(); } catch(Exception e) { }
>>> }
>>> }
>>> public Jdbc3PoolingDataSource setupDataSource(String connectURI) {
>>> Jdbc3PoolingDataSource ds = new Jdbc3PoolingDataSource();
>>> ds.setDataSourceName("Xxx");
>>> ds.setServerName("xxx.xxx.xxx.xxx");
>>> ds.setDatabaseName("xxxxxxxxxxxxx");
>>> ds.setUser("xxxxx");
>>> ds.setPassword("xxxxx");
>>> ds.setMaxConnections(10);
>>> return ds;
>>> }
>>> public static void shutdownDataSource(DataSource ds) throws
>>> SQLException {
>>> }
>>> }
>>>
>>> When i highload postgresql with a multi-threaded serverSocket using
>>> this class,
>>> my app freezes very quickly and all my connection are in idle state.
>>> It seems that the connection aren't released.
>>>
>>> It don't know what i can do if someone could help ???
>>> Thanks
>>>
>>>
>>
> Yes i use it as a singleon what's the problem with that ???
> I instanciate One object => 1 pool for all threads and each thread use
> the saveTrame
> isn't this right ???
>
> Thanks
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561