Faster way to use data from a String array?

Question

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 number1,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?

Krule · Accepted Answer · 2011-01-14 13:54:28Z

3

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

answered Jan 14, 2011 at 13:54

Krule

6,4963 gold badges36 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brian Agnew · Accepted Answer · 2011-01-14 13:59:46Z

1

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.

edited Jan 14, 2011 at 13:59

answered Jan 14, 2011 at 13:54

Brian Agnew

273k38 gold badges342 silver badges443 bronze badges

1 Comment

PuppyKevin Over a year ago

It saves more than it's written, but I do need the saving part to happen as quick as possible. Previously, I did have the skills table setup to have one column for each of the numbers, but when storing to that table, it just took too long to perform.

Raedwald · Accepted Answer · 2011-01-14 13:54:18Z

1

in the format of number1,number2,number3

consider normalising the table, giving one number per row.

answered Jan 14, 2011 at 13:54

Raedwald

49.2k49 gold badges162 silver badges250 bronze badges

Comments

Rasmus Franke · Accepted Answer · 2011-01-14 13:56:00Z

1

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.

answered Jan 14, 2011 at 13:56

Rasmus Franke

4,5348 gold badges48 silver badges64 bronze badges

Comments

Satya · Accepted Answer · 2011-01-14 13:54:32Z

0

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.

answered Jan 14, 2011 at 13:54

Satya

4,49824 silver badges30 bronze badges

Comments

biziclop · Accepted Answer · 2011-01-14 13:56:02Z

0

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.

answered Jan 14, 2011 at 13:56

biziclop

50k12 gold badges80 silver badges108 bronze badges

Comments

Navi · Accepted Answer · 2011-01-14 13:55:47Z

-1

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

answered Jan 14, 2011 at 13:55

Navi

8,7764 gold badges36 silver badges32 bronze badges

Collectives™ on Stack Overflow

Faster way to use data from a String array?

7 Answers 7

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related