Odd SQL Query Behavior, 5+ hour query

Ignition
1

I have been running across a weird issue with the following SQL query in ignition:

WITH CTE_TotalProductionRecipe AS (
    SELECT
        module_serial,
        max(speed) as max_speed,
        avg(speed) as avg_speed
    FROM recipe_data
    GROUP BY module_serial
)

SELECT DISTINCT
    Fusion.press_serial as 'module_serial',
    SUM(gross_footage) AS 'output',
	SUM(waste_footage) AS 'waste',
	SUM(net_footage) as 'net_output',
	SUM(gross_impressions) AS 'impressions',
	SUM(waste_impressions) AS 'waste_impressions',
	SUM(net_impressions) as 'net_impressions',
	Format(SUM(net_footage)/SUM(gross_footage),'P') as 'percGoodProduct', 
	Format(SUM(net_impressions)/SUM(gross_impressions),'P') as 'percGoodImpressions', 
    max(max_speed) as 'max_speed',
    max(avg_speed) as 'avg_speed',
    'Footage' as 'production_units'	
    
FROM fusion_production_data AS Fusion
JOIN CTE_TotalProductionRecipe as recipe ON recipe.module_serial = Fusion.press_serial
--LEAVE THIS SPACE
WHERE Fusion.t_stamp BETWEEN :timeStart and :timeEnd
GROUP BY Fusion.press_serial

It works well on what I want it to do, however, every morning I come into work it breaks and runs forever. Every time I debug it some weird way whether it being an arbitrary space, switching the DB to a different one then switching it right back to the required one, or changing the LEFT JOIN to just a JOIN. Is this an issue with my SQL code or could this be a bug with Ignition 8.1.7. Its just very confusing to me that it would work all day then every morning without fail be busted.

2

My first instinct is that this isn’t a problem with Ignition as Ignition really just acts to pass through your SQL to the database, it doesn’t modify or do anything to the SQL itself.

Switching the DB is surely odd though - this happens in the middle of the query? How are you able to tell that is the case?

3

I’m not sure where in the query the hangup is, just that it breaks some time at night, I don’t get any errors with it, and the one day I didn’t notice it was happening I just got a select warning that my query ran for 9 hours. I’m not even sure as to where to start with debugging because just the slightest edit fixes it for the day so it’s hard to reproduce.

4

Have you run this query directly in your databases workbench and seen the similar behavior (ie long query length)? Maybe try putting it into your workbench and running an EXPLAIN statement on it - perhaps one of the columns you are JOINing/WHEREing/GROUP BYing on is not index which could lead to slow query times if your table is large.

5

The tables I am joining are fairly large, but they show no issue straight from the database. Is it possible to run the EXPLAIN statement through ignition? I am not able to access the database directly today.

6

Yes you can, but you can’t do it in the Database Query Browser, you must use system.db.runQuery(), I find it easiest to do this inside the script console. Only caveat is if the db user that Ignition is logged in as doesn’t have permissions to run it.

7

Any possibility that SUM(gross_Footage) or SUM(gross_impressions) could resolve to 0 at the beginning of the Day. This would result in a Divide by Zero, which I would expect to cause an error, but who knows, should definitely insure that the Divide by Zero case is handled.

What DB platform are you using? How often is this query run?

8

I'll look into that in a bit, thank you.

I don't think so because like you said it should error out. it works in the DB platform we use, which is Microsoft SQL Server 2018 (I think that's the name you want I've only been using SQL for a couple months.)