“Bulk Insert” for SQLite does not exist.  However, there are some tricks to speed up bulk loading of SQLite data.  In this post, I am going to show you some techniques that I’ve learned to load data as fast as possible into a SQLite database.

First, every SQL statement ran on SQLite is ran under a transaction even if you don’t specify one.  When loading thousands of records, this can be very taxing and slow things down very fast.  As a result, you’ll want to load all records under one transaction so that the SQLite DBMS will not start/commit a transaction for every row.

Second, use prepared statements and/or reuse your SQLiteCommand objects.  What you do is dependent on what technology you are using SQLite with (e.g. Java for Android or ADO.NET).  Rebuilding objects for every record inserted is very taxing.  So you’ll want to build the object once, then pass values to it for each row.

In summary, do the following for quick “bulk inserts”:
1)  Use 1 transaction to load all records.
2)  Reuse command/prepared statements to insert each row.

Here’s a snippet on how to bulk insert in Java for Android:

db.beginTransaction();
try {
	SQLiteStatement insert = null;
	insert = db.compileStatement("INSERT OR REPLACE INTO \"MyTable\" ("
			+ "\"MyColumnName\") VALUES (?)");
	
	for (i = 0; i++; i < 10000)
	{
		insert.bindLong(1, i);
        insert.execute();
		insert.clearBindings();
	}
	
	db.setTransactionSuccessful();
}
catch (Exception e) {
	String errMsg = (e.getMessage() == null) ? "bulkInsert failed" : e.getMessage();
	Log.e("bulkInsert:", errMsg);
}
finally {
	db.endTransaction();
}

Here’s an example of how to bulk insert in VB .NET:

        Using sqliteConn As SQLiteConnection = New SQLiteConnection("Data Source=c:\test.s3db")

            Dim sqliteTran As SQLiteTransaction = Nothing

            Try
                Dim sql As StringBuilder = New StringBuilder()
                sql.Append("INSERT INTO ""MyTable""(""MyColumnName"") VALUES (:MyColumnName);")

                Dim cmdSQLite As SQLiteCommand = sqliteConn.CreateCommand()
                With cmdSQLite
                    .CommandType = CommandType.Text
                    .CommandText = sql.ToString()
                    cmdSQLite.Parameters.Add(":MyColumnName", DbType.Int64)
                End With

                Dim i As Integer = 0
                While i < 10000
                    cmdSQLite.Parameters(":MyColumnName").Value = i
                    cmdSQLite.ExecuteNonQuery()

                    i += 1
                End While

                sqliteTran.Commit()
            Catch ex As Exception
                'attempt to rollback the transaction
                Try
                    sqliteTran.Rollback()
                Catch ex2 As Exception
                    'do nothing
                End Try

                'rethrow the exception
                Throw (ex)
            End Try
        End Using

Both examples insert 10,000 records.  Notice that we explicitly kick off one transaction for all inserts and commit it after we are finished inserting.  Also notice that in both examples, one command/prepared statement is built and reused for all 10,000 inserts.  There are some other SQLite tweaks you can do to slightly speed up performance, but the 2 mentioned will give you the most gains without having to get too technical with the SQLite DBMS engine.

If you are like me, you may be confused with all of the Android release code names.  Something I learned today is that the code names are in alphabetical order.  So if 5.0 is Jelly Bean, my wild guess is 6.0 will be Kiwi…

Something else I learned today… don’t set your SD card size to 16gb for your AVD.  It literally takes up 16gb of HD space and takes forever to allocate.  :(

In my last two posts on Android, I went over how to setup your machine to program for Android and I also went over how to create an Android project.  In this post I’m going to go over Android Virtual Devices and how they are used in Android development.  An Android Virtual Device (AVD) is an emulator configuration which models an Android device.  Before you can run your application, you have to define an AVD to run and test it on.  Setting up an AVD is very easy using the AVD Manager integrated into Eclipse.

To setup a new AVD in Eclipse, you’ll need to navigate to “Window >> Android SDK and AVD Manager”.  Click the Virtual Devices option on the left side.  I will show you how to create a Gingerbread (Android 2.3) AVD.  To create the AVD, click the “New” button and fill out the options as shown below.  Click “Create AVD” when you are finished.

Copying the options I have set above will create a generic Android Gingerbread emulator to use.  Next, I’ll briefly touch on what each of the AVD options do.

Name:  The name of the AVD.

Target:  The API level to which you want to test against.  Typically this is the API level that you setup for your application’s build target.  The AVD target level must be greater than or equal to what your application is set to.

CPU/ABI:  This is intended for Android devices which run on non-ARM processor architectures.  From my research, I’ve not seen any devices that supported such.

SD Card:  The size of the SD card.

Snapshot:  This option is a newer option which helps speed up the starting of the Android emulator.  You can enable this so that the emulator will launch quicker and when you stop it, it will save its state.  You can disable the save to snapshot after you’ve saved it once in the state you want to help it close faster.  On my machine, it’s still slow to start… but maybe that’s just my laptop’s poor disk speed.

Skin:  The skin of the emulated Android device.  HVGA is default.  You can download custom skins.

Hardware:  You can define all sorts of custom settings to emulate (e.g. RAM size).  I’m not going to list them here, but you can view the list here.

Override the existing AVD with the same name:  If you have an existing AVD created that has the same name as you just named this AVD device, it will override the old one.

In my last post, I showed what is needed to get started programming for Android.  There’s a lot that goes into setting up your machine’s environment (SDK’s, IDE’s, Plug-Ins, etc).  Once that’s all done, you are probably ready to start programming in Eclipse.  My first obstacle I encountered was creating a new Android Project.

When creating a new project in Eclipse, you are presented with a “New Project'” wizard.  If you installed & setup all the items I mentioned in my last post correctly, you’ll have a new project type of “Android Project” in the “Android” folder.  It should look similar to below:

Once you’ve selected to create an Android project, your SDK mapping will kick in and the subsequent step of the wizard will populate all the build targets for you as well as present you with a ton of project creation options.  If you don’t have any build targets, you’ve not set your SDK location properly.  You can do this by going to Windows >> Preferences >> Android and fill in the SDK location.  Below are screenshots of the “New Android Project” step in the wizard.  I’m going to go over what these project creation options are.

Project Name:  This is the name of the directory where all the project files will reside.  This is an "Eclipse” thing.  Just make sure that your project name will be intuitive and unique.

Build Target:  This is basically the minimum version of Android which your application will run on.  Keep in mind that Android applications are forward compatible.  So if you choose Android 2.2, it will compile against the Android 2.2 platform library, but technically it should be able to be ran on  Android 3.0.  So choose your minimum supported version for your target here.  Pick the lowest version your application can support.

Application Name:  This is the title of your application.  This will be seen by your users on an Android device.  It is good to be descriptive, but keep it somewhat short.

Package Name:  This guy is a bit tricky.  It is the namespace which your application’s source code will reside under.  The tricky part is the realization that on Android devices, the package name MUST be unique.  So if two applications are running under the same package name (namespace) of “HelloWorld”… bad things.  As a result, there is a “domain-style” naming methodology that should be employed to keep things unique.  The suggestion is to always follow a “com.companyname.packagename” type of a naming model.  The “companyname” can be your organization, company, or unique name which identifies the developing entity.  I’ve also read that com.google, com.android, and com.example are reserved.  So stay away from those.

Create Activity:  This is a class that is auto-created by the plugin which is a subclass of Android’s Activity class.  The class is a stub which will be launched when your application starts up.  Other activities can be created from your application, but this is the first one that the user will experience.

Min SDK Version:  As I previously noted with the build target, you choose the minimum SDK version which your application will support.  This option tells what the minimum API level will be along with the minimum SDK version.  Each build target should have a corresponding API Level assigned.  Pick the API Level for the build target you previously chose.  So again, the Min SDK Version number will match the Build Target number for most applications.

In my previous post, I gave a brief overview of the current state of computing and why I felt that being an expert in .NET is not enough to effectively address the mobile market.  I chose to learn to develop for Android first.  Before I delve into what you’ll need to get started programming for Android, I’d like to go over a bit of background on Android.  This background will help you understand why you need what you need to get up and programming.

The core of the Android platform is ran on a Linux kernel which has all the drivers needed to run the display, camera, keypad, flash memory, etc.  On top of that, we have native libraries (OpenGL, SQLite, etc) and the Java virtual machine (JVM) which is responsible for interpreting and executing Java bytecode.  Android has its own JVM called the Dalvik VM which is highly optimized to run on low-end handled devices.  As applications invoke these core libraries, each application gets its own Dalvik VM instance to execute under.  On top of the Linux kernel and native libraries/VM, we have the Android Java API’s which include UI, package installs and security, telephony, locations, resources, graphics libraries, etc.  Basically the Android SDK supports most of the Java Platform but replaces the Abstract Window Toolkit (AWT) and Swing with it’s own fourth generation UI framework.  So from this architecture, we can assume we are going to need java and android software development kits, and an IDE to program, compile, and simulate such.

As a .NET developer, I am very familiar with Visual Studio and TFS.  I currently program in VB, however I do have a C# background as well.  It would be wonderful if I could leverage Visual Studio as my IDE along with already known programming languages to program for Android.  Technically, building Android apps can be done in Visual Studio either by getting creative with batch scripts and workarounds, using WinGDB, or by purchasing Mono for Android for $300.  Each of these solutions have some serious drawbacks such as spending money, no intellisense support, and larger application download size.  On top of this, most tutorials on the web are tailored for Eclipse for Java Developers, not Visual Studio.  On a good note, TFS can integrate into Eclipse with Microsoft Visual Studio Team Explorer Everywhere 2010.  As a result, I plan to stomach the pain of learning a new IDE and going with Eclipse for Java Developers.  I’ll have TFS integration, it’s free, most online tutorials will be using it, there is an ADT plugin which offers GUIs and wizards to assist in project creation, and I’ll have intellisense support.  The only unknown I have is TFS auto-builds.  A quick glance on the web appears to have some solutions, so I’ll cross that bridge when I get there.

Now that I know I want to use Eclipse as my IDE with TFS integration, I need to download and install the items I need.  Here are the steps that I took to get my development environment up and going on Windows 7. 

1) Install the Java Platform (JDK – Java Development Kit) – You need this to develop Java applications on your computer.  Download and install the 32-bit version as other items we will be downloading and installing will be 32-bit.
2) Add the JDK path to your System Environment Variables – This will allow you tap into executables provided via the JDK from the command prompt.  In Windows 7, you can do so by right-clicking Computer >> Properties >> Advanced System Settings >> Environment Variables >> User variables for xxxxx >> New.  Variable name will be “Path”.  Variable value will be the local path to where the JDK bin directory is.  On my machine it was “C:\Program Files\Java\jdk1.7.0\bin”.
3)  Install the Android SDK (Software Development Kit) – This kit will provide the libraries necessary for you to develop Android applications.  After you’ve downloaded and installed this, you should have the Android SDK manager application.  This will allow you to download and update the Android SDK libraries and components.  I went ahead and launched the program and had it install & update everything.  Warning: it took a long time to do this.
4)  Install Eclipse for Java Developers -  This is the IDE of choice for developing Android apps.  It’s free and plugins are available for Android and TFS.  Again, download and install the 32-bit version of it.
5)  Install the ADT Plugin for Eclipse (Android Development Tools) – You can download and install this plugin by navigating to Help >> Install New Software in Eclipse.  It should bring up a dialog which you can click the “Add” button which will bring up an “Add Repository” dialogue.  Within this window, you can give it a name of “Android ADT” and for the Location type in: “https://dl-ssl.google.com/android/eclipse/”.  Confirm, select all available components to install, and click through the wizard and it should download and install the ADT plugin into your Eclipse IDE.  For more information click here.
6)  Set the Android SDK Location in Eclipse – In Eclipse, you need to set the Android SDK location.  To do this, go to Window >> Preferences >> Android in Eclipse.  Browse to the Android SDK install location.  On my machine, it was “C:\Program Files\Android\android-sdk”.
7)  Install the TFS Plugin for Eclipse (Microsoft Visual Studio Team Explorer Everywhere 2010) – If you want to integrate TFS into Eclipse, you’ll need a license for Team Explorer Everywhere and need to download the “TFSEclipsePlugin-UpdateSiteArchive-10.1.0.zip” (or whatever version is the latest when you go to install it).  Like the ADT Plugin, you have to install the plugin by going to Help >> Install New Software in Eclipse.  This time, you can browse to your local disk and select the “TFSEclipsePlugin-UpdateSiteArchive-10.1.0.zip” file for the location instead of a remote URL like before.  You can add a project to TFS by right-clicking a project and going to Team >> Share Project.  The first time you run the TFS plugin, you’ll be prompted to enter your license key.  You should be able to take it from here.

So there you have it.  6-7 steps to setting up your machine to develop for Android.  Most of the steps are for setting up the Eclipse IDE.  It definitely is not the 4 hour install process that Visual Studio makes you go through, but it does require manual setup.  The nice thing is that all of this is free except for the TFS piece.  I already had an MSDN subscription so this wasn’t an issue for me, but it may be an issue for others.  In that case, you could probably find other options to use for your source control.