Re: [sqlite] multiple inserts
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Kodok Márton > Sent: Monday, August 11, 2008 2:21 PM > To: General Discussion of SQLite Database > Subject: [sqlite] multiple inserts > > Hi, > > Does SQLite accepts multiple insert? > insert into table (col1,col2) values (val1,val2), > (val3,val4), (val5,val6) No. > If not, how can I speed up large inserts? > eg: 1000 rows > Here is a quick shot: Just use a transaction surrounding your INSERTs, and use prepared statements and parameter bindings to prevent multiple parsing. - char* errmsg = NULL; sqlite3* db = NULL; db = open( ) // begin transaction sqlite3_exec( db, "BEGIN TRANSACTION", NULL, NULL, ); // prepare for multiple inserts sqlite3_stmt* stmt = sqlite3_prepare_v2( "INSERT INTO T1 (C1, C2, C3) VALUES (?, ?, ?)") -for-each-row // read doc for sqlite3_bind_* carefully!! sqlite3_bind_int( stmt, 1, someIntVariable); sqlite3_bind_text( stmt, 2, -1, "hello", SQLITE_STATIC); sqlite3_bind_text( stmt, 3, -1, pzSomeString, SQLITE_TRANSIENT); sqlite3_step(stmt); // executes the INSERT sqlite3_reset(stmt); // important! Clears the old values, makes the statement accept new parameters. -end-for-each sqlite3_finalize(stmt); // clean up prepared statement // begin transaction sqlite3_exec( db, "COMMIT", NULL, NULL, ); // COMMIT all dirty pages at once - Regards, Stefan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Loading a existing database 100% into memory
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Brown, Daniel > Sent: Thursday, August 07, 2008 12:27 AM > To: General Discussion of SQLite Database > Subject: [sqlite] Loading a existing database 100% into memory > > Good afternoon list, > > I would like to load my current database file completely into > memory, mostly as an experiment to check SQLite's maximum > memory footprint, however searching through the documentation > I can only find references about how to create new databases > that are completely memory resident. > Is there a way to do this? I'm currently using the SQlite > console application for my testing if that makes a difference. > What do you mean by loading it into memory? If you want to dump the on-disk tables into memory and also want to check the memory footprint used for caching try this: Open the :memory: database and attach the on-disk database with ATTACH filename.db AS filename Then do a CREATE TABLE tableName AS SELECT * FROM filename.tableName On each table in the file, thus creating an in-memory copy of the DB and having done a select on each table (i.e. you'll see how much cache in memory will be used, etc.) You can enumerate all tables in a your on-disk-file in the mentioned scenario by doing a "SELECT tbl_name FROM filename.sqlite_master WHERE type = 'table'". Best regards, Stefan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] General help, a bit OT
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of cstrader > Sent: Tuesday, July 15, 2008 2:28 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] General help, a bit OT > > Oh, sorry, the example was just an example. The calculations > I need to make on on ColC cannot be done within SQLite. > > > Why does SELECT ColA, ColB, ColA+ColB AS ColC FROM tab1; not work? > > AFAIK, the current .NET provider for SQLITE supports custom functions implemented in .NET. Maybe this will work? SELECT ColA, ColB, custCLRfct(ColA, ColB) AS ColC FROM tab1; You might also want to implement the function in C for speed improvements. Greetings, Stefan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] General help, a bit OT
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of cstrader > Sent: Tuesday, July 15, 2008 2:03 PM > To: General Discussion of SQLite Database > Subject: [sqlite] General help, a bit OT > > A bit off topic, but perhaps someone could direct me. > > I'm in vb.net > > I have to read thousands of tables and operate on each one in > sequence. I need to do this fast. > > I want to read the data from 6 stored columns and many rows > from each table. > > Once the table is read, I need to create derived columns > (e.g. (new) Column C = column A + ColumnB). Why does SELECT ColA, ColB, ColA+ColB AS ColC FROM tab1; not work? Greetings, Stefan Szomraky. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
