SQL Timeout slow query

Hi, I have this issue:

WARN com.inductiveautomation.ignition.client.gateway_interface.GatewayConnectionManager - Connection to Gateway lost, due to exception.
com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Read timed out

The Problem is that the query takes 3:40 minutes in SQL Studio. But guess Ignition times out before that… How can I boost the time before time out?

My first suggestion would be to do some serious work on why this query is taking so long. I’ve done some super complicated queries involving hundreds of tables that take less than a second to return, once I got the table structure and indexing tuned. If you’re doing things like count or sum in your query, look at whether you can pre-aggregate data. And finally, make sure your db has enough memory and a fast drive – databases are memory hogs and I frequently see dbs just way too starved for memory.

Once you do all that, you might consider using a db tag for your query if you still have problems with time outs.

This is a production machine supplied by a vendor. We just replaced it. The Old one had the issue with SQL Studio, but executed perfectly (in a second) in Ignition. I have no control on that machine except for a read only user to query views. Can’t do indexes or anything (Not if I could if I could anyway…) He’re the query if you can find any better fix

Sorry my comments are in French :stuck_out_tongue:

The goal is to get my closed bundles ([AutViewPackageHeader]) from a Shift with the Sum of BoardFeet in [AutViewTallyInventory] by Product Code (ScoopCode).

I’m trying a different approach now, by dumping bundle by bundle with it’s Tally info a temp table then do the sum on that table and the string formatting.

/*Dans mon script en python, j’execute chaque requête dans des transactions séparées et met la valeur dans
une variable pour l’utiliser dans les autres requêtes d’après, au lieu tout faire dans une requête comme ici.
Étrangement dans mon script en python, qui utilise un driver SQL en Java, tout le script s’exécute en moins
de 5 seconds… Celui si dans Management Studio, il prend 3 min à 4 mins…
*/
DECLARE @Faction varchar(25),
@Equipe varchar(25),
@Debut datetime,
@Fin datetime,
@Shift int,
@Entrepot int

SELECT @Shift=4200

SELECT @Faction=(SELECT(REPLICATE(’ ',25-LEN(RTRIM([Shift])))+ [Shift])
FROM [AutologACCTrimmerHardWood].[dbo].[AutViewShiftHeader]
WHERE IDDetail = @Shift
)

SELECT @Equipe=(SELECT(REPLICATE(’ ',25-LEN(RTRIM([Crew])))+ [Crew])
FROM [AutologACCTrimmerHardWood].[dbo].[AutViewShiftHeader]
WHERE IDDetail = @Shift
)

SELECT @Debut=(SELECT [StartDate]
FROM [AutologACCTrimmerHardWood].[dbo].[AutViewShiftHeader]
WHERE IDDetail = @Shift
)

SELECT @Fin=(SELECT [EndDate]
FROM [AutologACCTrimmerHardWood].[dbo].[AutViewShiftHeader]
WHERE IDDetail = @Shift
)
/Changer à 210,300 ou 400/
SELECT @Entrepot=210

/*Dans mon script j’execute une requête qui me donne les entrepots dans un array et je for loop par entrepot.
Je ne pouvais pas faire pareille ici, alors il faut executé la requête par entrepot manuellement.
*/

SELECT ‘QUE’ AS Localisation,
(SELECT([ScoopCode] + REPLICATE(’ ‘,25-LEN(RTRIM([ScoopCode]))))
)AS CodeProduit,
(SELECT
(SELECT([Specie] + REPLICATE(’ ‘,25-LEN(RTRIM([Specie]))))
) AS Specie
FROM [AutologACCTrimmerHardWood].[dbo].AutViewSpecieHeader
WHERE IDDetail = IDDetailSpecie
)AS Specie,
(SELECT [SortTbl]+(REPLICATE(’ ',25-LEN(RTRIM([SortTbl]))))
)AS Classement,
(SELECT(
REPLICATE(‘0’,25-LEN(RTRIM(CONVERT(varchar(10),CONVERT(DECIMAL(20,4),SUM([BoardFeetOut]))))))
+CONVERT(varchar(10),CONVERT(DECIMAL(20,4),SUM([BoardFeetOut])))
)
)AS PMP,
@Faction as Faction,
@Equipe as Equipe,
(SELECT CONVERT(varchar, @Debut, 101)
)AS DateDebutFaction,
(SELECT LEFT(CONVERT(varchar, @Fin, 108), 5)
)AS HeureDebutFaction,
CASE
WHEN Warehouse is NULL
THEN 210
ELSE Warehouse
END as Entrepot

FROM [AutologACCTrimmerHardWood].[dbo].[AutViewTallyInventory] AS TALLY
WHERE [Pile] = 1 AND Warehouse = @Entrepot
AND IDDetailPackage IN
(SELECT [IDDetail]
FROM [AutologACCTrimmerHardWood].[dbo].[AutViewPackageHeader]
WHERE EndDate BETWEEN @Debut AND @Fin
)
GROUP BY [ScoopCode],IDDetailSpecie,[SortTbl], Warehouse
ORDER BY ScoopCode

Result looks like:

Localisation	CodeProduit	Specie	Classement	PMP	Faction	Equipe	DateDebutFaction	HeureDebutFaction	Entrepot
QUE	TLVE1U0440RW070          	Tremble                  	Tremble cour             	00000000000000001289.0000	                    Jours	                    # 211	08/11/2017	17:15	210
QUE	TLVE1U0440RW080          	Tremble                  	Tremble cour             	00000000000000000945.0000	                    Jours	                    # 211	08/11/2017	17:15	210
QUE	TLVESU0440RW070          	Tremble                  	Tremble cour             	00000000000000002252.0000	                    Jours	                    # 211	08/11/2017	17:15	210
QUE	TLVESU0440RW080          	Tremble                  	Tremble cour             	00000000000000001908.0000	                    Jours	                    # 211	08/11/2017	17:15	210

Eric

You’re forcing the database engine to do a lot of formatting. It’s not a good idea since they’re not designed for that. Let Phyton do it. Get your dataset raw and run a formatting process on it. The queries are not complex, they look like it because of all the formatting.

Besides that, it’s not easy to work on performance without access to the table/index structure. But you may be able to get some improvement by working with your Where clauses. Put always the most restrictive condition last. Think about which one would give you the smallest recordset. This way, each filter along the line gets the less possible work load.
I’m not sure about MS SQL, this rule works in most databases, Oracle, MySQL. But I don’t work much with MS, maybe they parse the other way around… or randomly…

1 Like

Well good news, the SQL Genius of my vendor came back from her vacation. And she fixed the sloppy job of her colleague. The 3:40 minutes query takes 10 secs now!

Thanks to all of you for your input and tips. I’ll take them on the next project.

1 Like

@KathyApplebaum, I have a similar issue where I for various reasons are not able to touch the db to make it behave. I’s a single stored procedure call that Ignition seems to time out after 60 seconds no matter what I do. Can you conform that there is no setting or property that I can change that will extend the timeout?

If your query is timing out in the Client/Designer:
In the menu-bar go to Project->Properties->Client/Timing and change the timeout durations.

The default read timeout is 60,000ms or 60s.

2 Likes

I am getting this same error in my client on a screen that I have a button that runs
script similar this this:
try:
SProcCallMax = system.db.createSProcCall(‘PalletGetUnProcessedCR’, DBNAME)
SProcCallMax.registerInParam(1, system.db.INTEGER, lCell)
system.db.execSProcCall(SProcCallMax)
ds = SProcCallMax.getResultSet()
if ds is not None and ds.getRowCount() > 0:
###script code here (omitted)
except:
#log some exception script here (omitted)

Once I get this exception, all QUERY tags in ignition are failed and will not recover on the HMI.
They show up as RED boxes on the screens.
Also, on a different screen that has a TABLE on it that ALWAYS works. After the timeout,
it does not work.

It’s like once the exception “Timeout” occurs, then the entire HMI is broken.
Even though I “caught” the exception and logged it.

What am I doing wrong?
Why is the connection to the database unrecoverable after this error?
Is this some type of “special” error?

Thanks

The only solution is to shutdown the client and start it up again.
Then