Faster way to use data from a String array?

Currently, I have a table being read from like so:

        ps = con.prepareStatement("SELECT * FROM testrow;");
        rs = ps.executeQuery();
        rs.next();
        String[] skills = rs.getString("skills").split(";");
        String[] skillInfo;
        for (int i = 0; i < skills.length; i++) {
            skillInfo = skills[i].split(",");
            newid.add(Integer.parseInt(skillInfo[0]));
            newspamt.add(Byte.parseByte(skillInfo[1]));
            mastery.add(Byte.parseByte(skillInfo[2]));
        }
        rs.close();
        ps.close();

The information is saved to the database by using StringBuilder to form a string of all the numbers that need to be stored, which would be in the format of numbe开发者_如何学Gor1,number2,number3;

I had written a test project to see if that method would be faster than using MySQL's batch method, and it beat MySQL by roughly 3 seconds. The only problem I'm facing now is when I go to read the information, MySQL completes the job in a few milliseconds, where as calling the information using String[] to split the data by the ";" character, and then also using String[] to split information within a loop by the "," character, takes about 3 to 5 seconds.

Is there anyway I can reduce the amount of time it takes to load the data using the String[], or possibly another method?

---

Do not store serialized arrays in database fields. Use 3NF?

---

Do you read the information more often than you write it ? If so (most likely) then optimising the write seems to be emphasising the wrong end of the operation. Why not store the info in separate columns and thus avoid splitting (i.e. normalise your data)?

If you can't do that, can you load the data in one thread, and hand off to another thread for splitting/storing the info. i.e. you read the data in one thread, and for each line, pass it through (say) a BlockingQueue to another thread that splits/stores.

---

in the format of number1,number2,number3

consider normalising the table, giving one number per row.

---

String.split uses a regular expression for its algorithm. I'm not how it's implemented, but the chance is that is quite cpu heavy. Try implementing your own split method, using a char value instead of a regular expression.

---

Drop the index while inserting, that'll make it faster.

Of course this is only an option for a batch load, not for 500-per-second transactions.

---

The most obvious alternative method is to have a separate skills table with rows and columns instead of a single field of concatenated values. I know it looks like a big change if you've already got data to migrate but it's worth the effort for so many reasons.

---

I recommend that instead of using the split method, you use a precompiled regular expression, especially in the loop.