# Slow Query in Ignition **URL:** \ **Category:** Ignition\ **Tags:** database\ **Created:** [February 9, 2022, 2:07pm UTC](https://forum.inductiveautomation.com/t/slow-query-in-ignition/55908 "2022-02-09T14:07:50Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![kaushik.shah](https://avatars.discourse-cdn.com/v4/letter/k/34f0e0/32.png) [@kaushik.shah](https://forum.inductiveautomation.com/u/kaushik.shah)\ **Post date:** [February 9, 2022, 2:07pm UTC](https://forum.inductiveautomation.com/t/slow-query-in-ignition/55908/1 "2022-02-09T14:07:50Z")
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 ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [February 9, 2022, 7:07pm UTC](https://forum.inductiveautomation.com/t/slow-query-in-ignition/55908/2 "2022-02-09T19:07:04Z")
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… ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [February 9, 2022, 7:10pm UTC](https://forum.inductiveautomation.com/t/slow-query-in-ignition/55908/3 "2022-02-09T19:10:46Z")
Found it - try SET ARITHABORT ON before executing the query: > **[SQL Server stored procedure runs fast in SSMS and slow in application](https://www.mssqltips.com/sqlservertip/4318/sql-server-stored-procedure-runs-fast-in-ssms-and-slow-in-application/)** > > Our users complained about slowness in a few areas of our application. We ran Profiler and found that a few procedures were taking more than 6-10 seconds. I manually ran those same procedures in SSMS and they executed in less than second. Why? ---
**Author:** ![kaushik.shah](https://avatars.discourse-cdn.com/v4/letter/k/34f0e0/32.png) [@kaushik.shah](https://forum.inductiveautomation.com/u/kaushik.shah)\ **Post date:** [February 10, 2022, 9:14am UTC](https://forum.inductiveautomation.com/t/slow-query-in-ignition/55908/4 "2022-02-10T09:14:41Z")
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, ---
**Author:** ![nick.miller](https://avatars.discourse-cdn.com/v4/letter/n/77aa72/32.png) [@nick.miller](https://forum.inductiveautomation.com/u/nick.miller)\ **Post date:** [April 24, 2023, 7:43pm UTC](https://forum.inductiveautomation.com/t/slow-query-in-ignition/55908/5 "2023-04-24T19:43:12Z")
I was having a similar issue and SET ARITHABORT ON worked for me as well. ---
**Author:** ![Jani\_Kleindienst1](https://avatars.discourse-cdn.com/v4/letter/j/dc4da7/32.png) [@Jani\_Kleindienst1](https://forum.inductiveautomation.com/u/Jani_Kleindienst1)\ **Post date:** [March 28, 2024, 6:42am UTC](https://forum.inductiveautomation.com/t/slow-query-in-ignition/55908/6 "2024-03-28T06:42:51Z")
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. ---
**Author:** ![rbachman](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/rbachman/32/43718_2.png) [@rbachman](https://forum.inductiveautomation.com/u/rbachman)\ **Post date:** [May 3, 2024, 2:00pm UTC](https://forum.inductiveautomation.com/t/slow-query-in-ignition/55908/7 "2024-05-03T14:00:01Z")
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. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/0/20314a1cce0a7095acab6d60fdbb7a703cd4e788.png) But when I run the same query as a named query from the same computer, the query takes over 5 seconds to complete. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/2/02fee3266d11ebd27d890f23a97dd53dfda4add8.png) 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. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/9/e9e5221d05c9a2025b8e6719c8899a8d6eabe59f.png) ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [May 3, 2024, 2:11pm UTC](https://forum.inductiveautomation.com/t/slow-query-in-ignition/55908/8 "2024-05-03T14:11:35Z")
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?) ---
**Author:** ![rbachman](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/rbachman/32/43718_2.png) [@rbachman](https://forum.inductiveautomation.com/u/rbachman)\ **Post date:** [May 3, 2024, 2:28pm UTC](https://forum.inductiveautomation.com/t/slow-query-in-ignition/55908/9 "2024-05-03T14:28:47Z")
> [@pturmel](#): > > SSMS doesn't use JDBC, but a native connection to MS SQL Server. 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. ---
**Author:** ![rbachman](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/rbachman/32/43718_2.png) [@rbachman](https://forum.inductiveautomation.com/u/rbachman)\ **Post date:** [May 3, 2024, 2:51pm UTC](https://forum.inductiveautomation.com/t/slow-query-in-ignition/55908/10 "2024-05-03T14:51:30Z")
I updated the driver on a test computer and am getting the same results. I may just have to create a Stored Procedure. ---
**Author:** ![Steve\_Laubach](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steve_laubach/32/60900_2.png) [@Steve\_Laubach](https://forum.inductiveautomation.com/u/Steve_Laubach)\ **Post date:** [May 3, 2024, 3:33pm UTC](https://forum.inductiveautomation.com/t/slow-query-in-ignition/55908/11 "2024-05-03T15:33:05Z")
I haven't seen any of these performance issues and I typically use stored procedures so I would definitely try that. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [May 3, 2024, 3:36pm UTC](https://forum.inductiveautomation.com/t/slow-query-in-ignition/55908/12 "2024-05-03T15:36:48Z")
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? ---
**Author:** ![rbachman](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/rbachman/32/43718_2.png) [@rbachman](https://forum.inductiveautomation.com/u/rbachman)\ **Post date:** [May 3, 2024, 4:22pm UTC](https://forum.inductiveautomation.com/t/slow-query-in-ignition/55908/13 "2024-05-03T16:22:34Z")
> [@lrose](#): > > 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. ---
**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\ **Post date:** [May 3, 2024, 5:06pm UTC](https://forum.inductiveautomation.com/t/slow-query-in-ignition/55908/14 "2024-05-03T17:06:17Z")
> [@rbachman](#): > > 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. ---
**Author:** ![rbachman](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/rbachman/32/43718_2.png) [@rbachman](https://forum.inductiveautomation.com/u/rbachman)\ **Post date:** [May 3, 2024, 6:07pm UTC](https://forum.inductiveautomation.com/t/slow-query-in-ignition/55908/15 "2024-05-03T18:07:40Z")
> [@MMaynard](#): > > 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.