在sqlite3数据库中插入1000000条记录[英] Inserting 1000000 rows in sqlite3 database

本文是小编为大家收集整理的关于在sqlite3数据库中插入1000000条记录的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我想在数据库中插入 10,000,000 行,但是插入时间太长了.

例如现在我正在尝试使用 2055 行,将这些数据上传到数据库需要 3 分钟......而这个时间对于 2055 个条目来说太多了.

以下是我将数据插入数据库的方法:

      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 文件并在解析时:

试试{

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

所以这里是逐行解析并调用insert方法将条目插入到数据库中..

但是太费时间了..我怎样才能提高它的性能??

推荐答案

快速示例说明为什么应该做正确的事情而不是"错误的".这是在 ICS 4.0.4 上运行的测试,它具有可怕的插入性能.

首先,一个简单的 SQLiteOpenHelper 创建一个表,在列上有一个 UNIQUE 约束,导致时不时发生冲突.

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) {
    }
}

捆绑在任何旧的 Activity 中,我们添加了以下简单的测试方法:

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 个 INSERT 左右发生一次冲突.调用 InsertHelper#replace(..) 会导致助手在冲突时使用 INSERT OR REPLACE.

现在,让我们用 & 运行这个测试代码没有围绕它的交易.

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");
    }
}

一切都是这样开始的:

@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);
}

结果呢?在没有事务的情况下,INSERT 花费 41072 毫秒.对于事务,它们需要 940 毫秒.简而言之,FFS,开始使用InsertHelpers 和事务.

其他推荐答案

您可以使用离线工具填充数据库,然后在安装软件包时将其导入.您可以将数据库存储在外部 SD 卡或应用程序的资产文件夹中.

我是这样做的:

  1. 使用 Android Debuger Bridge (adb) 将应用程序数据库复制到本地文件夹,如下所示:adb pull /data/data/<your application provider>/databases/yourdatbase.db C:/users/databases/yourdatbase.db.

  2. 使用您最喜欢的 GUI/CLI 工具连接到 SQLites 数据库 C:/users/databases/yourdatbase.db 并完成 1 000 000 条记录的填充.

  3. 将填充的数据库复制到 Android 开发环境 asset 文件夹.

  4. 现在从设备上卸载您的应用程序,以确保在您第一次安装时没有创建数据库.

  5. 修改您的 SQLiteHepler 类,以便它检查数据库是否存在,如果存在则使用该数据库.如果不存在数据库,Helper 会从您的资产文件夹中复制该数据库以及您的 1 000 000 条记录.我是这样做的:

    public class MyDatabaseHelper extends SQLiteOpenHelper {
    
        /*
            Other SQLiteOpenHelper declarations here ...
        */
    
        private static final String DATABASE_NAME   = "application.db";
        private static final String DB_PATH         = "/data/data/" + context.getPackageName() + "/databases/";
    
    
        /*
            Your SQLiteOpenHelper functions/procedures here ...
        */
    
        public boolean isDataBaseExist() {
    
            File dbFile     = new File(DB_PATH + DATABASE_NAME);
            return dbFile.exists();
        }
    
        public void copyDataBase(Context context) throws IOException {
    
            this.getReadableDatabase();
    
            InputStream inFile      = context.getResources().getAssets().open(DATABASE_NAME);
    
            // Path to the just created empty db
            String outFileName      = DB_PATH + DATABASE_NAME;
            OutputStream outFile    = new FileOutputStream(outFileName);
    
            // transfer bytes from the inputfile to the outputfile
            byte[] buffer = new byte[1024];
    
            int length;
    
            while ((length = inFile.read(buffer)) > 0) {
    
                outFile.write(buffer, 0, length);
    
            }
    
            // Close the streams
            outFile.flush();
            outFile.close();
            inFile.close();
        } 
    

此数据库将与您的应用程序一起编译,并且在首次启动时所有数据都会在那里.可能有更简单的方法,但我希望这对某人有所帮助.

其他推荐答案

加速 sqlite 插入操作 经历了一个类似的案例,并展示了如何使用事务来优化插入.

本文地址:https://www.itbaoku.cn/post/102332.html

问题描述

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

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();
    }

}

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

try {

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??

推荐答案

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.

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

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.

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);
}

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

其他推荐答案

You could populate your database using offline tools and then import it when you install your package. You can either store the database in the external sd card or in the asset folder of your application.

This is how I do it:

  1. Copy the application database to a local folder using the Android Debuger Bridge (adb) like this: adb pull /data/data/<your application provider>/databases/yourdatbase.db C:/users/databases/yourdatbase.db.

  2. Connect to the SQLites database C:/users/databases/yourdatbase.db with your favourite GUI/CLI tool and complete your population of the 1 000 000 records.

  3. Copy your populated database to your Android development environment asset folder.

  4. Now uninstall your application from the device to make sure there is no database created when you install for the first time.

  5. Modify your SQLiteHepler class so that it checks if a database exists and if one exists it uses that one. If no database exists the Helper copies the one from your asset folder together with your 1 000 000 records. This is how I have done it:

    public class MyDatabaseHelper extends SQLiteOpenHelper {
    
        /*
            Other SQLiteOpenHelper declarations here ...
        */
    
        private static final String DATABASE_NAME   = "application.db";
        private static final String DB_PATH         = "/data/data/" + context.getPackageName() + "/databases/";
    
    
        /*
            Your SQLiteOpenHelper functions/procedures here ...
        */
    
        public boolean isDataBaseExist() {
    
            File dbFile     = new File(DB_PATH + DATABASE_NAME);
            return dbFile.exists();
        }
    
        public void copyDataBase(Context context) throws IOException {
    
            this.getReadableDatabase();
    
            InputStream inFile      = context.getResources().getAssets().open(DATABASE_NAME);
    
            // Path to the just created empty db
            String outFileName      = DB_PATH + DATABASE_NAME;
            OutputStream outFile    = new FileOutputStream(outFileName);
    
            // transfer bytes from the inputfile to the outputfile
            byte[] buffer = new byte[1024];
    
            int length;
    
            while ((length = inFile.read(buffer)) > 0) {
    
                outFile.write(buffer, 0, length);
    
            }
    
            // Close the streams
            outFile.flush();
            outFile.close();
            inFile.close();
        } 
    

This database will be compliled with you app and on first launch all the data will be there. There could be a simpler method, but I hope this helps someone.

其他推荐答案

Speeding up sqlite insert operations goes through a similar case and shows how to use transactions to optimize the insertion.