On 2015-09-30 12:31 PM, Deepak Hegde wrote: > On Wednesday 30 September 2015 03:30 PM, R.Smith wrote: >> >> On 2015-09-30 11:46 AM, Deepak Hegde wrote: >>> Hi All, >>> >>> I have a to copy entry from on database to another which have the >>> similar structure. >>> >>> So I am using the method of ATTACH the DB and INSERT statement to insert >>> the 200 entries at a time. >>> I have observed that as the entries in the copied database increases >>> event though I am inserting 200 entry only, time for insertion keeps on >>> increasing.//... >> The time taken to insert rows into a database is a function of the >> database size with several factors adding to it. >> >> Firstly, you need to use transactions to make the inserts faster if >> you are going to do multiple inserts. Secondly, it will be much faster >> if you insert using a SELECT query (as you do), but it can be the >> actual SELECT that takes longer to execute since I assume the >> original/source DB would have grown too. See taht you have good index >> for fast querying on the source DB. > [DEEPAK]: If the database reading have only 200 entry and I am doing > the same procedure than also same behavior is seen. So I thought Time > taken is for insertion and not for the reading.
This was merely a possibility, but I agree - your check ruled it out as one of the causes. >> The time taken to insert items into a database (with already many >> items in it) is mostly due to needing to expand the indices of the >> target DB. If you have no Index at all (though you will still likely >> have the hidden rowid index) then theoretically the database can grow >> without using much time. For every Index you add, the time taken to >> insert will go up because the DB has to add and re-organize the B-Tree >> used for every index (In the standard case). > [DEEPAK]: I had attached the DB structure but that mail size was more and got > rejected This forum does not usually allow attachments, although a moderator can OK it, so we did receive your attached image. > There is no index created externally by me. But there > are some index internally created by sqlite, this seems to be due to > unique constraint on column on the certain table like sqlite_autoindex_ALBUM_1 > This constrain is not present for the table AUDIO for which insertion is > happening, so for my understanding this index will not be invoked while > inserting. Yes the UNIQUE constraint implies simply an Index that does not allow duplicated entries, which makes it even more time-consuming than a usual index because there is a lookup step for duplication in addition to the B-Tree rejig. This however isn't usually a large time-consumer. > So is rowid index will increase the time to this extent if the entry > count goes high? Not really, the Unique constraint will have a larger effect, but even that shouldn't take as long. Nvm, just saw your reply re. the Trigger. Yes that will of course increase time a lot - my apologies, It's the obvious thing - I should have mentioned this earlier. Cheers, Ryan
