Re: [sqlite] methods to improve insertion speed with SQLite
Hello Thomas, you are right in principle: The gain should be not too great. However, the number of calls to sqlite3_bind_text is * of columns>, which is *very* high. Also, i suspect that sqlite3_bind_text makes a copy of the text i pass, which could be eliminated too. Or am i wrong there? Martin Thomas Briggs wrote: However, I would very much like a "bulk insert" - call to sqlite (Oracle OCI does this, for example), where i can put many (thousands) of records into the database with one call. Is there any chance of something like this ever to be added to sqlite? I can't speak authoritatively, but I don't see what the impact would be. Adding rows in bulk, with other databases, is mostly about transferring the data more optimally and/or avoiding the SQL processing engine. Both of those problems have already been solved by SQLite - you can't get any more optimal than intra-process communication, and given a prepared INSERT statement that is executed repeatedly, there is no SQL processing engine involved. It might be convenient to be able to provide arrays of inputs to a prepared statement, but the only thing you'd gain performance-wise is eliminating the function call overhead of all calls past the first, so even that doesn't provide any serious benefit. -Tom
RE: [sqlite] methods to improve insertion speed with SQLite
> However, I would very much like a "bulk insert" - call to > sqlite (Oracle > OCI does this, for example), where i can put many (thousands) > of records > into the database with one call. Is there any chance of > something like > this ever to be added to sqlite? I can't speak authoritatively, but I don't see what the impact would be. Adding rows in bulk, with other databases, is mostly about transferring the data more optimally and/or avoiding the SQL processing engine. Both of those problems have already been solved by SQLite - you can't get any more optimal than intra-process communication, and given a prepared INSERT statement that is executed repeatedly, there is no SQL processing engine involved. It might be convenient to be able to provide arrays of inputs to a prepared statement, but the only thing you'd gain performance-wise is eliminating the function call overhead of all calls past the first, so even that doesn't provide any serious benefit. -Tom
Re: [sqlite] methods to improve insertion speed with SQLite
On 9/14/05, Rajan, Vivek K <[EMAIL PROTECTED]> wrote: > > > I am wondering if there techniques/tricks which can improve the total > insertion speed of my application. Any suggestions/feedback? > Remove indexes from the tables you're inserting into. If you need that data for queries later you can create a 'data warehouse' . You later copy the data you've collected to separate tables, index it as necessary, then you can do your queries/reporting against that table. Doesn't work for everyone, but it's sometimes very useful when up to the second data isn't required. --- The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264
Re: [sqlite] methods to improve insertion speed with SQLite
Hello You can also speed up the inserts when creating the index after the inserts. To check the constraints you could use QDBM (http://qdbm.sourceforge.net/). Rajan, Vivek K wrote: Hello- In my application, I perform large number of insertions (~100K) to create a SQLite database. The method which I currently have is following: - Start a transaction - Insert into database (~100K inserts) - During the insert process, I also have to query the already inserted elements in the database to establish foreign-key constraints in my schema. My schema is like a graph structure, with many foreign keys relationships - Close transaction I am wondering if there techniques/tricks which can improve the total insertion speed of my application. Any suggestions/feedback? Vivek
Re: [sqlite] methods to improve insertion speed with SQLite
Hello Vivek, I have a very similar application, without the foreign key constraints, however. If you use sqlite3_prepare() once for your statement, then sqlite3_bind_...() with every call, and if you wrap all the inserts int one transaction (seems that you do), your speed schould be optimal. However, I would very much like a "bulk insert" - call to sqlite (Oracle OCI does this, for example), where i can put many (thousands) of records into the database with one call. Is there any chance of something like this ever to be added to sqlite? Martin Rajan, Vivek K schrieb: Hello- In my application, I perform large number of insertions (~100K) to create a SQLite database. The method which I currently have is following: - Start a transaction - Insert into database (~100K inserts) - During the insert process, I also have to query the already inserted elements in the database to establish foreign-key constraints in my schema. My schema is like a graph structure, with many foreign keys relationships - Close transaction I am wondering if there techniques/tricks which can improve the total insertion speed of my application. Any suggestions/feedback? Vivek
[sqlite] methods to improve insertion speed with SQLite
Hello- In my application, I perform large number of insertions (~100K) to create a SQLite database. The method which I currently have is following: - Start a transaction - Insert into database (~100K inserts) - During the insert process, I also have to query the already inserted elements in the database to establish foreign-key constraints in my schema. My schema is like a graph structure, with many foreign keys relationships - Close transaction I am wondering if there techniques/tricks which can improve the total insertion speed of my application. Any suggestions/feedback? Vivek
