Hi Ryan, Please find my reply below. Thanks a lot.
Thanks and Regards Deepak 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.//... > > Hi Deepak - (Not related to Mr. Chopra I trust?) [DEEPAK]: No sir. > > 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. > > 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. 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. So is rowid index will increase the time to this extent if the entry count goes high? > > Some bit of time gets lost on the file-handling of large files once > they grow significantly, but that is usually rather negligible > compared to the Indexing factors. > > HTH, > Ryan > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
