System.Data.SQLite

Hi all

I have a Pocket PC (about 350Mhz CPU) on which I am reading through a 2.3MB XML file and inserting into a database.  Here is the C# insert code:

        private void InsertNewInstancesIntoDatabase(List<PersistentObject> newInstances, SQLiteTransaction transaction)
        {
            SQLiteCommand insertCommand = Connection.CreateCommand();
            insertCommand.Transaction = transaction;
            using (insertCommand)
            {
                insertCommand.CommandType = CommandType.Text;
                insertCommand.CommandText = "insert into Instances(Oid, InstanceType, InstanceData) values (?,?,?)";
                SQLiteParameter oidParameter = new SQLiteParameter(DbType.Int32);
                SQLiteParameter instanceTypeParameter = new SQLiteParameter(DbType.String);
                SQLiteParameter instanceDataParameter = new SQLiteParameter(DbType.Binary);
                insertCommand.Parameters.Add(oidParameter);
                insertCommand.Parameters.Add(instanceTypeParameter);
                insertCommand.Parameters.Add(instanceDataParameter);

                foreach (PersistentObject instance in newInstances)
                {
                    oidParameter.Value = instance.ObjectId;
                    instanceTypeParameter.Value = instance.GetType().FullName;
                    instanceDataParameter.Value = ClassHelpers.StreamHelper.GetObjectAsByteArray(instance);
                    insertCommand.ExecuteNonQuery();
                    CurrentRowNumber++;
                    OnProgress(CurrentRowNumber, RowCount);
                }
            }//using(insertCommand)
        }


The import is currently taking about an hour (18 seconds on my desktop).  Is there any way I can make this faster?  I have tried executing "PRAGMA synchronous = OFF" when the connection opens, on my desktop this reduces the time to 2 seconds whereas on the Pocket PC it doesn't seem to make any difference.

I really need to improve the speed of this import as it is performed twice per week and is costing too much money in wasted man hours (200 people * 1 hour * twice per week = 400 hours per week).

I'd appreciate any help you can offer!

Thanks

Pete

A few questions ...

1. How many rows are we talking about?
2. Is the database on a SD card or some media card?
3. What does the table definition look like?

Here's why I ask ...

If it takes an hour to insert 100,000 rows, that's 27 row a second, which sucks.  Also, if we're talking about a storage card rather than main memory, then your mileage will vary depending on the speed of the card.

Finally, the table definition can also affect performance.  A typical performance booster would be to drop any indexes on the table, do the insert, then recreate the indexes.

Robert

 

Hi

Thanks for the quick reply!

#1 I will have to get back to you with.  Keep in mind that my object persistence framework sits between the importer and SQLite, but a profiler reveals that SQLite does in constitute most of the total time.

#2 SD Card

#3

create table Instances (
 Oid Integer Primary Key,
 InstanceType Text,
 InstanceData BLOB
);
create index InstancesInstanceTypeIndex on Instances(InstanceType);


Pete

Definitely drop that index before you start and recreate it afterwards.  It should have a big impact on performance.

On an SD card, between 500 and 1500 inserts a second is pretty reasonable.  I've gotten up to 2,000 rows per second in main memory on a 520mhz PocketPC.  You're pushing the limits of your platform my friend :)

Robert

 

Hi again

First I changed the primary key from Integer to INT.  This saved me 400KB on a 2MB file, and considering I don't have enough storage for more than 2^31 objects I think that an Int32 will be just fine.

Here are my statistics:
Inserts = 4229 / 89 seconds
Updates = 1474 / also 89 seconds

I got these figures by wrapping the command.ExecuteNonQuery() with
    DateTime startTime = DateTime.Now;
    ......
    InsertTime += DateTime.Now - startTime;
    InsertCount++;


The SD card I am writing to is an extension card if that matters?  What I'd like to know is if there any way I can speed this up at all?  The PRAGMA setting looked promising and worked great on the full .NET framework but made no difference on the compact framework.

What I am after is any kind of tip for improving speed.  For example, "Use Image instead of Blob".  My table only consists of an object ID, class name (with namespace), and binary data holding the streamed object.


Thanks

Pete

Couple things first. 

1. Your profiling code should use Environment.TickCount and not DateTime.Now -- DateTime.Now and its friends are very expensive on the PocketPC and will slow the entire thing down especially the way you're using it.
2. Get the Environment.TickCount before your loop, and again after the insert loop is finished (and do one subtract) rather than gather numbers during the loop.  Right now your statistics code is weighing down the code you want to profile and giving you a false number.
3. OID is a reserved word (or so I thought) in SQLite.  Changing the primary key from integer to int should not have made any difference at all -- it should have actually increased the database size!  "integer primary key" is an alias for the default ROWID (OID) of a row, so SQLite can optimize that out and save some space.
4. SQLite packs integers when it stores them.  That means that a column declared as "int" storing the value "1" takes 1 byte of storage, because only 1 byte is required to fully describe the int.  In otherwords, "int" and "integer" and "bigint" all store up to an Int64-sized integer, but the data is packed so that the smallest number of bytes is used to describe the int.

Robert

 

Hi

#1 I am not really too worried about.  I'm really after performance enhancing tips rather than "Why is this so slow?"

#2 I had to time the ExecuteNonQuery otherwise the time taken would include the time it takes to serialise my object to an array of bytes

#3 OID is working, and the DB is smaller if I use INT instead of Integer, what can I say? :-)

#4 Weird, because it is definately smaller


But back to my main point, can you think of anything I could do to squeeze a bit of extra speed out of it?  For example, is there a faster data type than BLOB?


Thanks

Pete

BLOB, image, etc are all the same.

The only thing left that you can do to speed things up is to drop the index before you begin the insert and recreate it afterwards.

Robert

 

Unfortunately that would be a bit of a pain to implement because I have abstracted the DB from the objects with a framework.

Okay, thanks very much for your time, I really appreciate it!


Pete

You may find this a bit odd

Time taken with index present = 9.2 seconds
Time taken with index dropped at start and then created at end = 10.5 seconds

How strange :-)

Pete

I ran into a similar situation.  I was using parameterized queries to perform inserts/updates/deletes for a desktop application.  The initial inserts down onto the client could exceed 400,000 inserts for a given table while after that I just pushed down changes.  This works well and is lightning fast on a desktop.  Then I started doing the same thing on a mobile device and found inserting 300-400 thousand rows taking a few minutes or so.

I decided this was just not good enough so I decided to ditch parameterized queries entirely.  I rewrote my import/update routine at the cursor level, creating records and setting column data for inserts, and seeking to index values and setting column data when doing updates.  After the change I'm now pulling in multiple hundreds of thousands of rows in seconds instead of minutes.

Another tactic I used is when synchronizing data, I have my client send up sets of index values for the data I'm synchronizing and then the server returns data so that all my inserts, updates, and deletes are pre-seperated, allowing the client to focus on each operation exclusively.