0

The issue I am working on is the elapsed time it takes to execute 3 SP's in the following TRY{} CATCH{} statement is presenting problems when executing a transaction that is compared to the data in 2 of the SQL tables. I have a dataset that is being created from an XML file, parsed and inserted into a CARD object and then inserted into 2 tables from that object. The number of records being processed is 15,198 records. Per the INSERTTMS column in each table there is a timestamp of when the record is inserted in the table, it is literally taking 10 minutes and 33 seconds to complete the insert.

I am needing help finding a solution to refactor this code to help speed up the process. We're running on SQL Server 2003 and we're using Java 1.6. Any help/direction would be greatly appreciated. Thanks.

Here is the code:

ResultSet results = null;
    CallableStatement call = null;
    PassThroughDBConnection con = null;
    try {
        con = new PassThroughDBConnection();
        con.setName("PBFDBConnection");
        con.setDBServer(System.getProperty(ATMServer.DBSERVER_PROPERTY));
        con.setDBServerType(System.getProperty(ATMServer.PROP_DBSERVERTYPE));
        con.setDBName(System.getProperty(ATMServer.DBNAME_PROPERTY));
        if (System.getProperty(ATMServer.DBUSER_PROPERTY) != null) {
            con.setDBUser(System.getProperty(ATMServer.DBUSER_PROPERTY));
        }
        if (System.getProperty(ATMServer.DBPASS_PROPERTY) != null) {
            con.setDBPass(System.getProperty(ATMServer.DBPASS_PROPERTY));
        }
        con.connect();

        call = con.prepareCall("{call clearData (?, ?)}");
        call.setInt("ServerID", ATMServer.getServerID());
        call.setInt("BankID", owningChannel.getBankID());
        call.executeUpdate();

        try {
            call.close();
        } catch (Exception e) {

        }
        results = null;
        call = null;
        LOGGER.trace("Preparing to save PBF data to database.  cards size: " + cards.size());           

        for (Card currentCard : cards.values()) {

            // add the card record
            // add any account records
            call = con.prepareCall("{call insertCardData (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");

            call.setInt("ServerID", ATMServer.getServerID());
            call.setInt("BankID", owningChannel.getBankID());
            call.setString("CardNumber", currentCard.cardnumber);
            call.setString("Zip", currentCard.zip);
            call.setString("Address", currentCard.address);
            call.setString("Expiration", currentCard.expire);
            call.setLong("PurchLimit", currentCard.purLimit);
            call.setLong("PurchUsed", currentCard.purUsed);
            call.setLong("ATMLimit", currentCard.atmLimit);
            call.setLong("ATMUsed", currentCard.atmUsed);
            call.setShort("Status", currentCard.status);
            call.setShort("Sequence", currentCard.cardSequence);
            call.setShort("CardType", currentCard.cardType.pbfValue);

            results = call.executeQuery();
            Long newId = null;
            if (results.next()) {
                newId = results.getLong("NewID");
                if (newId != null) {
                    currentCard.databaseId = newId;
                    for (Account account : currentCard.accounts) {
                        if (account != null) {
                            call = con.prepareCall("{call insertAccountData (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
                            call.setInt("ServerID", ATMServer.getServerID());
                            call.setInt("BankID", owningChannel.getBankID());
                            call.setString("AccountID", account.accountID);
                            call.setString("Zip", account.zip);
                            call.setString("Address", account.address);
                            call.setLong("Available", account.available);
                            call.setLong("Balance", account.balance);
                            call.setLong("CreditLine", account.creditline);
                            call.setShort("AccountType", (short) account.accountType.pbfValue);
                            call.setLong("CardId", newId);
                            call.executeUpdate();
                            try {
                                call.close();
                            } catch (Exception e) {
                                LOGGER.fatal("Error saving PBF data to database:" + FormatData.formatStack(e));
                            }
                            results = null;
                            call = null;
                        }
                    }
                }
            }
        }

1 Answer 1

1

One way of doing this is by doing the inserts in batch, instead of doing it one by one insert. It will save multiple trips to database and will improve performance.

Use call.addBatch() and once all inserts are added into batch call call.executeBatch()

Something like this

               currentCard.databaseId = newId;
                for (Account account : currentCard.accounts) {
                    if (account != null) {
                        call = con.prepareCall("{call insertAccountData (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
                        call.setInt("ServerID", ATMServer.getServerID());
                        call.setInt("BankID", owningChannel.getBankID());
                        call.setString("AccountID", account.accountID);
                        call.setString("Zip", account.zip);
                        call.setString("Address", account.address);
                        call.setLong("Available", account.available);
                        call.setLong("Balance", account.balance);
                        call.setLong("CreditLine", account.creditline);
                        call.setShort("AccountType", (short) account.accountType.pbfValue);
                        call.setLong("CardId", newId);
                        call.addBatch();

                    }
                }
                call.executeBatch()
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.