tcl database insertion and retrieval slow

I am new to tcl. I have connected to an SqLite Database.

I have around 100,000 records which i want to insert into database after computation. I use the following command to insert records into database 100,000times.

I am sure i am doing something wrong here. What would be the appropriate way to do this?

For insertion:

db eval {i开发者_如何转开发nsert into table values(value1,value2,value3,value4)} 

For retrieval i have to do select operation at least 1000 times, things are terribly slow:

db eval {select x as x, y as y from table} {
   set z  [expr $z + $x + $y]
}

By default each insertion is a single transaction. This is very slow. You can group your insertions together into single transactions of, say, 100 insertions, and speed things up greatly.

db eval { BEGIN TRANSACTION }

... do some insertions ...

db eval {  END TRANSACTION }

or

db transaction {

... do some insertions ...

}

Out of the box, SQLITE is extremely safe, but quite slow. If you know what you are doing, and are willing to risk db corruption on a disk crash, then there are several optimization you can do that provide spectacular speed improvements.

In particular:

• Switch off synchronization ( PRAGMA synchronous = OFF; )
• Group writes into transactions
• Index tables
• Use database in memory

If you have not explored all of these, then you are likely running many times slower than you might.

I can't speak to the sqlite part of the question because I haven't used sqlite in a few years, but one cause of performance degradation is your expr statement. You need to brace the arguments to expr, which should speed up that portion of your loop considerably:

set z [expr {$z + $x + $y}]

Yes, doing single inserts from Tcl into an sql table is not the fastest way to accomplish what you want.

To speed things up you should write the table you want into an external file and slurp the data in with a single db statement.

You'd write the data out like so:

set fh [open temp_file w]
set rowid 0
# loop
  puts $fh [join [list $value1 $value2 $value3 $value4] \t]
# end loop
close $fh

Then, using the copy method, you can read everything in one shot:

db copy replace values temp_file

And as for calculating a sum, I don't know why you're not using sql itself to do the summation. There are many examples on the web of how to do that. Here are a couple. I imagine your sql statement would be something like:

db select sum(x+y) from table

or possibly

db select sum(x+y) as z from table