aspose file tools*
The moose likes Performance and the fly likes Code consumes ~50% CPU Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Performance
Bookmark "Code consumes ~50% CPU" Watch "Code consumes ~50% CPU" New topic
Author

Code consumes ~50% CPU

Ronald Castillo
Ranch Hand

Joined: Apr 16, 2011
Posts: 47

Hi guys,

This time I having a doubt regarding performance. I have a code which reads records from a ODBC DataSource and then inserts it on a MySQL database.
The thing is, when Java is looping thru the records from the ODBC DS, it takes A LOT of CPU (around 50%) and I'm concerned whether this is normal for this type of operations or not.

This is the code I'm using:


Is it supposed to use that much CPU or is there something I can do to improve performance.

PD: The Datasource is a VisualFoxPro DBF db.

(Edited to reduce the excessive width: Paul C)
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

Normally when you have CPU-bound code (which I guess is what you have there), you want it to use as much CPU as possible so that it can
finish sooner. But perhaps you have a dual-core machine, which would explain why it's only using 50% of the CPU. You would have to use more
than one thread if you wanted to use both cores for that computation.
fred rosenberger
lowercase baba
Bartender

Joined: Oct 02, 2003
Posts: 11311
    
  16

What, exactly, is wrong with it using 50% of the CPU? Do you want it to use less? I would think that would then result in it taking longer to execute. Is that a trade off you want to make - using only 10% of the CPU, but then taking 5 times longer to run?

The general idea is that you need to decide what your performance requirements are FIRST, see if you meet them, and then adjust accordingly. Saying "i don't want my program to use 50% of the CPU" without having a reason why is really just silly.


There are only two hard things in computer science: cache invalidation, naming things, and off-by-one errors
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2374
    
  28

How big is your days loop? Also how many records returned from your localTemplate?

Generally speaking making one big ass query (yes big ass query is the technical term) for inserting lot of rows into a database is not reccomended.
a) First of all, if you have tens of thousands, or maybe millions of rows, you are building this big ass string in memory. Depending on how big that string is, it might be taxing GC, which will have a side effect of increased CPU consumption.
b) although StringBuffer is much better that String when you are concat-ing, it's not really a walk in the park. The JVM has to manage the buffers, grow them blah blah.
c) You will send this big ass query to the database, which will need to parse it and the execute it.


I would suggest using Prepared statement to insert rows inside your loop. You can create the prepared statement outside the loop and then execute it inside the loop. If you want to improve performance you can insert records in batches. It's better to make the database parse a small parameterized Insert query once, and then add rows by changing the parameters. The JPA layer and JDBC driver is going to do some caching, and in most cases they are going to do a better job than you will

I don't think you will reduce your CPU usage, but you will probably increase your throughput.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

Well, he IS using a StringBuilder to construct the big-ass query, so we don't have the dreaded String concatenation problem to deal with. But everything else you said about inserting into the output database one record at a time in batches... +1 to that.

I would have tried to use an "INSERT INTO... FROM SELECT ..." statement, or something like that, so that my code didn't even have to handle the data at all. But perhaps FoxPro doesn't support that kind of query, or perhaps it would have been an unmaintainable mess if it could even be written at all.
Ronald Castillo
Ranch Hand

Joined: Apr 16, 2011
Posts: 47

Paul Clapham wrote:Well, he IS using a StringBuilder to construct the big-ass query, so we don't have the dreaded String concatenation problem to deal with. But everything else you said about inserting into the output database one record at a time in batches... +1 to that.

I would have tried to use an "INSERT INTO... FROM SELECT ..." statement, or something like that, so that my code didn't even have to handle the data at all. But perhaps FoxPro doesn't support that kind of query, or perhaps it would have been an unmaintainable mess if it could even be written at all.


The database that I'm inserting the data to is a MySQL database.

How big is your days loop? Also how many records returned from your localTemplate?

About 1.5k records PER day
Generally speaking making one big ass query (yes big ass query is the technical term) for inserting lot of rows into a database is not reccomended.

I need a ALL or NOTHING insert (at least per day), maybe I could have used a transaction, but I'm not sure how the performance is when doing a transaction + insert in a preparedstatement
c) You will send this big ass query to the database, which will need to parse it and the execute it.

I ran a query to get data from 200 days worth of data, at about ~1.5k records a day, out of all those records just 85k were unique, the query just took 315seconds to execute on the MySQL server (remote).
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 861
My experience is that transactions of many inserts in batch are faster than each insert committing by itself. By a wide margin sometimes. You should test this at least at the day level being as you want this behaviour anyway. (I should add I didn't read the whole thread before adding this contribution, so take my comments with that fact in mind


http://www.jamonapi.com/ - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16058
    
  21

Paul Clapham wrote:Normally when you have CPU-bound code (which I guess is what you have there), you want it to use as much CPU as possible so that it can
finish sooner. But perhaps you have a dual-core machine, which would explain why it's only using 50% of the CPU. You would have to use more
than one thread if you wanted to use both cores for that computation.


Well, actually, you want productive use of your hardware, but be careful here. As an IBM rep once pointed out to an old boss of mine, 100% resource utilization means that the first unexpected event that happens is likely to make the entire system crash. In extreme cases, the whole box, not just the app. The business conceit of "giving 110%" doesn't fly when you have the laws of physics to deal with.

Regardless, trying to cram everything into an enormous string just to avoid using database transactions is false economy.

1. It keeps the memory from being used by anything else.

2. If you ARE using more resources than you really posesses (e.g., virtual memory), you're at risk for memory thrashing. Which can burn TONS of CPU, and yet accomplish nothing.

3. You putting all the work on the database client when you might benefit offloading to the database server.

That #3 is especially important. Database servers are often much more powerful (when it comes to data handling) than standard server machines. Also, because transactions are such a critical function, any serious SQL DBMS is going to have had a lot of design effort spent on making them as efficient as possible.

Effectively, a transaction means that all the data is transferred from the client to the server and stored, but until the "commit" is done, it's only stored in potentio. The commit flips the whole batch over to reality at once. How long "at once" takes varies with the operation and the architecture.

Customer surveys are for companies who didn't pay proper attention to begin with.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Code consumes ~50% CPU