CODEWITHSUNDEEP
pythonhtmldatabase
thomas
thomas

Reputation: 1174

Creating an archive - Save results or request them every time?

I'm working on a project that allows users to enter SQL queries with parameters, that SQL query will be executed over a period of time they decide (say every 2 hours for 6 months) and then get the results back to their email address.

They'll get it in the form of an HTML-email message, so what the system basically does is run the queries, and generate HTML that is then sent to the user.

I also want to save those results, so that a user can go on our website and look at previous results.

My question is - what data do I save?

  1. Do I save the SQL query with those parameters (i.e the date parameters, so he can see the results relevant to that specific date). This means that when the user clicks on this specific result, I need to execute the query again.

  2. Save the HTML that was generated back then, and simply display it when the user wishes to see this result?

I'd appreciate it if somebody would explain the pros and cons of each solution, and which one is considered the best & the most efficient.

The archive will probably be 1-2 months old, and I can't really predict the amount of rows each query will return.

Thanks!

Upvotes: 0

Views: 47

Answers (3)

Rainy
Rainy

Reputation: 1106

The crucial difference is that if data changes, new query will return different result than what was saved some time ago, so you have to decide if the user should get the up to date data or a snapshot of what the data used to be.

If relevant data does not change, it's a matter of whether the queries will be expensive, how many users will run them and how often, then you may decide to save them instead of re-running queries, to improve performance.

Upvotes: 1

ChrisProsser
ChrisProsser

Reputation: 13088

Specifically regarding retrieving the results from queries that have been run previously I would suggest saving the results to be able to view later rather than running the queries again and again. The main benefits of this approach are:

  • You save unnecessary computational work re-running the same queries;
  • You guarantee that the result set will be the same as the original report. For example if you save just the SQL then the records queried may have changed since the query was last run or records may have been added / deleted.

The disadvantage of this approach is that it will probably use more disk space, but this is unlikely to be an issue unless you have queries returning millions of rows (in which case html is probably not such a good idea anyway).

Upvotes: 1

Harsh
Harsh

Reputation: 1072

If I would create such type of application then

  1. I will have some common queries like get by current date,current time , date ranges, time ranges, n others based on my application for the user to select easily.

  2. Some autocompletions for common keywords.

  3. If the data gets changed frequently there is no use saving html, generating new one is good option

Upvotes: 1

Related Questions