CODEWITHSUNDEEP
javasqloracle11g
user1285928
user1285928

Reputation: 1476

How to optimize this update SQL query

I have this Java method which I will use to insert data from JSF form into Oracle:

public int saveData(int result) throws SQLException, java.text.ParseException, NoSuchAlgorithmException {

        String SqlStatement = null;

        if (ds == null) {
            throw new SQLException();
        }

        Connection conn = ds.getConnection();
        if (conn == null) {
            throw new SQLException();
        }

        PreparedStatement ps = null;

        /*

        CREATE TABLE USERS(
            USERID INTEGER NOT NULL,
            GROUPID INTEGER,
            SPECIALNUMBER VARCHAR2(60 ),
            USERNAME VARCHAR2(50 ),
            PASSWD VARCHAR2(50 ),
            DATETOCHANGEPASSWD DATE,
            ADDRESS VARCHAR2(60 ),
            STATEREGION VARCHAR2(50 ),
            COUNTRY VARCHAR2(50 ),
            USERSTATUS VARCHAR2(30 ),
            TELEPHONE VARCHAR2(50 ),
            DATEUSERADDED DATE,
            USEREXPIREDATE DATE,
            DATEUSERLOCKED CHAR(20 ),
            CITY VARCHAR2(50 ),
            EMAIL VARCHAR2(50 ),
            DESCRIPTION CLOB
        )
        /
        */

        try {
            conn.setAutoCommit(false);
            boolean committed = false;
            try {           /* insert into Oracle the default system(Linux) time */
        InsertSqlStatement = "INSERT INTO USERS"
                        + " (USERID, GROUPID, SPECIALNUMBER, USERNAME, PASSWD, DATETOCHANGEPASSWD,"
                        + " ADDRESS, STATEREGION, COUNTRY, USERSTATUS, TELEPHONE, DATEUSERADDED," 
                        + " USEREXPIREDATE, DATEUSERLOCKED, CITY, EMAIL, DESCRIPTION)"
                        + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        UpdateSqlStatement = "UPDATE USERS "
                                + "SET "
                                    + "USERID = ?, "
                                    + "GROUPID = ?, "
                                    + "SPECIALNUMBER = ?, "
                                    + "USERNAME = ?, "
                                    + "PASSWD = ?, "
                                    + "DATETOCHANGEPASSWD = ?, "
                                    + "ADDRESS = ?, "
                                    + "STATEREGION = ?, "
                                    + "COUNTRY = ?, "
                                    + "USERSTATUS = ?, "
                                    + "TELEPHONE = ?, "
                                    + "DATEUSERADDED = ?, "
                                    + "USEREXPIREDATE = ?, "
                                    + "DATEUSERLOCKED = ?, "
                                    + "CITY = ?, "
                                    + "EMAIL = ?, "
                                    + "DESCRIPTION = ? "
                        + "WHERE USERID = " + id;

                ps = conn.prepareStatement(SqlStatement);

                ps.setString(1, settingsMap.get("USERID"));
                ps.setString(2, settingsMap.get("GROUPID"));
                ps.setString(3, settingsMap.get("SPECIALNUMBER"));
                ps.setString(4, settingsMap.get("USERNAME"));
                ps.setString(5, passwdConvert(settingsMap.get("PASSWD")));
                ps.setDate(6, toDate(settingsMap.get("DATETOCHANGEPASSWD")));
                ps.setString(7, settingsMap.get("ADDRESS"));
                ps.setString(8, settingsMap.get("STATEREGION"));
                ps.setString(9, settingsMap.get("COUNTRY"));
                ps.setString(10, settingsMap.get("USERSTATUS"));
                ps.setString(11, settingsMap.get("TELEPHONE"));
                ps.setDate(12, toDate(settingsMap.get("DATEUSERADDED")));
                ps.setDate(13, toDate(settingsMap.get("USEREXPIREDATE")));
                ps.setDate(14, toDate(settingsMap.get("DATEUSERLOCKED")));
                ps.setString(15, settingsMap.get("CITY"));
                ps.setString(16, settingsMap.get("EMAIL"));
                ps.setString(17, settingsMap.get("DESCRIPTION"));

                ps.executeUpdate();

                conn.commit();
                committed = true;
            }
            finally 
            {
                if (!committed) {
                    conn.rollback();
                }
            }
        } finally {
            /* Release the resources */
            ps.close();
            conn.close();
        }
        return result;
    }

Right now I cannot test the SQL query. Can you tell me is it valid and how I can optimize the SQL query for performance?

Upvotes: 1

Views: 283

Answers (4)

Stephen C
Stephen C

Reputation: 718758

Right now I cannot test the SQL query. Can you tell me is it valid ...

Not with any certainty. (Why don't you wait until you CAN test it??)

... and how I can optimize the SQL query for performance?

It is not entirely clear what you are trying to do. However, here are some suggestions on performance:

  • You are creating and releasing a database connection for each SQL statement executed. That has to be bad for performance.
  • There is no need to do an insert followed by an update of the same record ... if that is what you are proposing to do.
  • You will get performance by doing a bulk or batch insert or update rather than inserting records one at a time.
  • If you are inserting lots of data into an empty table with lots of indexes, then you may get better performance if you do the insertions first and create the indexes afterwards.

At the level of a single query (i.e. the "UPDATE"), you probably cannot make the query significantly faster.

Upvotes: 3

MvG
MvG

Reputation: 60858

If you are going to insert several rows, then you could increase performance by reusing the database connection, as well as the prepared statement. The latter requires treating the user id as a row as well, the way ftom2 suggested. Apart from that, there is little room for performance optimizations.

Upvotes: 1

Tomer
Tomer

Reputation: 17930

The only improvement you can make is put the id as '?' also:

UPDATE USERS "
                            + "SET "
                                + "USERID = ?, "
                                + "GROUPID = ?, "
                                + "SPECIALNUMBER = ?, "
                                + "USERNAME = ?, "
                                + "PASSWD = ?, "
                                + "DATETOCHANGEPASSWD = ?, "
                                + "ADDRESS = ?, "
                                + "STATEREGION = ?, "
                                + "COUNTRY = ?, "
                                + "USERSTATUS = ?, "
                                + "TELEPHONE = ?, "
                                + "DATEUSERADDED = ?, "
                                + "USEREXPIREDATE = ?, "
                                + "DATEUSERLOCKED = ?, "
                                + "CITY = ?, "
                                + "EMAIL = ?, "
                                + "DESCRIPTION = ? "
                    + "WHERE USERID = ?";

And of course add a set decleration:

 ps.setInt(18, id);

Upvotes: 2

Petr Mensik
Petr Mensik

Reputation: 27496

I think there is nothing to optimize because you are inserting to only one table. Same for update. There are no joins or grouping so there is really anything you can do about it. Maybe just one note - you could use StringBuilder for code formatting :-)

Upvotes: 1

Related Questions