Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: FireDAC SQLite slowdown on Mac


This question is answered.


Permlink Replies: 7 - Last Post: Feb 9, 2016 2:07 AM Last Post By: adammary mary
Michael Leahy

Posts: 239
Registered: 5/9/07
FireDAC SQLite slowdown on Mac  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 29, 2015 12:01 PM
My XE7 FireUI app uses FireDAC with SQLite.

What would cause my app to do database transactions about 12 times faster on Windows than on Macintosh?

Sample project is here:
https://forums.embarcadero.com/thread.jspa?threadID=115042&stqc=true

Edited by: Michael Leahy on Apr 29, 2015 5:59 PM to add a sample project.

Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC SQLite slowdown on Mac  
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 4, 2015 1:36 AM   in response to: Michael Leahy in response to: Michael Leahy
What would cause my app to do database transactions about 12 times faster on Windows than on Macintosh?

FireDAC on Windows uses statically linked SQLite engine, which is
updated and compiled by Embarcadero with optimal set of SQLite
configuration defines.

FireDAC on OS X uses dynamically linked SQLite engine, which is
provided by Apple. And most probably it is quite outdated version
of SQLite. This is the reason of performance difference.

You can try to download latest SQLite sources and build your own
SQLite dynamic library for OSX. And then to test and provide it
with your application.

Additionally, verify that you have the same connection parameters
and pragmas for Windows and OSX.

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Michael Leahy

Posts: 239
Registered: 5/9/07
Re: FireDAC SQLite slowdown on Mac  
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 4, 2015 11:20 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
What would cause my app to do database transactions about 12 times faster on Windows than on Macintosh?

FireDAC on Windows uses statically linked SQLite engine, which is
updated and compiled by Embarcadero with optimal set of SQLite
configuration defines.

FireDAC on OS X uses dynamically linked SQLite engine, which is
provided by Apple. And most probably it is quite outdated version
of SQLite. This is the reason of performance difference.

You can try to download latest SQLite sources and build your own
SQLite dynamic library for OSX. And then to test and provide it
with your application.

Additionally, verify that you have the same connection parameters
and pragmas for Windows and OSX.

--
With best regards,
Dmitry Arefiev / FireDAC Architect

Thanks, Dmitry.

You really think an outdated version of SQLite on Macintosh is the reason for a 12 fold performance difference? Maybe an outdated version would make it twice as slow but I doubt it could explain how it is 12 times slower.

I use the same default connection parameters on both Windows and OSX.

What pragmas would be different by default? And which ones might explain the huge difference in performance?
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC SQLite slowdown on Mac
Helpful
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 4, 2015 11:41 PM   in response to: Michael Leahy in response to: Michael Leahy
I played with your code and my results are:

* Windows (running in VM Ware), SQLite v 3.8.7, default params:
** original code - 78 ticks
** wrapped all INSERT's into a transaction - 31 ticks
** wrapped all INSERT's into a transaction and replaced all INSERT's with Array DML - 15 ticks

* Windows (running in VM Ware), SQLite v 3.8.7, LockingMode=Normal:
** original code - 1014 ticks
** wrapped all INSERT's into a transaction - 31 ticks
** wrapped all INSERT's into a transaction and replaced all INSERT's with Array DML - 15 ticks

* OSX, SQLite v 3.7.13, default params:
** original code - 720 ticks
** wrapped all INSERT's into a transaction - 690 ticks
** wrapped all INSERT's into a transaction and replaced all INSERT's with Array DML - 46 ticks

* OSX, SQLite v 3.7.13, LockingMode=Normal:
** original code - 1337 ticks
** wrapped all INSERT's into a transaction - 690 ticks
** wrapped all INSERT's into a transaction and replaced all INSERT's with Array DML - 46 ticks

Yes, the differences are impresive. But between these SQLite versions were a lot of changes:
http://www.sqlite.org/changes.html
Playing with different SQLite pragmas I was not able to figure out what so seriously affects
the performance. This may be WAL setting, locking mode settings, etc. But I still believe
the major difference is in SQLite code changes between these versions.

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Michael Leahy

Posts: 239
Registered: 5/9/07
Re: FireDAC SQLite slowdown on Mac  
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 7, 2015 11:07 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
I played with your code and my results are:

* Windows (running in VM Ware), SQLite v 3.8.7, default params:
** original code - 78 ticks
** wrapped all INSERT's into a transaction - 31 ticks
** wrapped all INSERT's into a transaction and replaced all INSERT's with Array DML - 15 ticks

* Windows (running in VM Ware), SQLite v 3.8.7, LockingMode=Normal:
** original code - 1014 ticks
** wrapped all INSERT's into a transaction - 31 ticks
** wrapped all INSERT's into a transaction and replaced all INSERT's with Array DML - 15 ticks

* OSX, SQLite v 3.7.13, default params:
** original code - 720 ticks
** wrapped all INSERT's into a transaction - 690 ticks
** wrapped all INSERT's into a transaction and replaced all INSERT's with Array DML - 46 ticks

* OSX, SQLite v 3.7.13, LockingMode=Normal:
** original code - 1337 ticks
** wrapped all INSERT's into a transaction - 690 ticks
** wrapped all INSERT's into a transaction and replaced all INSERT's with Array DML - 46 ticks

Yes, the differences are impresive. But between these SQLite versions were a lot of changes:
http://www.sqlite.org/changes.html
Playing with different SQLite pragmas I was not able to figure out what so seriously affects
the performance. This may be WAL setting, locking mode settings, etc. But I still believe
the major difference is in SQLite code changes between these versions.

--
With best regards,
Dmitry Arefiev / FireDAC Architect

Thanks again, Dmitry.

I am totally new to both SQL and SQLite.

For example, the SQLite documentation says of this pragma... "Only Mac OS-X supports F_FULLFSYNC."
I have no idea what that setting does and I cannot find an explanation even when searching Google with "site:sqlite.org F_FULLFSYNC"

I also don't know what is meant by "wrapping all INSERTs into a transaction" or an "Array DML".

My project inserts positions from chess games so each INSERT could mean that any of the following transactions must be UPDATEs if the position matches the key from an earlier INSERTed record. This happens often.

Is it possible to compile and deploy an optimized copy of SQLite with an OS-X app?
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC SQLite slowdown on Mac
Correct
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 7, 2015 11:13 PM   in response to: Michael Leahy in response to: Michael Leahy
For example, the SQLite documentation says of this pragma... "Only Mac OS-X supports F_FULLFSYNC."
I have no idea what that setting does and I cannot find an explanation even when searching Google with "site:sqlite.org F_FULLFSYNC"

https://www.sqlite.org/pragma.html#pragma_fullfsync
FDConnection1.ExecSQL('pragma fullsync=true');


I also don't know what is meant by "wrapping all INSERTs into a transaction"

http://docwiki.embarcadero.com/RADStudio/XE8/en/Managing_Transactions_(FireDAC)
FDConnection1.StartTransaction;
FDQuery1.ExecSQL('insert into ....');
FDQuery1.ExecSQL('insert into ....');
FDQuery1.ExecSQL('insert into ....');
FDConnection1.Commit;


... or an "Array DML".

http://docwiki.embarcadero.com/RADStudio/XE8/en/Array_DML_(FireDAC)

Is it possible to compile and deploy an optimized copy of SQLite with an OS-X app?

https://www.sqlite.org/custombuild.html

PS: In next RAD Studio version we will try to provide optimized SQLite engine for OSX.

--
With best regards,
Dmitry Arefiev / FireDAC Architect

Edited by: Dmitry Arefiev on May 8, 2015 10:13 AM
Michael Leahy

Posts: 239
Registered: 5/9/07
Re: FireDAC SQLite slowdown on Mac  
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 11, 2015 1:11 PM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
For example, the SQLite documentation says of this pragma... "Only Mac OS-X supports F_FULLFSYNC."
I have no idea what that setting does and I cannot find an explanation even when searching Google with "site:sqlite.org F_FULLFSYNC"

https://www.sqlite.org/pragma.html#pragma_fullfsync
FDConnection1.ExecSQL('pragma fullsync=true');


I also don't know what is meant by "wrapping all INSERTs into a transaction"

http://docwiki.embarcadero.com/RADStudio/XE8/en/Managing_Transactions_(FireDAC)
FDConnection1.StartTransaction;
FDQuery1.ExecSQL('insert into ....');
FDQuery1.ExecSQL('insert into ....');
FDQuery1.ExecSQL('insert into ....');
FDConnection1.Commit;


... or an "Array DML".

http://docwiki.embarcadero.com/RADStudio/XE8/en/Array_DML_(FireDAC)

Is it possible to compile and deploy an optimized copy of SQLite with an OS-X app?

https://www.sqlite.org/custombuild.html

PS: In next RAD Studio version we will try to provide optimized SQLite engine for OSX.

--
With best regards,
Dmitry Arefiev / FireDAC Architect

Thanks again. An optimized version of SQLite for OSX would be welcomed.

I did find that paragraph on F_FULLFSYNC but it did not explain what it does at all.
adammary mary

Posts: 1
Registered: 3/9/16
Re: FireDAC SQLite slowdown on Mac  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 9, 2016 2:06 AM   in response to: Michael Leahy in response to: Michael Leahy
My app uses FireDAC (Delphi XE7) to access SQLite.

The Mac version is about 12 times slower than the Windows version.

I have journal_mode turned off, and no journal files appear. http://www.traininginsholinganallur.in/php-training-in-chennai.html

The only difference I can see is that the Mac has checkpoint_fullfsync turned on by default (it's turned off by default in Windows). I've turned it off on the Mac but the 12X slowdown persists.

The Mac is the latest Yosemite. SQLite3 -version reports 3.8.5.

Windows 7 is using whatever version of SQLite3 is baked into FireDAC.

What would cause such a huge difference in performance? http://www.traininginsholinganallur.in/web-designing-training-in-chennai.html

Edited by: adammary mary on Feb 9, 2016 2:06 AM
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02