插入在sqlite3的数据库百万行 [英] Inserting 1000000 rows in sqlite3 database

问题描述

我想插入10,00,000行到数据库中，但它需要很长的时间的时间插入等。

I want to insert 10,00,000 rows into the database, but the it takes too long time time in insertion like.

例如。现在，我想这与2055行，它需要3分钟，这些数据上传到数据库..而这一次是太多了2055项。

e.g. Now I am trying it with 2055 rows and it takes 3 minutes to upload this data into the database.. and this time is too much for 2055 entries.

在将数据插入数据库的下面是我的方法：

The following is my method of inserting the data into the database:

      public void insert_database(Context context,String field1,String field2,String field3,String field4,String field5,String field6 ,String field7,String field8,String field9,String field10)
{

    try
    {
        //RayAllen_Database.beginTransaction();
        RayAllen_Database.execSQL(" insert or replace into "+ TableName_csv+" values( '"+field1+"' ,'"+field2+"','"+field3+"','"+field4+"','"+field5+"','"+field6+"','"+field7+"','"+field8+"','"+field9+"','"+field10+"');");


    }
    catch(Exception e)
    {
        //Log.i("Database Exception", "Exception");
        e.printStackTrace();
    }

}

而在另一个类叫做：解析数据：在​​这里我解析csv文件，并在解析：

and in the another class called: Parsing Data: here I am parsing the csv file and while parsing:

尝试{

CSVReader reader=new CSVReader(new FileReader(filename));
String [] nextLine;

//create database
obj.create_database(context);
obj.OpenDatabase(context);
//reader.readNext();

while ((nextLine=reader.readNext())!=null)
{
          //here I am calling the insert_database function
    }
 }

所以这里解析一行一式调用插入方法插入进入数据库。

so here It is parsing row one by one and calling the insert method to insert the entry into the database..

但它是太多的时间考虑。我怎样才能改善这种性能??

But it is too much time taking.. How can I improve the performance of this??

推荐答案

简单的例子时，为什么你应该做的，而不是错误的正确的事情。这是测试的ICS 4.0.4，它有可怕的INSERT性能运行。

Quick example time why you should do the right thing instead of "wrong". This was tested running on ICS 4.0.4, which has horrible INSERT-performance.

首先，一个简单的 SQLiteOpenHelper 的列上创建了一个唯一约束的表会导致冲突现在 - 和-即可。

First, a simple SQLiteOpenHelper that creates a table with a UNIQUE constraint on a column to cause conflicts now-and-then.

class SimpleHelper extends SQLiteOpenHelper {
    // InsertHelpers are a really good idea - they format a prepared statement
    // for you automatically.
    InsertHelper mInsert;
    public SimpleHelper(Context context) {
        super(context, "tanika.db", null, 1);
    }
    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        mInsert = new InsertHelper(db, "target");
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE target (\n" +
                "_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n" +
                "val1 TEXT NOT NULL,\n" +
                "val2 TEXT NOT NULL,\n" +
                "val3 TEXT NOT NULL,\n" +
                // Let's make one unique so we can get some juicy conflicts
                "val4 TEXT NOT NULL UNIQUE\n" +
                ")");
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }
}

捆绑在任何旧活动添加下面简单的测试方法：

Bundled in any old Activity we add the following simple test method:

long test(final int n) {
    long started = System.currentTimeMillis();
    ContentValues values = new ContentValues();

    for (int i = 0; i < n; i++) {
        values.clear();
        // Every 20th insert, generate a conflict in val4
        String val4 = String.valueOf(started + i);
        if (i % 20 == 0) {
            val4 = "conflict";
        }
        values.put("val1", "Value1");
        values.put("val2", "Value2");
        values.put("val3", "Value3");
        values.put("val4", val4);
        mHelper.mInsert.replace(values);
    }
    return System.currentTimeMillis() - started;
}

正如你所看到的，这会导致冲突的每20 插入左右。呼叫 InsertHelper＃替换（..）将会导致助手使用插入或更换上的冲突。

As you can see, this would cause a conflict every 20th INSERT or so. Calling InsertHelper#replace(..) causes the helper to use a INSERT OR REPLACE on conflicts.

现在，让我们来运行这个测试code与和放大器;没有事务周围。

Now, let's run this test code with & without a transaction surrounding it.

class Test1 extends AsyncTask<Integer, Void, Long> {
    @Override
    protected Long doInBackground(Integer... params) {
        return test(params[0]);
    }
    @Override
    protected void onPostExecute(Long result) {
        System.out.println(getClass().getSimpleName() + " finished in " + result + "ms");
    }
}

class Test2 extends AsyncTask<Integer, Void, Long> {
    protected Long doInBackground(Integer... params) {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        db.beginTransaction();
        long started = System.currentTimeMillis();
        try {
            test(params[0]);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
        return System.currentTimeMillis() - started;
    }
    @Override
    protected void onPostExecute(Long result) {
        System.out.println(getClass().getSimpleName() + " finished in " + result + "ms");
    }
}

一切开始是这样的：

Everything is started like this:

@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    mHelper = new SimpleHelper(this);
    mHelper.getWritableDatabase(); // Forces the helper to initialize.
    new Test1().execute(2055);
    new Test2().execute(2055);
}

和结果？如果没有交易插入。就拿 41072ms 。通过交易，他们采取的 940ms 。总之，农民田间学校，开始使用 InsertHelper 和交易。

And the results? Without a transaction the INSERTs take 41072ms. With transactions they take 940ms. In short, FFS, start using InsertHelpers and transactions.

这篇关于插入在sqlite3的数据库百万行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！