StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<blockquote> <p>So, it appears that sqlite treats this update as a transaction, which does not make much sense in this case because all rows are independent. </p> </blockquote> <p>No, that actually makes perfect sense, because you're not executing multiple, independent updates. You're executing a single update statement. The <a href="http://www.sqlite.org/lang_transaction.html" rel="nofollow">fine manual</a> says</p> <blockquote> <p>No changes can be made to the database except within a transaction. Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last query finishes.</p> </blockquote> <p>If you can determine the range of keys involved, you can execute multiple update statements. For example, if a key is an integer, and you determine the range to be from 1 to 1,000,000, you can write code to execute this series of updates.</p> <pre><code>begin transaction; UPDATE T set C1 = calculation(C2) where C1 is NULL and your_key between 1 and 100000; commit; begin transaction; UPDATE T set C1 = calculation(C2) where C1 is NULL and your_key between 100001 and 200000; commit; </code></pre> <p>Other possibilities . . .</p> <ul> <li>You can sleep for a bit between transactions to give other queries a chance to execute. </li> <li>You can also time execution using application code, and calculate a best guess at range values that will avoid timeouts and still give good performance.</li> <li>You can select the keys for the rows that will be updated, and use their values to optimize the range of keys.</li> </ul> <p>In my experience, it's unusual to treat updates this way, but it sounds like it fits your application.</p> <blockquote> <p>But I cannot start a new transaction for each row, can I?</p> </blockquote> <p>Well, you <em>can</em>, but it's <em>probably</em> not going to help. It's essentially the same as the method above, using a single key instead of a range. I wouldn't fire you for testing that, though.</p> <hr> <p>On my desktop, I can insert 100k rows in 1.455 seconds, and update 100k rows with a simple calculation in 420 ms. If you're running on a phone, that's probably not relevant.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload