Hi Ryan, Since you mentioned index, I started looking into other constrains and logic i had on DB. which made me realize about the trigger. Thanks a lot.
Thanks and Regards Deepak On Wednesday 30 September 2015 04:47 PM, R.Smith wrote: > > > 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 > > >
