插入时间太长，需要代码优化 [英] Insert takes too long, code optimization needed

问题描述

我有一些代码用于将table1值传输到另一个table2，它们位于不同的数据库中.

I've some code I use to transfer a table1 values to another table2, they are sitting in different database.

当我有100.000条记录时，速度很慢.它要花10余分钟才能永远完成.

It's slow when I have 100.000 records. It takes forever to finish, 10+ minutes.

(Windows Mobile智能手机)

我该怎么办?

cmd.CommandText = "insert into " + TableName + " select * from sync2." + TableName+"";  
cmd.ExecuteNonQuery();

编辑

问题未解决.我仍在寻找答案.

推荐答案

1]您可以在connectionString中设置以下参数

1] You can set the following parameters in your connectionString

string connectionString = @"Data Source=E:\myDB.db3; New=true; Version=3; PRAGMA cache_size=20000; PRAGMA page_size=32768; PRAGMA synchronous=off";

有其自身的局限性.查看此链接以获取详细信息

which has its own limitations. check this link for details

以上内容最终会增加缓存的大小(cache_size和page_size)，但是如果强制关闭系统(synchronous = off)，您可能会丢失一些数据.

The above will increase the cache size ultimately(cache_size & page_size) but you could lose some data in case of force shutdown of your system(synchronous=off).

2]您可以将插入语句包装在这样的事务中

2] You can wrap your insert statements inside a transaction like this

dbTransaction = dbConnection.BeginTransaction();
dbCommand.Transaction = dbTransaction;
// your individual insert statements here
dbTransaction.Commit();

3] 此页面.看看

希望有所帮助
干杯！

Hope that helps
Cheers!

这篇关于插入时间太长，需要代码优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！