I require a service that will run every 15 minutes to generate "up-to-date" JSON for our AJAX services to consume. We are wanting to use this JSON for metric analysis, charts, all that jazz.
The reasoning for running every 15 minutes is that the JSON generation is long-running - the queries take at least 45-60 seconds to complete. This is expected to increase as the needs of our application increase.
EDIT: We pull all our data from a SQL database. We already have a huge relational database set up. Getting the data in a fast, efficient time to our web users is absolutely necessary. Therefore, querying the database for all the data we need for metrics on the fly is not satisfactory as it takes too long.
EDIT 2 The data needs to be completely regenerated every 15 minutes. The JSON data needs to be available almost instantaneously. We aren't concerned whether the background service takes long, as long as it generates our JSON every 15 minutes.
The problem is that I am unsure on how to store this JSON. There will be at least 30-40 separate JSON objects generated and serialised to strings for transmission. I'm not sure how I should go about storing these strings. Also, these 30-40 will only increase over time. We also have preset time periods for generating the JSON, these are 1 day, 1 week, 1 month, 3 months, 6 months, 1 year, 2 years.
I have considered a flat database table, though I loathe tables that have dozens of columns and only one row (it doesn't seem right to me). I've wondered whether it would be possible to generate some kind of "data.json" file on the server which the services would pull data from when necessary, but does this suffer from any kind of drawbacks? Can it be cached? (caching would undermine the point of this).
Is there some other method available? Can an expert give a solid opinion on this?
I think you're taking this example as the only source of your data by having a look at some other giant company's API (Tumblr., Facebook etc).
But I would like to make it clear to you, using Database to store the Data would be more efficient as compared to store the data in JSON.
Storing the data in the File would require you to create your own plugins to handle the data inside the File, to detect whether the data is OK, whether the User's input is OK, whether the Data matches, if it does, what other data can relate to it. That would be a great time for coding for you. If you use Databases on the other hand, you can just select the ROWS and COLUMNS that you want, make a JSON string of that data returned and then give it back to the client as a JSON object. That's what you want.
Use the Database, create a table for every possible JSON Object that you'll use. Like this
ObjId | ObjName | ObjProperty
1 | User | Employee
2 | User | Client
This is an example table for you. Once you're done with saving that data. You can always get the data back with a simple SELECT statement as
SELECT * FROM table_name WHERE ObjId = 1
Now, the first row would be returned to you. Stringify it, then return it as a string to the client to work on it.
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/JSON/stringify
Since the data will be served statically, and not generated on the fly, I suggest just storing them as text files. It's fast, simple and scales well. It easy to make an archive of old data, or you could just overwrite the old files if you don't need history.
To avoid caching of the json files, you can eiter do it client side
$.ajax({ cache: false })
Or you can do it in the headers server side when serving the file. Here you can also set cache time to 15 minutes, to minimise load on your server. How this is done, depends on what technology you are using.