vendredi 29 janvier 2016

How can I speed up the execution of a large amount of SQL updates in Java (JDBC)

I'm trying to code a system for a large IRC channel (Twitch Channel)

One of the things I'm trying to do is log every user and give them points for being in the chat. For all intents and purposes the chat is just a large IRC channel. I'm retrieving the users in a big list from the twitch API, I put all the usernames in a large Array and running the following timer with a while loop:

        timer = new Timer(900000, new ActionListener() {
        public void actionPerformed(ActionEvent evt) {
            updating = true;
            try {
                Arraynumber = 0;
                TwitchBot.getDate();
                arrayused = false;
                System.out.println("trying to save users if theres enough stuff");
                while(Arraynumber < TwitchBot.words.length){
                    TwitchBot.CheckUpdateUserSQL(TwitchBot.words[Arraynumber]);
                    Arraynumber++;
                    System.out.println("updating database");
                }
            } catch (ClassNotFoundException e) {

                e.printStackTrace();
            } catch (InterruptedException e) {

                e.printStackTrace();
            }
        }
    });

As you can see it's a simple timer that picks the name from a String[] and runs every name through the script individually.

The updateuser looks like such:

public static void CheckUpdateUserSQL(String sqluser) throws ClassNotFoundException{
        selectSQL(sqluser);
        if (id == "thisuserdoesntexistforsure"){
            InsertSQL(sqluser);
        }
        else{
            int progress = CurrentTime - lastlogin;
            int totalprogress = progress + totaltime;
        if(progress < 60 && progress > 0){
            c15 = null;
            Statement stmt = null;
                if(isonline == 1) {
                    coins = progress / 4;
                }
                else{
                    coins = progress / 5;
                }
                int coinsincrease = (int) Math.ceil(coins);
                int coinstotal = coinsamount + coinsincrease;
              Class.forName("org.sqlite.JDBC");
              try {
                c15 = DriverManager.getConnection("jdbc:sqlite:users.db");
                c15.setAutoCommit(false);
                stmt = c15.createStatement();
                String sql = "UPDATE USERS set TOTALTIME = " + totalprogress + " where NAME='" + sqluser + "';";
                stmt.executeUpdate(sql);
                c15.commit();
                String sql2 = "UPDATE USERS set LASTLOGIN = " + CurrentTime + " where NAME='" + sqluser + "';";
                stmt.executeUpdate(sql2);
                c15.commit();
                String sql3 = "UPDATE USERS set TOTALCOIN = " + coinstotal + " where NAME='" + sqluser + "';";
                stmt.executeUpdate(sql3);
                c15.commit();
                String sql4 = "UPDATE USERS set ISONLINE = 0 where NAME='" + sqluser + "';";
                stmt.executeUpdate(sql4);
                  c15.commit();
                  stmt.close();
                  c15.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            }
            else {
                Connection c = null;
                Statement stmt = null;
                try {
                    c = DriverManager.getConnection("jdbc:sqlite:users.db");
                     c.setAutoCommit(false);
                        stmt = c.createStatement();
                        String sql2 = "UPDATE USERS set LASTLOGIN = " + CurrentTime + " where NAME='" + sqluser + "';";
                        stmt.executeUpdate(sql2);
                        c.commit();
                          stmt.close();
                          c15.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }

            }

        }
    }

This code checks whether an user exists. (using the select method, which is as concise as I can get it, it only search for an username and returns the id, which will be 'thisuderdoesntexistforsure' if nothing returns)

If the user exists it will run the code to calculate their online time and the increase in online time and points since the last time they visited. Then updates the code. If they were not online or if the time somehow returns a negative value (or one that's too high) it will instead only update the timestamp and skip the rest of the updates. This makes sure that users who leave for a day don't just get 1.400 minutes of online time when they log on five minutes the next day.

Anyway. My question is; How can I trim it down? I'm running into an issue where it will take 6 minutes to update the entire userlist. having 2000 users online is not rare and it would take 2,000 loops through that while loop to update them all. The program is updating more often then not. I've tried cutting down the code to be as condensed as possible, but I have no idea where to start to speed things up.

Sorry if I'm coming over as moronic, I'm relatively new to SQL and this is my biggest project yet in JAVA.

Aucun commentaire:

Enregistrer un commentaire