How to make this sql processing faster?

Posted on 2006-04-08
Last Modified: 2010-05-19

My code below does this,

-it reads rows from a db table row by row
-every row that is reads it gets the user info and send data to the user info via a method sendText()

Now, my current code takes very long to run especially when there's lots of rows. My requests are,

-Suggestion on how to load my rows of data into memory then send one by one from mem
-Suggestion on improving sql processing of this code.

 * Classname: Description: Generic class to send
 * readings to fortune teller subscribers
 * Author: melwong Date started: 11-Mar-2004
 * Version: 1.0


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.dbutils.DbUtils;


public class FortuneTellerReadingsDelivery {
      private Connection conn = null;
      public FortuneTellerReadingsDelivery(Connection conn) {
            this.conn = conn;

      public void processDelivery (String message) {
            int[] fortuneReadings = null;
            int groupId = 0;
            int serviceId = 0;
            int subkeyword1Id = 0;
            String lineFeed = "";
            String serviceKeyword = "";
            String fortuneCookie = "";
            String readings = "";
            String phoneNumber = "";
            String sqlStr = "";
            //Connection conn = null;
            PreparedStatement statement = null;
            ResultSet rs = null;
            KeywordProcessor keyPro = null;
            OutboundResponse outRes = null;
            FortuneTellerUtils util = null;

            keyPro = new KeywordProcessor();
            outRes = new OutboundResponse();
            util = new FortuneTellerUtils();
            lineFeed = System.getProperty("line.separator");
            //To get the subscribers table
            serviceId = keyPro.getServiceId(message);
            subkeyword1Id = keyPro.getSubkeyword1Id(message, serviceId);
            groupId = keyPro.getGroupId(subkeyword1Id);
            serviceKeyword = keyPro.getServiceKeyword(groupId);
            //Get one fortune cookie for all subscribers
            fortuneCookie = util.getFortuneCookie();

            try {
                  //conn = DriverManager.getConnection(DbConfig.dbUrl);

                  sqlStr = "SELECT DISTINCT phoneNumber FROM "
                              + serviceKeyword + "_fortuneteller_users"
                              + " WHERE inactive = 0";
                  statement = conn
                  rs = statement.executeQuery();

                  while ( {
                        phoneNumber = rs.getString(1);
                        fortuneReadings = util.getFortuneReadings();
                        //fortuneCookie = util.getFortuneCookie();
                        readings = "Love:" + fortuneReadings[0] + "%" + lineFeed
                                          + "Money:" + fortuneReadings[1] + "%" + lineFeed
                                          + "Attitude:" + fortuneReadings[2] + "%";
                        //Melvin: 02/08/05
                        outRes.processText(groupId, "Your today's fortune cookie: " + fortuneCookie + lineFeed + readings, phoneNumber);
                        //outRes.processText(groupId, fortuneCookie + lineFeed + readings, phoneNumber);


            } catch (SQLException e) {
                  throw new FortuneTellerException(this.getClass().toString(), e);
            } finally{


      public static void main(String args[]) {

            String message = "";
            String absPath = "";
            PropertyFileReader readProp = null;
            DbConfig initDbConfig = null;
            FortuneTellerReadingsDelivery fortuneTellerReadingsDelivery = null;
            Connection conn = null;

            if (args.length != 0) {

                  absPath = args[0];

                  for (int i = 1; i < args.length; i++) {
                        message += args[i];
                        message += " ";
                  message = message.trim();

                  System.out.println("absPath=" + absPath);
                  System.out.println("message=" + message);

            } else {
                              .println("FortuneTellerReadingsDelivery: ERROR: Invalid command line arguments.");
                  System.err.println("\tFortuneTellerReadingsDelivery: Usage:");
                              .println("\tjava FortuneTellerReadingsDelivery <full webapp path> <full SMS message>");
                              .println("\tE.g: java FortuneTellerReadingsDelivery C:\\tomcat\\webapps\\jm HI FC");

            //Read properties must process first, then initialize db
            readProp = new PropertyFileReader(absPath);
            //To initialize the db stuffs
            initDbConfig = new DbConfig();

            //To get URL for WAP Push and app full path
            try {
                  conn = DriverManager.getConnection(DbConfig.dbUrl);
                  fortuneTellerReadingsDelivery = new FortuneTellerReadingsDelivery(conn);
            } catch (SQLException e) {
                  throw new FortuneTellerException("FortuneTellerReadingsDelivery", e);
            } finally {
            //fortuneTellerReadingsDelivery = new FortuneTellerReadingsDelivery();




---EOF CODE-----
Question by:mwhc
    LVL 86

    Expert Comment

    You can start with:

    a. bind the parameter to the PS

    statement.setString(1, serviceKeyword);

    b. make sure there's an index on phone number column

    Does the below need to be called in the loop?

    >>fortuneReadings = util.getFortuneReadings();
    LVL 92

    Expert Comment

    Don't use a PreparedSTatement, just use a standard one using createStatement()

    And if your result set size is large then try adjusting the fetch size of the resukt set


    Author Comment

    Hi CEHJ,

    a. What is PS

    >>Does the below need to be called in the loop?
    >>fortuneReadings = util.getFortuneReadings();
    Yes is does.

    To objects,
    How do I adjust the fetch size of the result set?

    Author Comment


    What do you mean by "make sure there's an index on phone number column". This is my table structure,

    CREATE TABLE `just_fortuneteller_users` (
      `userId` int(11) NOT NULL auto_increment,
      `phoneNumber` varchar(100) NOT NULL default '',
      `registrationTime` datetime NOT NULL default '0000-00-00 00:00:00',
      `totalMessage` int(11) NOT NULL default '0',
      `inactive` int(11) NOT NULL default '0',
      PRIMARY KEY  (`userId`)

    Is this ok?

    LVL 86

    Accepted Solution

    >>a. What is PS


    >>What do you mean by "make sure there's an index on phone number column". This is my table structure,

    try the following in your CREATE TABLE

    phoneNumber varchar(100) NOT NULL, INDEX USING BTREE (phoneNumber)
    LVL 92

    Expert Comment

    > How do I adjust the fetch size of the result set?


    Author Comment


    I'm learning on indexing now. But I found out that indexing is useful especially in the WHERE statement. Will it be useful in my phoneNumber col since it's not in the WHERE statement?
    LVL 92

    Expert Comment

    doubt it, your better off indexing on inactive.
    LVL 86

    Expert Comment

    >>But I found out that indexing is useful especially in the WHERE statement.
    >>your better off indexing on inactive.

    Sorry mwhc, inactive is the correct column


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Understanding @ResponseBody annotation 5 36
    instanceof  operator in java 26 57
    network + 7 60
    countPairs challenge 7 39
    After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
    Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
    Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
    Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now