I am getting pretty good with the reporting module but as my reports get more and more intricate I find my reports sometimes having 10 - 20 individual queries. This is typically because I am selecting from multiple tables that dont have a similar column where I can join them, so I have to query each table individually. The only similar columns are things like Date, shift, employee, etc; things that from my understanding cannot be used to join to another table.
I find this sometimes makes Ignition super slow, especially when I have to query over a longer date range. My question is, is there a better way to select multiple tables with one single query?
Not the answer you want but you most likely need to redesign your database so they do have related fields.
You can join on whatever field you want, but realise it will be slow if it is not an indexed field designed for this purpose, thought it should be faster then you iterating through different tables manually yourself.
Sorry about that I will give a bit more information.
I work for a manufacturing company that has used ignition to build a electronic form for machine operators to input quality paper work. We have a different form for each machine because they all have different tests that need to be performed. Each machine entry is stored into its own database table (grouped by machine) where the columns are each test performed by operators.
So for example, a filler check submitted by an operator would be stored in the fillerQA database table, where some example columns would be date, shift, pearlescence, tamper separation, employee, OutOfSpec etc. An injection check submitted by an operator would be stored in the Injection database table where some example columns would be date, shift, temperature, employee, sometimemeasurement, OutOfSpec, etc. As you can see some columns are similar, but they will never be the same, nor will they have the same amount of columns.
I am trying to create a report that queries certain columns like date, shift, employee etc. from all of the entries with outofspec data from these tables and combine them into one table. Right now since we have about 20 different forms, I have to have 20 individual queries to query the info from the tables and then I use a script to combine the results.
I want to know if there is a way I could do all of this with one query rather than 20 different queries and a script.
You can create a View in the Database that combines the columns you're interested in, and then query the view.
Also, Consider filtering on parts of a columns value. So, perhaps the time may not be the same down to the ms, but perhaps the hour, day, month are?
I would suggest that you look up PIVOT operations, as it sounds like perhaps that is what you're in need of as the identifying value is the actual work cell itself where the "tests" are performed.
Also "super slow" is quite subjective. What does that mean to you? How many rows are you querying total (when it's slow)?
SELECT employee, shift, outOfSpec
FROM injectionEmployees
UNION
SELECT employee, shift, outOfSpec
FROM fillerQA
UNION
SELECT employee, shift, outOfSpec
FROM someOtherTable
UNION
...
Then you can grab from all your unrelated tables into a single dataset in one go. Will not be fast but you can at least get all employees in a single query instead of one per table.
I would recommend doing this in a view as @lrose mentioned and using that.
The other thing to consider if response time is long is that your data tables are not indexed optimally. I would run a sample query on each table and measure the response time. You can do this quite quickly in the Named Query editor or using your database's query console. If you find any that are sluggish then consider creating an index on the multiple fields. e.g., create an index on employee, shift, outOfSpec and run the query again.
@Transistor@zacht@bkarabinchak.psi@n099y All of these are great suggestions. I will do a little bit of research and try out a few of them. Will report back once I find something that works.
Thank you everyone for the help! Very much appreciated!