SQLite open and Read

[shoneill]

A new SQLite question:

 

I have, during testing, created several multi-GB files which I can use for read testing.  I notice that upon first access (after loading LabVIEW) the query to get data takes sometimes exactly as long as if the file was not indexed at all.  Subsequent reads are fast (drops from 30sec to 100 msec).

 

1) Has anyone else seen this?

2) Is this normal (Loading Index into memory)

3) How do I make the first file access fast?

 

Shane

[shoneill]

*EXPLETIVES HERE*

 

It seems I'm a victim of OS disk access buffering.

 

I have, in my stupid naivety, been using the same query again and again for speed testing.  Problem is that (as Ihave now seen) every time I change my decimation factor (i.e. access different portions of the disk) the read speed is completely destroyed.

 

Opening file X and running Query A 100 times has one slow and 99 fast queries.  Changing the Query to B makes things slow again (followed by subsequent fast queries), and changing to C again slows things down and so on and so forth.  Differences can be huge, from 30ms (fast) back to 34 seconds if I want to access different disk areas.  Am I insane to not have thought of this before?  Is the OS buffering or is it SQLite?  It seems like SQLite may also have just died for my application.

 

I initialise with:

PRAGMA temp_store=MEMORY; (Could this be part of my problem perhaps?)
PRAGMA cache_size=50000; (Surely this should only speed things up, right?)

 

And I query using:

WITH RECURSIVE CnT(a) AS
  (
     SELECT 0
     UNION ALL
     SELECT a+1 FROM CnT
     LIMIT 1000
  )
  SELECT X,Y,Z,Float1 FROM P JOIN CnT ON CnT.a=P.X WHERE Y=0

 

Just changing the step size in my cte from 1 to something else causes massive slowdown int eh subsequent access.  Of course my application will be performing many different queries.  It seems like either the OS or SQLite itself was fooling me.  I started getting suspicious about a magnetic disk even being able to return 1000 data points in 30 msec, it doesn't add up physically.

 

*MORE EXPLETIVES*

[shoneill]

*quack*

 

Just to make it clear I'm a power rubber ducker.

 

I think part of my problem may be down to different versions of the SQLite DLL.

 

I was using the "original" SQLite Library from drjdpowell but with an up-to-date SQLITE DLL from the web.  I just downloaded the newest version and let it run in LV 2015 in a VM and all queries seem to be fast.

 

If drjdpowell is watching, any input on that regard?

[drjdpowell]

If drjdpowell is watching, any input on that regard?

Difficult to say.  There is caching by the OS, caching by SQLite (though 50,000 pages isn’t that much for a multi-GB file), and also the Query Planner that can greatly affect things.   Have you run Analyze?   There is a custom probe for Prepared Statements that runs “EXPLAIN†to help debug Planner issues.   The Query Planner is upgraded with newer versions of SQLite so that could be an issue.  Or OS caching can have a big effect on reads.

[shoneill]

I have a request pending to stop the world so that I can disembark......

 

Maybe I have something on my physical PC getting int he way of the file access...... I have just saved the newest version of your package to LV 2012 and run the software there.  There still seemt o be differences.  Either LV 2012 has some significant differences or I have processes running on my physical PC which is interfering.  Either way, benchmarking this is turning into a nightmare.

 

On a side note: How on earth does SQLite manage to get 1000 decimated data points (with varying spacing) from a multi-GB file in under 100ms given that I'm accessing a standard HDD, not an SSD.  That's very few disk rotations for so much data.......

[shoneill]

The answer to my surprisingly fast read times (Given the physical work required to actually perform head seeks and factoring in rotational speed of HDDs) is that Windows does a rather good job of transparently caching files int he background.  If you look at the area labelled "Standby" on the memory tab in Task Manager - this is cached file data.

 

Search for RamMap from Sysinternals for a tool to allow you to empty this cache and otherwise view which files are in cache and which portions of the files..... Very interesting stuff.  And stuff I knew nothing about until a few days ago.

[Jordan Kuehn]

Do you happen to have a SSD?

[shoneill]

I do, but testing was done on a HDD.

[JackDunaway]

Are all your variables bound to your prepared statement?

 

For example, rather than

SELECT foo FROM table1 WHERE col='bar'

the statement 

SELECT foo FROM table1 WHERE col=@value_to_match -- then later binding 'bar' to @value_to_match

 is going to be more performant (over multiple queries, especially), more precise (for floating-point numbers), and more secure (against SQL Injection Attack, assuming that the original `bar` was inserted at run-time using the likes of the labview `Format to String` function or C `snprintf()`).

[shoneill]

Yes, everything is done via binding to prepared statements.

 

None of this in my code: