Hi All, I have a strange performance problem with SQLite and I'm hoping someone here may have an explanation.
My application uses an SQL script with several hundred statements to define the database schema. This script creates the tables and triggers, and populates several small tables with predetermined values. We have a second script that creates a number of temporary tables, views, and triggers based on the permanent tables. To create a new file we open an SQLite database and then execute the first script to create the permanent tables, and then execute the second script to create the temporary tables. To open an existing file we simply open the existing SQLite database and execute the second script to create the temporary tables. These scripts are both executed inside transactions to minimize the execution time. At this point both of the database's should be in the same state, regardless of how it was opened. We then execute several thousand SQL SELECT and INSERT statements to populate the permanent tables based on user commands. Some of these inserts run triggers that update permanent tables based on updates to the temporary views. This is where we are seeing behavior that is very puzzling. The exact same set of SQL statements can be executed after opening an existing file, or creating a new file. This SQL executes in about one half the time if a previously existing database is opened (so only the second script executed prior), verses the case of opening an empty database (where both scripts are executed prior). FYI, the two scripts execute in less than 1 second each. The SQL to add user data executes in about 5 seconds after an opening an existing database, and about 10 seconds after creating a new database. We have a very simple workaround, simply closing the database after executing the first script, and reopening it before executing the second script. The new file case is now exactly like the open file case where the user didn't cause any inserts before the first close (he simply initialized the tables). Now all user updates execute in about 5 seconds. I didn't expect this 2 to 1 speed difference, and have no explanation for why it occurs. Does anyone else have an idea what might be happening? Dennis Cote --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
