SQLiteDatabase Plugin for MP2 (3 Viewers)

[breese]

I went onto my server and changed the security levels for Windows Temp folders and User Temp Folders.
(I moved the system tmp / temp to R:\windows\temp and users temp to R:\Users when I built the machine)
Now the nice thing about building and saving the RamDrive is that any changes are removed on a reboot there-by allowing me to play games with it like Disallow Any Deletes!
So after making the security changes I started the server process, and copied the temp files it created.
Then I started the Client, and any temp file it created I copied.

In the zip file are 2 folders (Windows Temps) Normally stored in C:\Windows\temp, and (User Temps) Normally created by any applications used by the user.

All I had to do in order to return my machine to normal was Reboot.
I hope this helps.....

 

[MJGraf]

Thanks, will have a look at it tomorrow and probably already found a solution: PRAGMA temp_store=MEMORY. Does what the name implies and gives us another performance increase by 30 secs for the ImportTest

 

[MJGraf]

Just a short note so that it is not forgotten:

SQLite does not work with SlimTV Native (yet), because SlimTV Native uses TVE3.5, which in turn uses Entity Framework 4, which finally requires three methods called DatabaseExists, CreateDatabase and DeleteDatabase in the respective ProviderService when you use CodeFirst (which TVE 3.5 does).

These methods are not yet implemented for System.Data.SQLite.

MS itself has published a sample on how to implement these methods. The methods themselves are quite straightforward, but only because the main work is transferred into an object called DdlBuilder. Although this object's content is a bit more complicated, it doesn't really seem impossible to adapt this code for SQLite. Seems just like a few hours of comparing the SQL statements built by this class with the SQLite SQL syntax...

Only problem: The System.Data.SQLite.SQLiteProviderServices class is internal and sealed. Does anyone of the C# cracks around knows a (potentially very dirty) hack to nevertheless inherit from that object just by using the DLL? I don't want to setup a build environment for the whole System.Data.SQLite project...

Thanks!

 

[MJGraf]

@gibman @morpheus_xx
I'm currently thinking of implementing the missing CreateDatabase, DatabaseExists and DeleteDatabase methods for SQLite's DBProviderServices. As mentioned, the DBProviderServices contained in system.data.SQLite is internal and sealed, so I thought about just copying the code into the SQLiteDatabase project and adding the missing parts.
My question is: Can we somehow register our custom DBProviderServices class to be used instead of the one contained in system.data.sqlite? I read somewhere that doing so via code is only possible since EF 6. But is it possible to do so in the app.config file?
Thanks for your help!
Michael

 

[MJGraf]

Found it again:
http://entityframework.codeplex.com/wikipage?title=Rebuilding EF providers for EF6
As can be seen under 'config file registration it should be possible to register our custom DBProviderService with EF < 6. At least this is my understanding. Is this correct?

 

[breese]

It just so happens I did a little more testing between SQLCe and SQLite
With each I ran an import from Music on Raid5 to SSD and from Music and DB from and to RamDrive (Yes I have a RamDrive large enough to hold it).

First: The Soap Error I orig found Only happen when using SQLCeDatabase and it doesn not matter where the source or destinations are.

Second: Speed with SQLite is only millaseconds either on the raid to ssd or all raid...

I am including all the logs and databases in seperate folders for your viewing pleasure. Imput to this or anything is always welcome.

MJGraf, if you have a newer version of SQLite DB, I would and willing to test anything and everything. if you want to add more logging to the code, I am open to it.... I do have the horse power for it... even better if I have the ability to log it to my RamDrive.
btw: I can test whatever you want tested, I am not trained / know how to build the end code....

 

[MJGraf]

Hi breese,

there will be a test version of the next SQLiteDatabase plugin version soon. I'm currently waiting for system.data.sqlite v1.0.89.0 to be released (see here), which is scheduled for October so it shouldn't be too long anymore. The main reason for waiting is that this release of system.data.sqlite will include the SQlite native version 3.8.1, which was release on October 17.
Currently we use system.data.sqlite version 1.0.88.0, which only includes SQLite native version 3.7.17.
Starting with version 3.8.0 (and therefore also contained in v3.8.1) of SQLite native there is a completely new query optimizer included, which is said to give a material speed improvement for read queries.
I'm curious how this will affect our SQLiteDatabase plugin speed in MP2

As to your test results (thanks again for all your efforts, this is very interesting and helpful):
Somehow I get the feeling that something is wrong - but I cannot say, yet, what and why. Let's take a look at the relevant parts of the log files:

Raid5/SSD:
SQLite:

[2013-10-21 10:24:39,539] [92007 ] [Importer ] [INFO ] - ImporterWorker: Starting import job 'ImportJob '{e88e64a8-0233-4fdf-ba27-0b44c6a39ae9}:///F:/Music/''
[2013-10-21 10:24:39,543] [92011 ] [Importer ] [INFO ] - ImporterWorker: Importing 'F:\Music\'
[2013-10-21 10:24:39,646] [92114 ] [Importer ] [INFO ] - ImporterWorker: Importing 'F:\Music\Verious\'
[2013-10-21 10:24:39,650] [92118 ] [Importer ] [INFO ] - ImporterWorker: Importing 'F:\Music\Verious\500 Classic Rock Songs\'
[2013-10-21 10:24:45,551] [98019 ] [Importer ] [INFO ] - ImporterWorker: Finished import job 'ImportJob '{e88e64a8-0233-4fdf-ba27-0b44c6a39ae9}:///F:/Music/''

==> 6 seconds
==> Database file size: 1.272KB

SQLCE:

[2013-10-21 09:58:56,541] [91041 ] [Importer ] [INFO ] - ImporterWorker: Starting import job 'ImportJob '{e88e64a8-0233-4fdf-ba27-0b44c6a39ae9}:///F:/Music/''
[2013-10-21 09:58:56,543] [91043 ] [Importer ] [INFO ] - ImporterWorker: Importing 'F:\Music\'
[2013-10-21 09:58:56,634] [91134 ] [Importer ] [INFO ] - ImporterWorker: Importing 'F:\Music\Verious\'
[2013-10-21 09:58:56,659] [91159 ] [Importer ] [INFO ] - ImporterWorker: Importing 'F:\Music\Verious\500 Classic Rock Songs\'
[2013-10-21 10:09:28,280] [722780 ] [Importer ] [INFO ] - ImporterWorker: Finished import job 'ImportJob '{e88e64a8-0233-4fdf-ba27-0b44c6a39ae9}:///F:/Music/''

There is no log entry between 9:58:56 and 10:09:28
==> 632 seconds
==> Database file size: 1.664KB


Ramdisk/Ramdisk:
SQLite:

[2013-10-21 10:34:46,118] [168762 ] [Importer ] [INFO ] - ImporterWorker: Starting import job 'ImportJob '{e88e64a8-0233-4fdf-ba27-0b44c6a39ae9}:///R:/Music/''
[2013-10-21 10:34:46,123] [168767 ] [Importer ] [INFO ] - ImporterWorker: Importing 'R:\Music\'
[2013-10-21 10:34:46,200] [168844 ] [Importer ] [INFO ] - ImporterWorker: Importing 'R:\Music\Verious\'
[2013-10-21 10:34:46,203] [168847 ] [Importer ] [INFO ] - ImporterWorker: Importing 'R:\Music\Verious\500 Classic Rock Songs\'
[2013-10-21 10:34:54,283] [176928 ] [Importer ] [INFO ] - ImporterWorker: Finished import job 'ImportJob '{e88e64a8-0233-4fdf-ba27-0b44c6a39ae9}:///R:/Music/''

==> 8 seconds
==> Database file size: 1.272KB

SQLCE:

[2013-10-21 09:21:03,709] [689521 ] [Importer ] [INFO ] - ImporterWorker: Starting import job 'ImportJob '{e88e64a8-0233-4fdf-ba27-0b44c6a39ae9}:///R:/Music/''
[2013-10-21 09:21:03,716] [689528 ] [Importer ] [INFO ] - ImporterWorker: Importing 'R:\Music\'
[2013-10-21 09:21:03,822] [689634 ] [Importer ] [INFO ] - ImporterWorker: Importing 'R:\Music\Verious\'
[2013-10-21 09:21:03,842] [689654 ] [Importer ] [INFO ] - ImporterWorker: Importing 'R:\Music\Verious\500 Classic Rock Songs\'
[2013-10-21 09:21:04,326] [690138 ] [Importer ] [INFO ] - ImporterWorker: Finished import job 'ImportJob '{e88e64a8-0233-4fdf-ba27-0b44c6a39ae9}:///R:/Music/''

==> 0.5 seconds
==> Database file size: 1.664KB

First of all, the database file sizes are identical for SQLite Raid5/SSD and SQLite Ramdisk/Ramdisk. Same applies to SQLCE. That means that in both cases both databases import the same amount of data. Just as it should be.
The database file size is in both cases somewhere between 1MB and 2MB. When the folder name of your share is correct, you import 500 music files so I would assume that there are no covers imported, because otherwise the database file size would be too small.

Now when the music files are located on your Raid5 and the database is located on SSD, the import with SQLite takes 6 seconds, the same import with SQLCE takes 632 seconds. I should be very happy with that result and I wouldn't exclude that it is correct. But on the other hand, a speed improvement of more than 100x as fast for the import would really be amazing. I'm so surprised about it that I still have my doubts whether there may be just a bug in SQLCE that makes it so slow...
Taking into account that the database file size in the end is only 1.2MB, I would consider 6 seconds as normal - most of the time is probably spent for reading the tags from the files, because SQLite can hold the database with a size of < 2MB completely in its cache, so database access itself should be very fast.

In case both, the music files and the database are located on your RamDisk, the whole thing starts to become really strange. Now all of a sudden SQLCE only needs 0.5 seconds instead of 632 seconds, but SQLite takes 16x as long, i.e. 8 seconds. So SQLite is slower with the RamDisk than when using Raid5/SSD. I just can't believe that...

But as I said, I don't know yet how we tackle this. Best thing would probably to wait at least for my next test release of SQLiteDatabase. Then I could provide you with an additional MP2 core DLL which includes additional logging. That way we could see exactly how long it takes to read the tags and to write it to the database for every single music file. Maybe this makes it a bit clearer...

Thanks again!
Michael

 

[MJGraf]

In the zip file are 2 folders (Windows Temps) Normally stored in C:\Windows\temp, and (User Temps) Normally created by any applications used by the user.

Had a look at those files as well. They have nothing to do with SQLite. Very interesting, I didn't see such before, but it seems that these are temp files created by the .net runtime itself (there are some C# source files with fragments from MP2 source code as well as the compiled versions of those files).
So there is nothing we can (and should) do about this. Everything is fine

 

[MJGraf]

Back to SQLiteDatabase and the speed improvements. In the meantime I got two things solved:

Shared Cache Mode
As mentioned above, I would like to use shared cache more because I expect it to improve the performance in particular when using multiple clients at the same time. The problem was that to enable shared cache mode in system.data.sqlite it is necessary to use a so called "FullURI" with the parameter "?cache=shared" at the end. But when doing so, system.data.sqlite ignores the page size value that we specify in the connection string. I found the respective code in the system.data.sqlite sources and I'm not sure whether this is a bug in system.data.sqlite. The standard page size of SQLite is 1024 bytes, which is considerably slower than using 4096 or 8192 bytes.
Now the solution to this was easier than I thought. The page size is - obviously - saved in the respective database file. I.e., if you create a database file with a page size of 4096 bytes, it will keep this page size forever unless you explicitly change it. So the solution was that SQLiteDatabase now checks whether the database file already exists. If not, it creates the database with a connection string that uses the datasource property instead of the FullURI (and therefore does not use shared cache) and the correct page size as specified in our SQLiteSettings. It then closes and disposes this "one-time-connection" and as of now uses connections with FullURIs in the connection string. Now we have a database file with a correct page size and for all future accesses to the database we have the shared cache mode.
Since I don't know whether the behavior explained above is a bug in system.data.sqlite or whether it is intended behavior, I put a lot of comments in the code to explain this situation. If system.data.sqlite considers this a bug and this bug is solved in future versions, we can take this potential "workaround" out of our code...
A small hint to @morpheus_xx (sorry for tagging you once more ): When I remember correctly, for your integration of TVE3.5 into MP2 Server you take the connection string from the MP2 database, extract the DataSource property, change the name of the database(-file) and use the resulting connection string for the TVE3.5 EF-database-connection. Now as explained above, SQLiteDatabase does not have a DataSource property anymore, it has a "FullURI" instead so we would have to adapt this part of the integration code accordingly. For now we first need code first support in sqlite, so it still has time until we have to take care of this. Just wanted to make sure one of us remembers this when we first see the respective exception

locking_mode=EXCLUSIVE
Another thing I wanted to try for quite some time was the so called exclusive locking mode. Normally, SQLite acquires OS file locks only for the time it actually reads or writes to the database file. This sounds reasonable, but it requires getting and releasing OS file locks for each and every transaction, which takes time. That's what exclusive locking mode avoids. When a connection first acquires an operating system file read lock, it keeps this lock. When it first acquires an operating system write lock, it keeps the write lock forever. So no need to permanently acquire and release read or write locks = better performance.
There is another potential performance gain when using exclusive locking mode and WAL journal mode (which we do). In WAL-mode, we have in particular three files while MP2 server is running. The database file itself (Datastore.s3db), the write ahead log file (Datastore.s3db-wal) and the shared memory index file (Datastore.s3db-shm). The latter one is used when multiple connections access the same database file at the same time. It is kind of an index of what is contained in the write ahead log file. This shared memory index file is not needed in locking mode exclusive, because there is only one connection holding a lock on the database - no need to write this file to the file system, the index can be kept in memory.
Now switching on exclusive locking mode was easy - just issue "PRAGMA locking_mode=EXCLUSIVE;" to the database. But the shared memory index file was still there. It took a while until I solved this one: When using WAL-mode, you can in general easily switch between locking mode exclusive and locking mode normal. To make this possible, SQLite generates and maintains this shared memory index file although you switch to exclusive locking mode - so no performance improvement. If you want to avoid this file to be created, you have to switch on exclusive locking mode BEFORE you enter WAL-mode. But the problem is that we specify WAL-mode in our connection string - which results in system.data.sqlite issuing a "PRAGMA journal_mode=WAL" to the database already when we open the connection (I only found this out with my extensive logging option...). So when I created a connection I thought the first command was "PRAGMA locking_mode=EXCLUSIVE;", but it was not.
The solution I found is similar to the solution for the shared cache mode above. Unfortunately, the locking mode is not stored in the database file, but has to be set for every connection. So we have to apply this "workaround" on every MP2 server start - but the impact on the startup speed is not measurable on my system (below 1ms). What I do now is we first create another "temporary connection" without entering WAL-mode. Then we switch on exclusive locking mode. We close the connection and dispose it. As of now we can use our regular connections with the regular connection string - but we still have to switch on exclusive logging mode for each and every "regular" connection. By doing so, no shared memory index file is ever created. Bingo...

The combination
Some of you may already have asked yourself: What the heck is the advantage of exclusive locking mode? There can only be one connection holding an exclusive operating system file lock on our database file. Doesn't that make multithreaded access to the database impossible?
Yes and No
If we don't use shared cache mode, but exclusive locking mode, we are doomed. Wonderfully reproducible deadlocks as soon as two or more database connections are in use. One of those connections has the exclusive lock and when a thread fetches a connection from our cache pool, does NOT get this one connection holding the exclusive lock but one of the other connections and then tries to write something into the database, it waits forever for this lock to be released.
BUT... When using shared cache mode, all the connections used by one process (even if they are used by multiple threads) look to other processes and - most importantly to the operating system - as one single connection. The picture here probably explains it best: http://www.sqlite.org/sharedcache.html As a result, we can have as many connections as we want withing one process - the MP2 Server process - but with as many threads as we want and nevertheless save the time for permanently acquiring and releasing OS file locks.
The only downside I could see is that now you can't open the database file with another third party tool while the MP2 Server is running - but you shouldn't do so anyway so this is imho acceptable If you want to open the DB file externally, stop MP2 Server Service first...

What we could now do is additionally enable "read uncommited" mode for our transactions (only possible in SQLite when shared cache is enabled). This would mean that we can even read from a table in our database file while another thread at the same time writes to exactly that table. On the other hand, this may obviously result in reading parts of a transaction, which may not be desired. I don't know how this would affect MP2 as a whole, because we are not talking about a business critical banking database here - it's all just about media files... So I will probably test how much this would influence our performance and only analyze the impact if there is a material performance increase by that. Otherwise I would stick with "serializable" for our transactions.

Now the results
The pure ImportTest result is not that impressive:
Page Size = 4KB / Shared Cache / temp_store=MEMORY / Exclusive Locking without SHM-File
ImportTest: 20:09
So a couple of seconds faster than before (heck I want those missing 10 seconds to be below 20 mins before I release the next version )
But what was impressive for me was indeed the "feeling" when using multiple MP2 clients. What I did is that I started my import test a second time and while the import was running, I "tortured" the MP2 Server with 3 concurrent MP2 clients (couldn't find more boxes here...). I tried to enter the audio section of MP2 with all three clients concurrently (as good as possible with two hands...) and permanently clicked on new and different filters. The log file showed that in the end the MP2 Server was using 6 database connections. And I couldn't notice any difference to when I use one MP2 Client and no import is running. Everithing was smooth. Really impressive. Unfortunately, I cannot "measure" that improvement, so everyone who want's to feel this improvement probably has to install at least three MP2 Clients

What is next
As mentioned above, I'm currently waiting for the next system.data.sqlite version to be released.
Then there is read uncommitted mode - as explained above.
But I already have an idea what else to do in the meantime... My extensive logs of SQLiteDatabase are very interesting. When we e.g. execute a simple insert, the extended SQLite logs show the following:

2013-10-23 20:37:19.668130 [Debug][Main]: SQLite (): Preparing {INSERT INTO MIA_TYPES (MIAM_ID, NAME, CREATION_DATE, MIAM_SERIALIZATION) VALUES (@MIAM_ID, @[USER=45291]name[/USER], @CREATION_DATE, @MIAM_SERIALIZATION)}...
2013-10-23 20:37:19.668130 [Debug][Main]: SQLite (): Statement 117328960 paramter count is 4.
2013-10-23 20:37:19.668130 [Debug][Main]: SQLite (): Statement 117328960 paramter #1 name is {@MIAM_ID}.
2013-10-23 20:37:19.668130 [Debug][Main]: SQLite (): Statement 117328960 paramter #2 name is {@NAME}.
2013-10-23 20:37:19.668130 [Debug][Main]: SQLite (): Statement 117328960 paramter #3 name is {@CREATION_DATE}.
2013-10-23 20:37:19.668130 [Debug][Main]: SQLite (): Statement 117328960 paramter #4 name is {@MIAM_SERIALIZATION}.
2013-10-23 20:37:19.668130 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #1 with database type Guid and raw value {0a296acd-f95b-4a28-90a2-e4fd2a4cc4ed}...
2013-10-23 20:37:19.683730 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #1 as type System.Byte[] with value {cd6a290a5bf9284a90a2e4fd2a4cc4ed}...
2013-10-23 20:37:19.683730 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #2 with database type String and raw value {ProviderResource}...
2013-10-23 20:37:19.683730 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #2 as type System.String with value {ProviderResource}...
2013-10-23 20:37:19.683730 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #2 as type System.Byte[] with value {50726f76696465725265736f7572636500}...
2013-10-23 20:37:19.683730 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #3 with database type DateTime and raw value {23.10.2013 20:37:19}...
2013-10-23 20:37:19.683730 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #3 as type System.DateTime with value {2013-10-23T20:37:19.6681304+02:00 Local 635181574396681304}...
2013-10-23 20:37:19.683730 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #3 as type System.Byte[] with value {323031332d31302d32332032303a33373a31392e3636383133303400}...
2013-10-23 20:37:19.683730 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #4 with database type String and raw value {<MediaItemAspectMetadata xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Id="0a296acd-f95b-4a28-90a2-e4fd2a4cc4ed" IsSystemAspect="false"><Name>ProviderResource</Name><Attributes><AttributeType><AttributeName>System-Id</AttributeName><AttributeType>System.String</AttributeType><Cardinality>Inline</Cardinality><Indexed>true</Indexed><MaxNumChars>100</MaxNumChars></AttributeType><AttributeType><AttributeName>Path</AttributeName><AttributeType>System.String</AttributeType><Cardinality>Inline</Cardinality><Indexed>true</Indexed><MaxNumChars>1000</MaxNumChars></AttributeType><AttributeType><AttributeName>ParentDirectory</AttributeName><AttributeType>System.Guid</AttributeType><Cardinality>Inline</Cardinality><Indexed>true</Indexed><MaxNumChars>0</MaxNumChars></AttributeType></Attributes></MediaItemAspectMetadata>}...
2013-10-23 20:37:19.683730 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #4 as type System.String with value {<MediaItemAspectMetadata xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Id="0a296acd-f95b-4a28-90a2-e4fd2a4cc4ed" IsSystemAspect="false"><Name>ProviderResource</Name><Attributes><AttributeType><AttributeName>System-Id</AttributeName><AttributeType>System.String</AttributeType><Cardinality>Inline</Cardinality><Indexed>true</Indexed><MaxNumChars>100</MaxNumChars></AttributeType><AttributeType><AttributeName>Path</AttributeName><AttributeType>System.String</AttributeType><Cardinality>Inline</Cardinality><Indexed>true</Indexed><MaxNumChars>1000</MaxNumChars></AttributeType><AttributeType><AttributeName>ParentDirectory</AttributeName><AttributeType>System.Guid</AttributeType><Cardinality>Inline</Cardinality><Indexed>true</Indexed><MaxNumChars>0</MaxNumChars></AttributeType></Attributes></MediaItemAspectMetadata>}...
2013-10-23 20:37:19.683730 [Debug][Main]: SQLite (): Binding statement 117328960 paramter #4 as type System.Byte[] with value {3c4d656469614974656d4173706563744d6574616461746120786d6c6e733a7873693d22687474703a2f2f7777772e77332e6f72672f323030312f584d4c536368656d612d696e7374616e63652220786d6c6e733a7873643d22687474703a2f2f7777772e77332e6f72672f323030312f584d4c536368656d61222049643d2230613239366163642d663935622d346132382d393061322d6534666432613463633465642220497353797374656d4173706563743d2266616c7365223e3c4e616d653e50726f76696465725265736f757263653c2f4e616d653e3c417474726962757465733e3c417474726962757465547970653e3c4174747269627574654e616d653e53797374656d2d49643c2f4174747269627574654e616d653e3c417474726962757465547970653e53797374656d2e537472696e673c2f417474726962757465547970653e3c43617264696e616c6974793e496e6c696e653c2f43617264696e616c6974793e3c496e64657865643e747275653c2f496e64657865643e3c4d61784e756d43686172733e3130303c2f4d61784e756d43686172733e3c2f417474726962757465547970653e3c417474726962757465547970653e3c4174747269627574654e616d653e506174683c2f4174747269627574654e616d653e3c417474726962757465547970653e53797374656d2e537472696e673c2f417474726962757465547970653e3c43617264696e616c6974793e496e6c696e653c2f43617264696e616c6974793e3c496e64657865643e747275653c2f496e64657865643e3c4d61784e756d43686172733e313030303c2f4d61784e756d43686172733e3c2f417474726962757465547970653e3c417474726962757465547970653e3c4174747269627574654e616d653e506172656e744469726563746f72793c2f4174747269627574654e616d653e3c417474726962757465547970653e53797374656d2e477569643c2f417474726962757465547970653e3c43617264696e616c6974793e496e6c696e653c2f43617264696e616c6974793e3c496e64657865643e747275653c2f496e64657865643e3c4d61784e756d43686172733e303c2f4d61784e756d43686172733e3c2f417474726962757465547970653e3c2f417474726962757465733e3c2f4d656469614974656d4173706563744d657461646174613e00}...

What we can see here is that for every parameter added to the insert statement, there are multiple type conversions happening. One conversion for type GUID and two conversions for type DateTime and String in this case. The reason is probably that (except for binary data) we use DBUtils.AddSimpleParameter method to add the parameters. This method uses kind of a generic database type vs. dot net type mapping - but SQLite doesn't support all these generic database types and maps them to its internal types. I think we could get some further speed improvement, if we used the specific system.data.sqlite methods for adding parameters.
Let's see if this gives us the missing 10 seconds

Michael

 

[breese]

If given the chance, I can put 3 clients at it... 1)client/server 2)Shuttle client 3)Install MP2 Client on MP1 server.
All I need is the new code...
Would also be intrested in seeing what Performace Monitor shows...
btw: Loved every part of that... I was even able to follow along and understand.