[sqlite] A faster way to insert into a keyless table?
Greetings! I am working on updating an application that has been around for years, originally written by someone who knew SQLite exists but had very little idea of how to use it. The application monitors OPC information for 124 bases, with 7-10 tags per base. (Don't worry about what a base is.) Each base has a separate SQLite database file associated with it. Every minute, I have to write the latest tag value for each tag into the correct database. Here is the create statement for the table into which I am writing data: CREATE TABLE trend_data( tag_key integer, value integer, value_timestamp datetime ); There is no primary key specified for this table. The application is written in C#, and uses a .Net provider for SQLite. The code to insert tag data is very crude. I build an INSERT query as a string, and then I call the database object's Execute() method. The average time to update a base is half a second. Since I've got 124 bases, it takes almost exactly a minute to update each one. But I would like the entire update process, for all bases, to be repeated every minute. Therefore, I'd like to get the update time per base down to at most a quarter second. What would be the best way to speed this up? Thanks very much! RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A faster way to insert into a keyless table?
On 14 Feb 2012, at 7:01pm, Rob Richardson wrote: The application is written in C#, and uses a .Net provider for SQLite. The code to insert tag data is very crude. I build an INSERT query as a string, and then I call the database object's Execute() method. The average time to update a base is half a second. Does this include Non-SQLite-related operations ? Does this include the time to open and close the database connection ? How is the computer accessing the database files: are they on the boot disk, or across a network or what ? If it's just the time to execute the execute, it's ridiculously long. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A faster way to insert into a keyless table?
On Feb 14, 2012, at 8:01 PM, Rob Richardson wrote: What would be the best way to speed this up? wrap all your inserts in one transaction. commit at the end. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A faster way to insert into a keyless table?
Many thanks for your response. That solved the problem. Now I update data for all 124 bases in about eight seconds. RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Petite Abeille Sent: Tuesday, February 14, 2012 2:18 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] A faster way to insert into a keyless table? On Feb 14, 2012, at 8:01 PM, Rob Richardson wrote: What would be the best way to speed this up? wrap all your inserts in one transaction. commit at the end. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
