Tim Holloway wrote:Pfft. Only 150000 rows? And all you want to do is "UPDATE X SET colname = colname + 1"?
Hopefully you've made that operation transactional so that no one attempts to read/update colname while the update is actually going on, and that can potentially delay other users. But 150000 rows on modern hardware and software isn't likely to take all that long. I do much worse routinely.
Tim Holloway wrote:TWO HOURS!!!???
I think you need to have a serious look at that server and that database. I wouldn't expect it to take more than 10 minutes absolute max. Two minutes more like it. Check the CPU and I/O stats and run an EXPLAIN.
I've done multi-gigabyte database operations in far less than two hours.
It's not that I expect everything I do to operate instantaneously - I used to have a system that when cranked up took about 10 hours to run through. But it was doing complex scoring algorthms that required a lot of webapp-side work. A simple "Add 1 to X" can be done entirely within the database server in a single SQL command and that shouldn't take nearly so long.
I know that the current statndard business model encourages brute-force solutions over analysis and tuning, but two hours sounds seriously out of whack to me.
Tim Holloway wrote:Also, transactional isn't the same thing as batching. If you break the operation into multiple transactions, you're going to have data integrity concerns just as much (in most cases) as if you hadn't done transactions at all.
Tim Holloway wrote:Here's why I worry about your database. I made a simple table of 150K rows. Here's how long it took to add a value to a column:
It didn't take 2 hours. It didn't take 2 minutes. In fact, it didn't even take 2 seconds! And this isn't some hyped-up supermachine I'm using. It's about 6 years old and the database server is just one of several VMs running inside the physical box.
Sergei Prosvirnin wrote:such a long time is a consequence of network latency
Tim Holloway wrote:A simple "Add 1 to X" can be done entirely within the database server in a single SQL command and that shouldn't take nearly so long.