Hello There,
I am probably seeing something weird or missing out something (I think its the second). When I execute a query in Ignition (through Client, designer or Script console), the query takes around 30 seconds. But, the same query takes less than a second to execute in SSMS.
Any idea ?
Thanks,
Kaushik
Ignition doesn’t connect to the database with all the same parameters set as SSMS does, by default. You can change Ignition’s connection settings to try to match SSMS, if you can figure out exactly how SSMS is connecting. I believe there’s one in particular about arithmetic/rounding that I’ve seen cause issues in the past…
Found it - try SET ARITHABORT ON before executing the query:
I was about to try SET ARITHABORT ON. But the query from yesterday worked completely fine. I just don’t know how.
Well, I have a tool to experiment if this happens again.
Thanks so much,
I was having a similar issue and SET ARITHABORT ON worked for me as well.
We had a similar case recently. Our database server is MS SQL Server 2016. Log files were showing that some queries are taking 9 - 10 seconds in some cases. Query is a SELECT statement, which returns one last record for its ID from a table with several millions of records.
I have copied this query to SSMS to identify the problem. It turned out, that the same query takes only a couple of milliseconds when executed from SSMS. In Ignition, the query is executed just as quickly when a record is found. However, when we search for a non-existent ID, the query takes 9 - 10 seconds. Changing SET ARITHABORT did not have any effect.
Since this query is called via system.db.runNamedQuery, I also tried calling via system.db.runPrepQuery, but the result was the same.
I then saved the query as a stored procedure on the MS SQL server. Calling this procedure from Ignition then drastically speeded up execution in cases where the ID was not found, and a smaller speedup was also noticeable in cases where the ID was found.