SpatiaLite and PRAGMA cache_size

Topics: Data Access, SharpMap v0.9 / v1.x, WinForms Controls
Mar 12, 2013 at 12:51 PM

Is there any way for the SpatiaLite or ManagedSpatiaLite provider to enter an optimalization command as PRAGMA cache_size = 2000; ? It is only valid in the current session.
My 80k segments SpatiatiaLite table is somewhat slow on Refresh; I have already changed PRAGMA page_size = 2048;

Many thanks,

Mar 12, 2013 at 1:54 PM
Can this be defined with the connection string (not sure)?.
Other than that, the SpatiaLite providers (both managed and normal) use GetConnection function to open a connection. This would be the point where you'd execute your PRAGMA command.

If you come up with a solution, please provide a patchfile
Hth FObermaier
Mar 12, 2013 at 2:34 PM
FObermaier wrote:
Can this be defined with the connection string (not sure)?.
It can, see connection strings for SQLite
Mar 12, 2013 at 3:53 PM
Edited Mar 12, 2013 at 3:59 PM
Thanks mr Obermaier.

Yes, that works. I made the cache_size dependent on the filesize (e.g. 78MB = 700.000), but the speed gain was not great. Now I will try to change the page_size, it was 4096.
I was looking on [] but to put the setting in the connectionstring is better.


Update: page_size not changed