Performance Comparison Between SQLite and sqlce on Windows Mobile 6

Recently, the company needs to deploy the Desktop System on the PDA and select wm6 as the deployment platform. There are some concerns about database selection, because performance is the primary consideration for PDA.
In the open-source database SQLite and sqlce, we tested the performance of SQLite and sqlce on wm6. I also listed the problems I encountered during the testing process here, so that the friends who had been online for a long time could not find the answer would not take some detours.
Analysis from two aspects: first, the problems encountered and solutions, and second, the test performance results and conclusions.
First: First, copy the database PDA Or Wm The copied path isProgram. My path is "Program Files" smartdevicetest Below.

When you start deployment, Because I first testSqlce, Appears "Cannot be foundPinvoke DLL "sqlceme35.dll" "Error becauseVSDuring automatic deploymentSqlceme35.dllDeploy it on the simulator, causing compilation to pass, but an error occurred.

I have found some solutions online and many of them are unavailable. the following solutions can be used:Http://blog.csdn.net/flyjimi/archive/2008/12/11/3500606.aspx The original Article address is:Http://blogs.msdn.com/sqlservercompact/archive/2007/10/26/can-t-find-p-invoke-dll-sqlcemenn-dll.aspx However, in the solution, I did not understand the specific operation methods. Which three of them are specific?CabFile. Then I will go to the following directory

there are many cab because I use wm5 , therefore, for wm5 I copied all of them to the simulator. copy the selected file to wm5 above, and then to WM run the preceding command, when executing these files, "you have installed the previous version. Click" OK ". Click OK. My installation sequence is: Install from the bottom up in sequence. re-compile, deploy, run, sqlce pass.

SQLite now, you can copy the database to the following directory as before, currently, the database supports the following types: . DB "," . db3 " . later Ltraedit opens the SQLite database and finds that there is a keyword of sqlite3 in it. Therefore, the suffix name is supported no matter what it is used. No suffix is available.

the original format is the same. Deployment and running, "cannot be found pinvoke DLL 'sqlite. interop.064.dll ' vs This is not deployed during deployment. Depressing, there are many problems if there is no precedent. No way, only try. Find sqlite.net installation directory. My options are: D: "Program Files" SQLite. net "bin" compactframework , under this directory, find " SQLite. interop.064.dll "file, copied to the simulator, and found that it still does not work, then, find

in the same directory.

 "SQLite. interop.064.lib. Think about the two, and copy them to the simulator. Note that the copied path is in the same directory as the application. Of course there will be issues that need to be overwritten. Click overwrite.

Compile, deploy, and run. Pass. Very pleased!
Later, I installed a 6.0 SDK and then deployed it on 6.0!
Second: Everything is compiled and executed.


This figure is the main interface used for navigation. It is mainly used for adding, deleting, modifying, and modifying operations. It supports strings and compares them with date types.
Here, a simple SQL statement is used to compare the date types: Select * From T4 where birthday> '2017-09-05 '; here, I will explain it a little bit, in the database, I set this date to nvarchar.
Type, while SQLite supports weak types. Therefore, when I select a date type in the interface and save it to the database, no problem will occur. That is to say, I can save the date type directly to a field of the nvarchar type.

This figure compares the performance of two databases when inserting database data. Here, I used to insert 5000 data records, in ms. Of course, only one test result is provided here. After multiple tests, it is measured that the time for inserting 5000 pieces of data in SQLite is about 4500 to 5000, while that for sqlce is about 23000 to 25000. Therefore, the performance shows that SQLite is about 4 to 5 times of sqlce. Of course, I used transactions here, because I have tested them on the desktop program. If transactions are used, the performance of transactions is dozens of times higher than that of transactions that do not need to be used. In the most funny time, I tested and inserted 10000 pieces of data to the sqlce database on the desktop program, but I didn't use transactions. I woke up at noon and had not completed the insertion. This efficiency is visible.

This figure is used to query the data volume in Table T2 and table T3, and is prepared for the subsequent joint query. Here, I inserted 25000 pieces of data in each database table T2 and T3.

Union query is used here. The statement is simple: Select * From T2, T3 where t2.id = t3.id; is very simple. The query results are recorded here. After multiple tests, the query efficiency of SQLite is 4 to 5 times higher than that of sqlce.
Summary:
SQLite is much more efficient than sqlce in desktop programs and PDAs. Therefore, in the selection of lightweight databases, we 'd better select the SQLite database as much as possible, and it is open-source. During the test, no matter which database, the first query efficiency is relatively low.