SQL slow queries

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.

We can’t even hope to help you without seeing the query.

1 Like


Sorry my bad I thought I had attached the image.

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

Or even better, use triple backquotes (`) and add the language like so:

```SQL
code
```

It will add syntax highlighting, which is always nice. Example:

select t2.Faults, t2.Counts, round(cast(...)) from your_table where Faults != 'none'

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.

Do you have an index on just eventtime? If not, add one.

2 Likes

I do have an index for the eventtime. Here is the snapshot from the application.

and this what logs show

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.

Please show the EXPLAIN statement for the query.

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.

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.