I mean in real sutiations IDs are near to random and as I know the performance is not as bad as mine. Why this happens? I think it may be somehow related to caching, but I don't know if it's normal. It improves performance a little, maybe 4 times faster, but for random IDs it's still very slow. I also tried using prepared statements (executemany in python). I also noticed the bigger range of IDs I choose the slower it becomes, but IDs from that range are always exist in the table. Then the results become disappointing: Speed: 514.18/sec If I use a random ID c.execute('UPDATE users SET username = ? WHERE id = ?', ('random', random.randint(0, 1000000000))) Where inc is increased by 1 after every UPDATE, then the results would be: Speed: 233977.17/sec With c.execute('UPDATE users SET username = ? WHERE id = ?', ('random', inc)) If I replace c.execute('UPDATE users SET username = ? WHERE id = ?', ('random', 555)) In the example above the ID is constant and equals to 555 (the value doesn't matter, the speed is the same also for big values of ID).įor this example the speed is (UPDATEs per second): Speed: 376665.88/sec The value of username doesn't affect on performance, so let it be always random. The script for experiments are: import sqlite3Ĭonn = nnect('database.sqlite')Ĭ.execute('UPDATE users SET username = ? WHERE id = ?', ('random', 555)) Then I started experimenting with the script and found that UPDATEs are much faster when IDs are a constant or incrementing for example. ID is a primary key, so it must be indexed automatically and it should not slow down UPDATEs, right? Much slower than it has to be for UPDATE (not even INSERT). I realized that it worked extremely slow. I've written a simple python script for this purpose. Of course, if there is such ID in the table (do not insert a new record if there is no such user with this ID, just UPDATE existing one). I need to write all the names from the file into the database using corresponding IDs. I have a large database and a file with ID-Name pairs in each line, where ID corresponds to the primary key of the table in the database.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |