Queries, VBA or server-side

[cigarprofiler]

For my project I want to make some complex calculations. One calculation requires six consecutive queries, each one building on the results of the previous. This calculation has to be applied to ten categories, so 60 queries in total.

I have made the six queries using the Access interface. Easy as pie. I have copied the SQL to a text file and could modify the WHERE statements and the category variables, and then create the remaining 54 queries by copy-pasting. Not the most interesting job but quick and painless.

I do have some questions, though:

1. Is it better to write some VBA code for this? With a bit of Select Case and some sub-routines, the whole thing might be somewhat more elegant.

2. Is there a performance difference between VBA and queries? I would think not since it's all just a bunch of SQL queries, but what do I know?

3. There is some clever thinking involved in these queries, if I may say so myself. I'm considering performing the queries server-side as stored procedures, so that they are better secured. My thinking here is that it's probably easier to hack an Access frontend than a Postgresql backend, would that be a correct assumption?

Thanks for your thoughts!

 

[CJ_London]

(data manipulation) using vba instead of a query will be very much slower

SQL created and run from vba will be very slightly slower than a query itself because a query plan needs to be created. Also if one query references another then the other needs to exist as a query.

queries already created and run from VBA will be faster than running them manually

if you are using sql server/oracle backend then stored procedures will be the fastest with passthrough queries a close second. Note that the sql although similar is not the same and you will not have access to vba functions such as nz or iif. Also if using group by queries T-sql does not support first/last.

by all means, use vba to construct your 54 queries using the ones you have done as a template. May be quicker than doing it manually, maybe not.

 

[cigarprofiler]

...and you will not have access to vba functions such as nz or iif. Also if using group by queries T-sql does not support first/last

Good to know, that's a deal breaker right there. I'll just have to rely on the math being so complex that a hacker would have a hard time figuring out the algorithm. God knows I need my notes to remember what I did and why...

 

[CJ_London]

in Tsql, instead of nz, you use isnull (which is different to the vba isnull function) and instead of iif you use the 'case when' construct.

Don't dismiss it out of hand if performance is an issue. I have a complex series of queries which take about 3 seconds to run on an access backend on my C drive. It takes less than a second on sql server.

 

[cigarprofiler]

OK, I'll keep an eye on performance and check with my buddy who set up the postgresql backend for me about the possibilities for calculations in stored procedures.

 

[plog]

I really question your requirement of 60 queries. Actually, I question the need for the 6 base queries. Let's tackle the 10x multiplier first.

Instead of 10 queries for each category, you only need 1 query and then an additional field that Groups each category. For example, if you wanted to count how many people are in each department of an organization, you wouldn't have a query for each department (qryHumanResources, qryIT, qrySales, qryAccounting, etc.). You would create a field in the query to determine what department each record was for. I think that principle can be applied to the 10 queries you think you need.

As for the 6 base queries, I really think you can break down the math on those and incorporate them at a different level. For example if one query calculates FieldZ as FieldX + FieldY and FieldA as FieldB + FieldC, and then you have another query that generates FieldM as FieldA + FieldZ, you can simply have FieldM=FieldB + FieldC +FieldX + FieldY.

I know you have a solution that works, but I really think you can make it more efficient in a manner of ways.

If you would like to post yourr database I would be willing to give a few specific pointers.

 

[cigarprofiler]

Posting the db might be tricky, I haven't secured anything yet since I'm the only user so far. Let me see if I can make a local copy. Anyways, thanks for your offer to help!

 

[cigarprofiler]

I created a sample db with the relevant tables and queries. This application is a tool to create cigar reviews. While smoking (which can take up to an hour or more), the user can enter flavour observations in ten categories (which are stored in public_tblreview_item_types).

With these queries, I want to measure the variety of flavours in a review (typically called the “dynamics” of a cigar). As a cigar smoker, you want two things: variety between flavours (which is a simple count: the more, the better), and variety within each flavour while smoking. The theoretically ideal profile consists of many flavours which are evenly spaced in time.

To quantify the time aspect, I need the following:

Total review length (qry_timestamps_start_review, qry_timestamps_end_review, qry_results_review_duration)

The number of observations per review in a flavour category, and the time between the first and last observation (qry_observations_summary_animal, qry_max_interval_animal).

Next, I calculate the average interval as actually measured between first/last observation within a flavour category, and the theoretically ideal interval if the observations were perfectly spaced across the entire review (qry_avg_interval_animal)

Finally, I determine how closely the actual interval approaches the ideal interval (qry_dynamics_animal).

When all the calculations are done, I want to present the result as a single percentage for the dynamics of the cigar. I haven’t created the queries for that yet, as Access complains about the query being too complex.

These calculations have to be made per review, which is what I group on. I guess it would be ideal if you could group on review and then flavour, but I don’t know how to do that; especially the time aspect gives me headaches.

 

[plog]

Writing a complex series of queries is like writing a research paper--you start with a rough draft that contains every piece of information you think you might need, then on the second pass you condense and remove unnecessary parts. Your queries where a good rough draft because it produced what you wanted, but it had a ton of extra fields and queries that could be more concisely written.

I was able to achieve qry_avg_interval_animal in a total of 3 queries. Attached is that database with those queries. The query named Reviews is the main one and gives you the exact data qry_avg_interval does.

Essentially, my process was to start in qry_avg_interval_animal and track down exactly how each of its fields was determined. I opened up each subquery until I found the table it ultimately came out of and determined a more efficient path between the table and the final query.

 

[cigarprofiler]

Thank you for your time, plog, I really appreciate it.

Since your post yesterday, I was thinking about condensing the queries along the lines you suggested yesterday. But in the db you posted, you also used a few tricks I wasn't familiar with yet, like putting a table in a query twice. Is there a limit to how often you can do that? Because that might be a way get all ten categories in one query set, rather than repeating ten times.

Anyway, I'm gonna study your queries and experiment with them, and hopefully learn. Thanks again for your help!

 

[plog]

I'm sure you can put a table in a query 10 times, I think problems occur somewhere in the triple digits. However, I'd want to know why you want to do this 10 times? It might actually be better to either do a cross-tab query or find another way to achieve what you want.

Let me know if you have any questions about what I did.

 

[cigarprofiler]

From looking at your queries, I also noticed an erroneous assumption I have been working under, i.e. that you can't use the result of a calculation in a query for another calculation in that same query. Turns out I just used the wrong syntax and misinterpreted the error message.

So now the query set is reduced from six to three thanks to your help. I'm not familiar with crosstab queries, but I'm going to look into that next.