Jump to content
shoneill

SQLite open and Read

Recommended Posts

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

Share this post


Link to post
Share on other sites

*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*

Share this post


Link to post
Share on other sites

*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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.......

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites

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()`).

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Similar Content

    • By drjdpowell
      View File SQLite Library
      Introductory video now available on YouTube: Intro to SQLite in LabVIEW
       
      SQLite3 is a very light-weight, server-less, database-in-a-file library. See www.SQLite.org. This package is a wrapper of the SQLite3 C library and follows it closely.

      There are basically two use modes: (1) calling "Execute SQL" on a Connection to run SQL scripts (and optionally return 2D arrays of strings from an SQL statement that returns results); and (2) "Preparing" a single SQL statement and executing it step-by-step explicitly. The advantage of the later is the ability to "Bind" parameters to the statement, and get the column data back in the desired datatype. The "Bind" and "Get Column" VIs are set as properties of the "SQL Statement" object, for convenience in working with large numbers of them.

      See the original conversation on this here.
       
      Now hosted on the NI LabVIEW Tools Network.
       
      ***Requires VIPM 2017 or later for install.***
      Submitter drjdpowell Submitted 06/19/2012 Category Database & File IO LabVIEW Version 2013 License Type BSD (Most common)  
    • By Thang Nguyen
      Hi,
      I would like to know if I can write stream data into two different tables with SQLite at the same time or not? Currently my system has one camera. I store the capture images and particle measurement in one table. I have just add one more camera to the system and would like to do the same thing and write to a second table in a parallel process.  I wonder if this is possible or not.
      I use SQLite library.
      Thanks in advance.
       
    • By McQuillan
      Hi Everyone,
      I (re)watched James Powell's talk at GDevCon#2 about Application Design Around SQLite. I really like this idea as I have an application with lots of data (from serial devices and software configuration) that's all needed in several areas of the application (and external applications) and his talk was a 'light-bulb' moment where I thought I could have a centralized SQLite database that all the modules could access to select / update data.
      He said the database could be the 'model' in the model-view-controller design pattern because the database is very fast. So you can collect data in one actor and publish it directly to the DB, and have another actor read the data directly from the DB, with a benefit of having another application being able to view the data.
      Link to James' talk: https://www.youtube.com/watch?v=i4_l-UuWtPY&t=1241s)
       
      I created a basic proof of concept which launches N-processes to generate-data (publish to database) and others to act as a UI (read data from database and update configuration settings in the DB (like set-point)). However after launching a couple of processes I ran into  'Database is locked (error 5) ', and I realized 2 things, SQLite databases aren't magically able to have n-concurrent readers/writers , and I'm not using them right...(I hope).
      I've created a schematic (attached) to show what I did in the PoC (that was getting 'Database is locked (error 5)' errors).
      I'm a solo-developer (and SQLite first-timer*) and would really appreciate it if someone could look over the schematic and give me guidance on how it should be done. There's a lot more to the actual application, but I think once I understand the limitations of the DB I'll be able to work with it.
      *I've done SQL training courses.
      In the actual application, the UI and business logic are on two completely separate branches (I only connected them to a single actor for the PoC) 
      Some general questions / thoughts I had:
      Is the SQLite based application design something worth perusing / is it a sensible design choice? Instead of creating lots of tables (when I launch the actors) should I instead make separate databases? - to reduce the number of requests per DB? (I shouldn't think so... but worth asking) When generating data, I'm using UPDATE to change a single row in a table (current value), I'm then reading that single row in other areas of code. (Then if logging is needed, I create a trigger to copy the data to a separate table) Would it be better if I INSERT data and have the other modules read the max RowId for the current value and periodically delete rows? The more clones I had, the slower the UI seemed to update (should have been 10 times/second, but reduced to updating every 3 seconds). I was under the impression that you can do thousands of transactions per second, so I think I'm querying the DB inefficiently. The two main reasons why I like the database approach are:
      External applications will need to 'tap-into' the data, if they could get to it via an SQL query - that would be ideal. Data-logging is a big part of the application. Any advice you can give would be much appreciated.
      Cheers,
      Tom
      (I'm using quite a few reuse libraries so I can't easily share the code, however, if it would be beneficial, I could re-work the PoC to just use 'Core-LabVIEW' and James Powell's SQLite API)

    • By Munch
      Good Afternoon,
      I have been having an issue trying to resolve an error all afternoon, and hope that you might be able to help.
      I am trying to execute the follow SQL "
      SELECT m.name as tableName,
             p.name as columnName
      FROM sqlite_master m
      left outer join pragma_table_info((m.name)) p
           on m.name <> p.name
           where columnName = 'UploadedFlag'
      order by tableName, columnName
      " That returns a list of the tables that Contain the 'UploadedFlag' Column.   I have testing the SQL on both HeidiSQL and SQLite Expert Personal applications and run and return what I expect, however when I run it through LabVIEW I get a 402860 Error with the description " SQLITE_ERROR(1): near "(": syntax error " 
      If anyone could point me in the direction of the syntax error then I would be very greatful.
      Regards
      Mark
    • By drjdpowell
      For comment, here is a beta version of the next SQLite Library release (1.11).   It has a significant new feature of a "Parameter(s)" input to the "Execute SQL" functions.  This can be a single parameter or a cluster of multiple parameters.  Uses Variant functions and will be not as performance as a more explicit preparing and binding of a Statement object, but should be easier to code.

       
      drjdpowell_lib_sqlite_labview-1.11.0.86.vip
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.