Local Historical Cache

[dblk22vball]

Our company uses SQL servers to log all the data for our test fixtures.  Quality would like me to create a "Data Analysis" tool to monitor daily stats, and have the option of looking at the past 30 days worth of data.

 

Currently, I have been asked to start with just one line (3 test fixtures), which can have 1000 entries each day (some days there are none).  In the future this can grow significantly to other fixtures.

 

IT has asked that I not run a 30 day query every day to update the rolling 30 day stats, a local cache is desired and only the previous days test data will be queried.

 

My current plan:

 

Have a folder in which each Date is a unique file.  I can List all the files in the folder, get any dates between the current date and the newest file via SQL query, then delete any files that are past the "# of Days to Store" ini file value.  This will save me from indexing through the date column of a huge file to delete old data.

 

In order to prevent running analysis on each date every time the historical view is called, I was thinking I could create sections in each Date file (what Quality has asked for on each date):

 

FPY Stats

All Data

Failure Only

SN that were tested more than once

 

Then when the Quality Engineer wants the past 30 days FPY, I get the FPY section of each date in the folder.

 

Has anyone implemented anything like this before in their application?  I am looking for ideas on how to best approach it or does my current idea seem reasonable.

[Tim_S]

I'm not understanding the request to locally cache rather than use a structured query or report within the database. Querying 30,000 records that are joined, filtered, sorted, fold, spindle and mutilated is what databases are meant to do (even when adding records every 10-15 seconds). The computer the database is on does need to have enough oomph to keep up with all that is being asked of it, of course.

 

These look like reports that are normally produced once per day, so performing the report during break and non-production periods can resolve any issues with loading without the need for a local copy.

 

Something you may want to look into is database replication. I've not worked with it myself, but have seen it used to keep two databases (local and main) synchronized. This may allow you to keep a 30-day subset local.

[dblk22vball]

Sorry, I guess I forgot to mention about the SQL server in this case.

 

If the SQL server is local, then a 30 day query will take maybe 30 seconds (not outrageous for a wait time).  

 

But in this case, the SQL server is at a remote vendors site, their manufacturing facility, and they do not have a good internet connection (Malaysia).  So we do a "Linked Server" which allows you to query the remote server through a local server.

 

A 30 day query can take 4 minutes, and I guess they do not want to wait that long.

[ShaunR]

Sorry, I guess I forgot to mention about the SQL server in this case.

 

If the SQL server is local, then a 30 day query will take maybe 30 seconds (not outrageous for a wait time).  

 

But in this case, the SQL server is at a remote vendors site, their manufacturing facility, and they do not have a good internet connection (Malaysia).  So we do a "Linked Server" which allows you to query the remote server through a local server.

 

A 30 day query can take 4 minutes, and I guess they do not want to wait that long.

 

I would suggest you look into SQLite.There are a couple of LabVIEW libraries for it and you will be able to query the remote database and update your local (on-disk) one and pretty much run the same queries on it as you would do remotely.

 

Really, though. It is an infrastructure problem, not a programming one. Your IT department should be offering you solutions rather than passing the buck.

Edited September 19, 2014 by ShaunR