Union Query Design Best Practice for Speed and Performance (1 Viewer)

Hello,

I have 4 tables in which I plan to join with a union query and be able to query information based off of text boxes in a form. The form will have 5 Text boxes that I can use to limit my queries. Is it best practice to:

1) Have 4 separate select queries (one for each table) that will link to the text boxes from the form, then create a union query?

2) Create a Union query, and then create another select query off of the union query so I can link to the text boxes to the form.

Thank you for your time.

you will HAVE to make 4 queries if you have 4 tables.
just make 4 queries and put all 4 in 1 union qry.

Use your option #2--UNION then apply criteria in a query based on the UNION.

However, I have to ask, why must you UNION instead of having the data in these 4 tables all together in 1 table? Do these 4 tables share the exact same structure?

plog said:

Use your option #2--UNION then apply criteria in a query based on the UNION.

However, I have to ask, why must you UNION instead of having the data in these 4 tables all together in 1 table? Do these 4 tables share the exact same structure?

Click to expand...

I have a data logging program that is logging data from 4 different PLCs to 4 identical tables. Each requires their own table and share the exact structure. I have a frontend database that is linked to the 4 tables.

Could you explain why one way is better than the other so I can apply to other scenarios?

In both scenarios, the same amount of records would be scanned. I am assuming access can only perform one query at a time so the first option would require applying criteria 4 times compared to once. Am i on the right track?

Thank you

By any chance are these tables external to the actual database? Like, are you importing from Excel workbooks or perhaps MAPPING Excel workbooks?

If your data logging program is also in Access, the program should be updated to add one more field to the output to identify the PLC source of the logged event.

However, I might have to disagree with EITHER choice #1 or choice #2 until we learn how the four tables might be scanned. Do these four putative queries have four different criteria, one for each "leg" of the potential UNION query? Or would you always scan all four tables with the same criteria? We need to know WHY you have four filter text-boxes because it makes a difference. The answer to THAT question will determine which of the two options is better.

Each requires their own table

Click to expand...

Why is that? It really sounds like you haven't structured your tables correctly. Why can't all this data go into one table like it should?

The UNION-then-criteria method is better because its simpler for humans to understand and implement. An Access UNION requires writing SQL, there is no Design View, that ups the difficultly level, so you need to make your UNION as simple as possible. It should serve 1 purpose, bring data together--don't aggregate it, don't apply criteria, don't order it--just bring all your data together.

Once you've done that, then you apply criteria. When you do apply criteria, you are now only doing it in one SELECT statement instead of however many individual select statements make up the UNION. So, if you get criteria wrong, or it changes, its an easy one stop fix.

Lastly, if things go sideways and you get unexpected results, its easy to find out where the issue lies. First check the UNION, if the data is in there, then it means the criteria is the culprit. If the data isn't in the UNION, it means the UNION is wrong or the data doesn't exist in the underlying tables.

Thank you Everyone!

The data logging program is connected to access by an ODBC connection. Based off of 4 different triggers (4 different PLCs) I log the data into the respective table. Once logged, I query each table to verify I captured the info and I write back a value to the PLC to confirm. I do have a field to identify the PLC source as I was planning to union the tables together at a later point.

Size of the DB was the main reason why I separated the Tables. I plan to migrate someday to sql (express) to log and host the data and use Access on the front end. I also was not sure if one table could handle four different PLCs all logging data at the same time.

Yes I would always scan all 4 tables for the same criteria. I collect the same data per PLC and Table. I would have a text box on a form for each field I would want to filter. Example Date (MM/DD/YY), Type, Code ,PLC source, location…… I could query based of multiple fields at a time if needed.
I hope I supplied the information needed. Thoughts?
Thank you for your time.

plog said:

The UNION-then-criteria method is better because its simpler for humans to understand and implement. An Access UNION requires writing SQL, there is no Design View, that ups the difficultly level, so you need to make your UNION as simple as possible. It should serve 1 purpose, bring data together--don't aggregate it, don't apply criteria, don't order it--just bring all your data together.

Once you've done that, then you apply criteria. When you do apply criteria, you are now only doing it in one SELECT statement instead of however many individual select statements make up the UNION. So, if you get criteria wrong, or it changes, its an easy one stop fix.

Lastly, if things go sideways and you get unexpected results, its easy to find out where the issue lies. First check the UNION, if the data is in there, then it means the criteria is the culprit. If the data isn't in the UNION, it means the UNION is wrong or the data doesn't exist in the underlying tables.

Click to expand...

Thank you Yes Option 2 would be easier for me to set up maintain. Is there a speed and performance difference between the two options?

The speed/performance issue is USUALLY moot for the difference between those two styles unless you are talking about hundreds of thousands of entries per PLC.

I also was not sure if one table could handle four different PLCs all logging data at the same time.

Click to expand...

If by "at the same time" you mean "simultaneously" then you might indeed have an issue. However, if you mean "as part of the same general process" then there would be no issue in having one table hold data for all four PLCs differentiated by the PLC's ID number.

The data logging program is connected to access by an ODBC connection.

Click to expand...

This invites a question. If the data logging application is written in Access and runs from the same workstation for all four PLCs, you aren't going to get true simultaneity out of that anyway. So when you say "Trigger" - what do you actually mean here? I used to do lab-based data gathering and experimentation, so if there is a hardware trigger that is one thing, but if you are POLLING some device to see if it is ready, then simultaneity is never an issue.

Thanks Everyone for your Input and Time.

On a side note, The logging is simultaneously from a 3rd party data logging software not written in access. When I get some free time, I will try to experiment to see if I can consolidate to one table. As long as size won't be an issue. Thanks again!

Further to the discussion re which is better and why, consider what would you do if you had 5 different PLCs, or 20 or 100 or 1000... There seems to be circumstances in your business processes/set up that may favor one approach vs the other.

Consider this from a concept perspective.
You have supplies entering your "system" on a monthly basis,
you could have 1 table with supplier, item and dateReceived (fields), or
you could have a table for each Month with a Suppler and Item (fields), or
other combinations.
Some will "make more sense to you" from a business and/or technical view.

There are some basic concepts to relational database, but sometimes business processes/circumstances have to take precedence. You have to consider options and decide based on your set up.

Good luck.

Here's the rule of thumb so you can intelligently analyze the "size" issue. You have four tables with identical structure but different sources (PLCs). Each table takes up space in the database. The size of each table is based on the fields that are in it. If all four tables fit in your database now as four separate tables, then your database CAN hold that much data, right?

But if you added one field as an ID for the PLC and merged the tables, each record becomes perhaps one, two, or four bytes bigger. If you had 10,000 records per PLC, that is 40Kb larger, and you STILL have the same number of records in the same database. It doesn't matter whether you have one or four or forty tables, size-wise, because records of the same structure take up the same amount of space no matter where they are.

BUT you actually save a small amount of space by eliminating table definitions for the four tables and replacing them with one table definition for the conjoined tables. (Then promptly offset that small savings with the extra fields to define the PLC ID number.) But in the greater scheme of things, all of it still should fit wihin the 2 Gb structural limit that is inherent in Access.

Where your greater savings will appear is that you don't have to gyrate all over the place to build complex UNION queries. If you conjoin the tables, then you can still write a SELECT query to filter out only one PLC's data, but compared to UNION queries, a single SELECT-with-filter query is almost trivial. So what you save is skull sweat, because you don't have to wrack your brain to embrace the complexities of four parallel tables.

The only fly in the ointment will be the abilities of the 3rd-party software. Having fought with more than a couple of such beasties on a non-Windows system some years ago, I understand that somethings you get painted into a corner. But if your particular beastie can handle the required formatting to single-table-with-included ID, then you are able to get to a place where all the logic simplifies itself.