• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Code consumes ~50% CPU

 
Ranch Hand
Posts: 47
MySQL Database PHP Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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)
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
lowercase baba
Posts: 13089
67
Chrome Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Rancher
Posts: 2759
32
Eclipse IDE Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 47
MySQL Database PHP Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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).
 
Ranch Hand
Posts: 862
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Saloon Keeper
Posts: 27762
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic