SQLite3 performance: .obj vs .dll

Eric · 2013-01-10 10:51:18

I've ran a few tests between SynSQLite3 (that uses the .obj compiled by bcc32) and the "official" SQLite3.dll (3.7.15.1 on both sides)

The DLL is obviously compiled with a better C compiler, and it is executing queries about 25% faster overall.

Are there any plans to being able to use the dll instead of the obj? The dll could also be bundled as resource, and extracted as a temp file on startup, to avoid external dependencies)

ab · 2013-01-10 12:08:26

...and/or use another compiler to produce the .obj

But external dll is a good option for multi-platform support.

Eric · 2013-01-11 06:49:04

What other compilers produce Delphi-compatible obj? I quickly looked a while ago, but without much success, if one of the top grade C compilers could be used, that could indeed be convenient.

FWIW the bundling of the dll in the exe is what I've been using for a while, there are two ways to do, one is to simply extract the resource to a temporary file (with a random name, that windows cleans up automatically), this works fine IME, never had any issues with anti-virus with it.
The other is to map the dll directly from the resource. I haven't tried it, this avoid the need to extract anything but requires some low-level wizardry.

ab · 2013-01-11 08:06:03

Visual C compiler can produce Delphi-compatible obj, after conversion.
See http://www.agner.org/optimize/objconv.zip and generally his web site is the sum of all asm fears.

About embedding dll, we are doing this also for a while.
There is even a method in mORMot units to retrieve it either from a resource, either as a binary blob appended to the executable.
See http://synopse.info/forum/viewtopic.php?pid=232#p232
With no file access, just using the mapped executable content.

h.hasenack · 2013-01-15 08:17:42

If I'm right, putting the DLL in the same folder as your executable will make it find 'first', independent of other SQLITE3.DLL's that may be on the system. I use this same technique to put my BPL's in the installation folder. (my plugin system requires the usage of runtime bpl's.).
This avoids any conflicts that may occur if bpl's can be found in the PATH, provided that all required DLL's/BPL's are in the executable folder.

Therefore I would not mind mormot being linked (optionally) to a SQLITE3.DLL, as these are available in both 32bit and 64bit, and does not cause any compiler conflicts regarding the .obj format. (Which I managed to get around using the method stated above)

a potential drawback would be, in case of using the DLL that one cannot easily step into the sqlite3 sources when debugging.

Hans

Last edited by h.hasenack (2013-01-15 08:42:02)

ab · 2013-01-15 12:12:44

Having an external dll is not a problem on the server side, but if you need SQLite3 at the client side (this is not needed now, but I'm considering using it for local client-side storage in order to allow off-line work of a mORMot application), you may encounter the dll hell syndrome.
I really like the fact that Delphi executables can be 100% stand alone. It makes deployment and support a lot easier.

So I'm not against the use of external dlls, but it is not my preferred scenario.

Eric · 2013-01-16 09:00:21

h.hasenack wrote:

If I'm right, putting the DLL in the same folder as your executable will make it find 'first', independent of other SQLITE3.DLL's that may be on the system

If you use dynamic binding, you can load a specific DLL file in any particular path (it doesn't even have to be named '.dll').

I've used that to bundled DLLs as resources of EXE, then on startup they're extracted to the temp directory and loaded from there. If you create the DLL file with the temporary flag in Windows, it doesn't even really get written to the HDD, and you can use file locks to prevent other apps from accessing or modifying it.

That sidesteps the DLL and Windows built-in DLL resolution entirely.

ab · 2013-01-16 10:48:08

About embedding dll, we are doing this also for a while.
See http://synopse.info/forum/viewtopic.php?pid=232#p232

For instance, our SynProject tool is doing it for HunSpell English language check, and GraphWiz diagram generation.
http://synopse.info/fossil/wiki?name=SynProject

h.hasenack · 2013-01-16 15:15:19

Well, I don't see a DLL-Hell problem as long as you regard your application folder (the one with the binaries) as 'holy'. This means that altering or exchanging files in this area by anything else than the installation application is a no-no. (except for de developer of course).
The DLL-Hell mainly existed because every DLL was dnmped in the windows system folder and expected to be sharable between all kinds of applications.

Regarding current prices of disk space and RAM, I just dont care about disk space or some more modules.

I do care about being able to distribute my application in parts (eg plugins) and this brings me back to my bpl/dll solution.
AND, as mentioned before, it is a way to get around any compiler incompatabilities regarding the .obj file format.

ab · 2013-01-16 15:41:28

Note that using an external dll will make the encryption trick of SynSQLite3.pas unavailable, since it hook the low-level WinRead/WinWrite functions.

The "official" encryption callback system of SQlite3 is much more complicated than the hook we used, but is not free.

With an external dll, you have some versions including encryption (either to be paid from sqlite office, either some open source versions).

h.hasenack · 2013-01-16 19:01:40

This would be no problem for me, especially since encryption is provided as open source,

ab · 2013-01-16 19:28:11

The one which seems up to date, includes AES256 and has source and binaries can be retrieved from
http://sourceforge.net/projects/wxcode/ … wxSQLite3/

In the archive, you have for instance a "wxsqlite3-3.0.2\sqlite3\secure\aes256\dll\release\sqlite3.dll" file which seems compiled with Visual C++ and including encryption.

Since the main .dll supplied from http://sqlite.org is compiled not with Visual C++ but GCC, do you notice any speed difference (of course when used without encryption)?

I've created a ticket to track the request. See http://synopse.info/fossil/info/65fe490e9d

h.hasenack · 2013-01-21 08:52:29

ab wrote:

I've created a ticket to track the request. See http://synopse.info/fossil/info/65fe490e9d

Cool!

Are you still working on the other tickets involving some ASM issues that the dcc64 does not like?

Regards - Hans

ab · 2013-01-21 10:23:46

I think we'll switch to an external DLL for all platforms and CPUs, with the exception of the Win32 platform.

Therefore, I suppose we won't need to fix dcc64 issues any more.

External .dll seems faster than dcc compiled version, and x64 exe is already huge, so I do not find any benefit.

(I already stated this in the ticket)

h.hasenack · 2013-01-21 13:58:55

ab wrote:

I think we'll switch to an external DLL for all platforms and CPUs, with the exception of the Win32 platform.

Nice!

ab wrote:

Therefore, I suppose we won't need to fix dcc64 issues any more.

I don't agree to that one. There is still some work to do to be able to compile Mormot in native 64bit. Some inline assembly is still to be altered, as well as some routines that require a "pure pascal" alternative.

ab · 2013-01-21 15:49:25

h.hasenack wrote:

I don't agree to that one. There is still some work to do to be able to compile Mormot in native 64bit. Some inline assembly is still to be altered, as well as some routines that require a "pure pascal" alternative.

I was talking about SynSQLite3.pas unit, and the bcc64 compiler (my mistake).

Of course, SynCommons.pas and mORMot.pas have to be checked and debugged properly to handle Win64 platform.

ab · 2013-02-26 10:31:51

Since revision 1.18 of the framework, our SynSQlite3.pas unit is now able to access the SQLite3 engine in two ways:
- Either statically linked within the project executable;
- Or from an external sqlite3.dll library file.

The SQLite3 APIs and constants are defined in SynSQlite3.pas, and accessible via a TSQLite3Library class definition. It defines a global sqlite3 variable as such:

var
sqlite3: TSQLite3Library;

To use the SQLite3 engine, an instance of TSQLite3Library class shall be assigned to this global variable. Then all mORMot's calls will be made through it, calling e.g. sqlite3.open() instead of sqlite3_open().

See http://blog.synopse.info/post/2013/02/2 … -benchmark

array81 · 2013-02-27 11:45:44

But if I use the DLL library from wxsqlite instead of OBJ, how can use my encrypted database. Must I change my code? How?
What version of sqlite is based this "AES" library? I cannot find details.

Last edited by array81 (2013-02-27 11:48:50)

ab · 2013-02-27 14:36:41

The encryption is not the same.

So you have to first uncypher the file, then rekey() it.

wxsqlite follows latest version from http://sqlite.org
The compiled dll is 3.7.15.1 on their site.

Eric · 2016-10-06 08:43:45

A few years later...

After running some SQLite backup benchmarks against 3.14.2, using the bakup API, I have observed that the DLL is about 15 to 25% faster than the static .obj.

However a backup is essentially an I/O bound task, so the quality of the C compiler should not be playing a very significant role... which makes me wonder if the performance between dll & obj could not come from using different I/O functions, or different I/O options?

Interestingly the performance delta is in the same ballpark as back in the 3.7.15.1 days... so it might have been the same I/O problem.

ab · 2016-10-06 17:01:00

I guess this may be due to the winwrite/winread overriden versions in the static linked version...

mORMot Open Source

#1 2013-01-10 10:51:18

SQLite3 performance: .obj vs .dll

#2 2013-01-10 12:08:26

Re: SQLite3 performance: .obj vs .dll

#3 2013-01-11 06:49:04

Re: SQLite3 performance: .obj vs .dll

#4 2013-01-11 08:06:03

Re: SQLite3 performance: .obj vs .dll

#5 2013-01-15 08:17:42

Re: SQLite3 performance: .obj vs .dll

#6 2013-01-15 12:12:44

Re: SQLite3 performance: .obj vs .dll

#7 2013-01-16 09:00:21

Re: SQLite3 performance: .obj vs .dll

#8 2013-01-16 10:48:08

Re: SQLite3 performance: .obj vs .dll

#9 2013-01-16 15:15:19

Re: SQLite3 performance: .obj vs .dll

#10 2013-01-16 15:41:28

Re: SQLite3 performance: .obj vs .dll

#11 2013-01-16 19:01:40

Re: SQLite3 performance: .obj vs .dll

#12 2013-01-16 19:28:11

Re: SQLite3 performance: .obj vs .dll

#13 2013-01-21 08:52:29

Re: SQLite3 performance: .obj vs .dll

#14 2013-01-21 10:23:46

Re: SQLite3 performance: .obj vs .dll

#15 2013-01-21 13:58:55

Re: SQLite3 performance: .obj vs .dll

#16 2013-01-21 15:49:25

Re: SQLite3 performance: .obj vs .dll

#17 2013-02-26 10:31:51

Re: SQLite3 performance: .obj vs .dll

#18 2013-02-27 11:45:44

Re: SQLite3 performance: .obj vs .dll

#19 2013-02-27 14:36:41

Re: SQLite3 performance: .obj vs .dll

#20 2016-10-06 08:43:45

Re: SQLite3 performance: .obj vs .dll

#21 2016-10-06 17:01:00

Re: SQLite3 performance: .obj vs .dll

Board footer