# SQL slow queries **URL:** \ **Category:** Ignition\ **Tags:** database, ignition80, scripting, perspective\ **Created:** [June 14, 2022, 2:26am UTC](https://forum.inductiveautomation.com/t/sql-slow-queries/60725 "2022-06-14T02:26:11Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![Nikhil\_Pai](https://avatars.discourse-cdn.com/v4/letter/n/eada6e/32.png) [@Nikhil\_Pai](https://forum.inductiveautomation.com/u/Nikhil_Pai)\ **Post date:** [June 14, 2022, 2:26am UTC](https://forum.inductiveautomation.com/t/sql-slow-queries/60725/1 "2022-06-14T02:26:11Z")
Hello All, I have few named queries which run very fast sometimes and sometimes takes upto 2.5 mins to run the query. I read about the Slow queries and tried to troubleshoot it. Used the same query in the MS SQL server application and it runs exactly how I want it to be. Logger also shows ‘Slow Query’ So its kind of weird how it runs very fast sometimes and runs very slow sometimes. If anyone can shed some light on this topic, it would be appreciated. ---
**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:** [June 14, 2022, 2:33am UTC](https://forum.inductiveautomation.com/t/sql-slow-queries/60725/2 "2022-06-14T02:33:33Z")
We can’t even **hope** to help you without seeing the query. ---
**Author:** ![Nikhil\_Pai](https://avatars.discourse-cdn.com/v4/letter/n/eada6e/32.png) [@Nikhil\_Pai](https://forum.inductiveautomation.com/u/Nikhil_Pai)\ **Post date:** [June 14, 2022, 3:18am UTC](https://forum.inductiveautomation.com/t/sql-slow-queries/60725/3 "2022-06-14T03:18:53Z")
![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/6/56e12c9710e8a9d64ad838997565c1b1155e36e1.png) Sorry my bad I thought I had attached the image. ```auto SQL SELECT t2.Faults, t2.Counts --ROUND(CAST(SUM(t2.Counts) OVER(ORDER BY t2.Counts DESC) * 100 AS FLOAT)/CAST(SUM(t2.Counts) OVER() AS FLOAT), 2) as Percentage FROM( SELECT TOP 10 t1.Faults as Faults, COUNT(*) as Counts FROM( SELECT CASE WHEN displaypath LIKE '%jam%' THEN 'Jam' WHEN displaypath LIKE '%EStop%' THEN 'Estop' WHEN displaypath LIKE '%fault%' THEN 'fault' WHEN displaypath LIKE '%printer%' THEN 'Printer' WHEN displaypath LIKE '%Emergency Stop%' THEN 'Emergency Stop' WHEN displaypath LIKE '%Pull Cord%' THEN 'Pull Cord' WHEN displaypath LIKE '%Robot Faulted%' THEN 'Robot Faulted' WHEN displaypath LIKE '%Gate Lock%' THEN 'Gate Lock' WHEN displaypath LIKE '%Input Pallet%' THEN 'Input Pallet' WHEN displaypath LIKE '%Air Pressure%' THEN 'Air Pressure' WHEN displaypath LIKE '%Input Pallet%' THEN 'Input Pallet' WHEN displaypath LIKE '%Fence Circuit%' THEN 'Fence Circuit' ELSE 'none' END AS Faults FROM [Ignition_Data].[dbo].[alarm_events] WHERE eventtime > :start AND eventtime < :end AND displaypath LIKE '%'+:area+'%' ) as t1 WHERE Faults != 'none' GROUP BY t1.Faults ORDER BY Counts DESC ) as t2 ``` ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [June 14, 2022, 12:13pm UTC](https://forum.inductiveautomation.com/t/sql-slow-queries/60725/5 "2022-06-14T12:13:46Z")
Or even better, use triple backquotes (`) and add the language like so: > ```SQL > code > ``` It will add syntax highlighting, which is always nice. Example: ```sql select t2.Faults, t2.Counts, round(cast(...)) from your_table where Faults != 'none' ``` ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [June 14, 2022, 12:25pm UTC](https://forum.inductiveautomation.com/t/sql-slow-queries/60725/6 "2022-06-14T12:25:44Z")
In addition, can you throw this query into the Workbench of your specific DB flavor, and run an EXPLAIN query on it? We don’t know what indexes or table relationships you already have or not. The EXPLAIN statement of a query will indicate where it slows down, if it has to do full table scans, etc. Though I do see you are querying directly from the alarm\_events table and you’re using ` displayPath LIKE '%area%'` in the WHERE clause and these are usually performance killers. Not to mention all the other `LIKE '%stuff%'` in your CASE select statement though I’d think this is less problematic than the where clause one. ---
**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:** [June 14, 2022, 3:42pm UTC](https://forum.inductiveautomation.com/t/sql-slow-queries/60725/7 "2022-06-14T15:42:12Z")
Do you have an index on just `eventtime`? If not, add one. ---
**Author:** ![Nikhil\_Pai](https://avatars.discourse-cdn.com/v4/letter/n/eada6e/32.png) [@Nikhil\_Pai](https://forum.inductiveautomation.com/u/Nikhil_Pai)\ **Post date:** [June 14, 2022, 10:16pm UTC](https://forum.inductiveautomation.com/t/sql-slow-queries/60725/8 "2022-06-14T22:16:24Z")
I do have an index for the `eventtime`. Here is the snapshot from the application. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/6/567ef52cbc1037f7040ccda7a00554567f7b8003.png) and this what logs show ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/3/832d4fa7f579dea7a2789dc31c0c6a97e7dc2a3a.png) ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [June 14, 2022, 10:27pm UTC](https://forum.inductiveautomation.com/t/sql-slow-queries/60725/9 "2022-06-14T22:27:07Z")
So you are saying it will **always** run quick in a query in management studio? You aren’t limiting the results in studio? Doesn’t make much sense that it would run differently, unless you aren’t running the same query in both. ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [June 15, 2022, 12:28pm UTC](https://forum.inductiveautomation.com/t/sql-slow-queries/60725/10 "2022-06-15T12:28:36Z")
Please show the `EXPLAIN` statement for the query. ---
**Author:** ![Nikhil\_Pai](https://avatars.discourse-cdn.com/v4/letter/n/eada6e/32.png) [@Nikhil\_Pai](https://forum.inductiveautomation.com/u/Nikhil_Pai)\ **Post date:** [June 17, 2022, 2:25pm UTC](https://forum.inductiveautomation.com/t/sql-slow-queries/60725/11 "2022-06-17T14:25:44Z")
Exact same query was run on the management studio and on ignition. The problem though was sometimes it would run fast and sometimes it would be very slow. FYI the named query was then bounded to the table’s data source. ---
**Author:** ![Nikhil\_Pai](https://avatars.discourse-cdn.com/v4/letter/n/eada6e/32.png) [@Nikhil\_Pai](https://forum.inductiveautomation.com/u/Nikhil_Pai)\ **Post date:** [June 17, 2022, 2:29pm UTC](https://forum.inductiveautomation.com/t/sql-slow-queries/60725/12 "2022-06-17T14:29:43Z")
So I wrote the same query, but this time using the script transform (system.db.runQuery) and now it works really well. But still beats me why was it slow using the Query Binding. ---
**Author:** ![louagejasper](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/louagejasper/32/86311_2.png) [@louagejasper](https://forum.inductiveautomation.com/u/louagejasper)\ **Post date:** [November 1, 2022, 8:44pm UTC](https://forum.inductiveautomation.com/t/sql-slow-queries/60725/13 "2022-11-01T20:44:40Z")
I've had the same issue with a Named Query, a simple query that runs fast in management studio, but really slow as a Named Query. I'm also not sure what could trigger this, but I was calling not all available parameters in my named query. for example: I called: ```auto params = { "from_date": date1, "to_date": date2, "param1": value1 } ``` While my Named query also accepted param2. I don't know why it behaved in this way, but by passing all params is how I worked around it. ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [November 2, 2022, 12:24pm UTC](https://forum.inductiveautomation.com/t/sql-slow-queries/60725/14 "2022-11-02T12:24:52Z")
If you don't pass a param then the param value defaults to NULL which depending on your query structure could make it far less efficient or take much longer.