Given your sample data, your view t_pivot
has the following EXPLAIN
plan:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|--------|--------------|----------------|------|---------------|--------|---------|--------|--------|-----------------|
| 1 | PRIMARY | t | ALL | (null) | (null) | (null) | (null) | 44 | (null) |
| 2 | UNION | t | ALL | (null) | (null) | (null) | (null) | 44 | (null) |
| 3 | UNION | t | ALL | (null) | (null) | (null) | (null) | 44 | (null) |
| 4 | UNION | t | ALL | (null) | (null) | (null) | (null) | 44 | (null) |
| (null) | UNION RESULT | <union1,2,3,4> | ALL | (null) | (null) | (null) | (null) | (null) | Using temporary |
It's doing a full-table scan four times just to create the view.
Create the following indexes on your table t
:
CREATE INDEX idx_t_ws_ts_engoil ON t(wellsite, ts, EngOilP_sd);
CREATE INDEX idx_t_ws_ts_compoil ON t(wellsite, ts, CompOilLVL_sd);
CREATE INDEX idx_t_ws_ts_brake ON t(wellsite, ts, Brake_sd);
CREATE INDEX idx_t_ws_ts_hitemp ON t(wellsite, ts, HiTemp_sd);
Then you should get an explain
like this:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|--------|--------------|----------------|-------|---------------|---------------------|---------|--------|--------|-----------------|
| 1 | PRIMARY | t | index | (null) | idx_t_ws_ts_engoil | 70 | (null) | 44 | Using index |
| 2 | UNION | t | index | (null) | idx_t_ws_ts_compoil | 70 | (null) | 44 | Using index |
| 3 | UNION | t | index | (null) | idx_t_ws_ts_brake | 70 | (null) | 44 | Using index |
| 4 | UNION | t | index | (null) | idx_t_ws_ts_hitemp | 70 | (null) | 44 | Using index |
| (null) | UNION RESULT | <union1,2,3,4> | ALL | (null) | (null) | (null) | (null) | (null) | Using temporary |
Of course it still reads the same amount of rows, but it reads them from the indexes. The indexes are put into memory (if they fit), not the whole table (unless it fits in the query cache (I wouldn't recommend to increase the query cache to an absurd large value, just to make this query run faster. Especially since you don't run this query very often. It will hurt you in the long run...)).
Now you have to look at this query:
SELECT sd, wellsite, TIMEDIFF(MAX(ts), MIN(ts)) AS duration, MIN(ts) AS starttime, MAX(ts) AS endtime
FROM (
SELECT
tp.*
, @val_change := IF(@prev_val != tp.val, @val_change + 1, @val_change) AS vc
, @prev_val := tp.val
FROM
t_pivot tp
, (SELECT @prev_val := NULL, @val_change := 0) var_init_subquery
ORDER BY wellsite, sd, ts
) sq
WHERE val = 1
GROUP BY sd, vc
sqlfiddle.com stopped working right now somehow, so I can't show you the explain
output, but I've seen it before and I'm not sure, if there's much more you can do about it. But there's an Using filesort
which can be avoided. Add an ORDER BY NULL
after GROUP BY sd, vc
. The GROUP BY
does an implicit ORDER BY
which is not really needed here, but I guess this won't help much. See if the above mentioned indexes help already.
You mentioned that you're using SQL Server 2016, which means you can use Live Query Plans.
In SSMS, click Query, Include Live Query Statistics, and run your query. You'll be able to see exactly which part of the query it's working on.
Keep in mind that it's possible the query is only compiling, not actually executing. With really ugly queries - and the one you've posted just might qualify - you can see compilations that take hours.
Best Answer
The answer by kevinsky is one path towards a solution for you.
A different path for your specific issue is to create a second reporting (and maybe a third dev) server(s). With copies of the production data. Your reporting people and development people can do all kinds of stupid, and not impact production.
There are multiple ways of achieving this, depending on how fresh your report/dev data needs to be.
A common solution is a nightly backup and restore to the secondary server. If you need really current data (like no lag or one second) look into SQL Server Always On