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