tcl数据库插入和检索速度慢

发布于 2024-11-01 09:58:51 字数 382 浏览 6 评论 0原文

我是tcl新手。我已连接到 SqLite 数据库。

我有大约 100,000 条记录，我想在计算后将其插入数据库中。我使用以下命令将记录插入数据库 100,000 次。

我确信我在这里做错了什么。执行此操作的适当方法是什么？

对于插入：

db eval {insert into table values(value1,value2,value3,value4)}

对于检索，我必须执行至少 1000 次选择操作，速度非常慢：

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

原文

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 {insert 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]
}

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

那片花海 2024-11-08 09:58:52

默认情况下，每个插入都是一个事务。这非常慢。您可以将插入分组为单个事务，例如 100 个插入，从而大大加快处理速度。

db eval { BEGIN TRANSACTION }

... do some insertions ...

db eval {  END TRANSACTION }

或者

db transaction {

... do some insertions ...

}

开箱即用的 SQLITE 非常安全，但速度相当慢。如果您知道自己在做什么，并且愿意冒磁盘崩溃时数据库损坏的风险，那么您可以进行一些优化，以提供惊人的速度提升。

特别是：

关闭同步（ PRAGMA synchronous = OFF; ）
将写入分组到事务
中索引表
使用内存中的数据库

如果您还没有探索所有这些，那么您的运行速度可能会比实际情况慢很多倍。

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 }

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.

回复收藏 0 原文

╄→承喏 2024-11-08 09:58:52

我无法回答问题的 sqlite 部分，因为我已经有几年没有使用 sqlite 了，但性能下降的原因之一是您的 expr 语句。您需要将参数括起来 expr，这应该会大大加快循环的该部分：

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

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}]

回复收藏 0 原文

心的位置 2024-11-08 09:58:52

是的，从 Tcl 到 sql 表中执行单个插入并不是完成您想要的任务的最快方法。

为了加快速度，您应该将所需的表写入外部文件并读取数据
使用单个数据库语句。

您可以像这样写出数据：

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

然后，使用 copy 方法，您可以一口气读完所有内容：

db copy replace values temp_file

至于计算总和，我不知道为什么你不使用 sql 本身来进行求和。网络上有很多关于如何做到这一点的示例。这里 a 夫妇。我想你的sql语句会是这样的：

db select sum(x+y) from table

或者可能

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

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: