Ask the Expert

Methods to quicken update of 30-column table

I am updating 80% of the rows in a 30-column (6 VC [4000] and a blob) table. I have the STD 10% pctfree set up. I am getting fair throughput, but have been tasked with making it faster. So far, I haven't been able to improve on my original design. I've looked at what indexes are affected, used preparedStatements, batching (4000 updates at a time, each update affecting 0-N rows), set auto-commit off and switched to using the thin driver. Can you list other areas that I should explore?

    Requires Free Membership to View

Are you updating one row at a time? If so, your biggest payoff would probably be to modify the SQL to modify multiple rows at once. Otherwise, the things you've done are all worthwhile. Make sure that you're not parsing each statement more than once (I assume that using preparedStatements takes care of that) and that your access path to the rows to be updated is efficient (in this case, you probably don't want to be using an index). Have you traced the updates to see the breakdown of elapsed time? If not, that should be your next step. Get statistics on elapsed time, CPU time and wait time and proceed from there.

This was first published in January 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: