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.
I am encountering a similar issue. I have a query without any parameters that takes under 200ms CPU time and 52 total ms to complete when running in SSMS.
But when I run the same query as a named query from the same computer, the query takes over 5 seconds to complete.
I've checked the @@OPTIONS value, and I can get it to read the same for both SSMS and within Ignition if I add SET ARITHABORT ON before running the query, but the query still runs much slower in Ignition.
I did find that the query will run fast in Ignition if I remove one of the WHERE statements, but this causes me to return way more records than I actually want. The WHERE clause is searching for an INT value in an INT column in SQL Server. I have captured the exact query using SQL Profiler, and when I run it within SSMS with the same @@OPTIONS value, it is way faster. I'm stumped.
SSMS doesn't use JDBC, but a native connection to MS SQL Server. MS's JDBC driver must not be taking advantage of the possible optimizations. (Are you using the latest MS JDBC driver in Ignition?)
That's the only difference I could find as well. I don't think we're on the latest MS driver version, so I'll do some testing and see if that makes a difference.
I updated the driver on a test computer and am getting the same results. I may just have to create a Stored Procedure.
I haven't seen any of these performance issues and I typically use stored procedures so I would definitely try that.
Don't know how large the "tbd" table is, but sense removing that from the where clause results in a performance increase, have you insured that you have an index on that column?
The table is fairly large, but I have indexes on all of the appropriate columns. However, if it was just an index issue, I would expect it to also affect the speed of queries coming from SSMS, but that doesn't appear to be the case.
That is not always the case. You need to look at the actual compile/execution paths of the queries that is taking place when the query comes in.
It starting to look like a missing index might have been the issue. I created a Stored Procedure and started seeing it execute more slowly than if I ran the query directly from within SSMS, which made me think this issue wasn't just with the JDBC driver.
Subsequently, I discovered and identity column on a table in a database provided by a 3rd party that did not have an index on it. Once I added that index and a couple of others on the JOINS, it seems to be working much faster in Ignition.
