Is there any way to rephrase select statement so query will run faster

Question

I make use of a fairly advanced query with lots of calculated data based on a view which has calculated data itself. Creating this query takes a lot of time, which is a problem since the query is used to populate data models for Microsoft powerBI. Everytime I refresh my dashboard, my selected queries refresh, and this one in particular takes A LOT of time in order to refresh.

Is there any way to rewrite the select statement so that refreshing won't take 4-6 business days?

select top 10 x.model, count(x.model) as 'aantal gedraaid', 
(select avg(deltasec) from dbo.Testview1 where categorie = 'geen stop' and model = x.model) as 'gemiddelde tijd', 
(select count(model) from dbo.Testview1 where categorie in ('Lange stop', 'korte stop') and model = x.model ) as 'aantal stops',
((select sum(deltasec) from dbo.Testview1 where categorie not in ('setuptijd', 'uitschieter') and model = x.model )/3600) as 'total runtime',
((select sum(deltasec) from dbo.Testview1 where categorie in ('geen stop') and model = x.model )/3600) + (select ((cast((select count(model) from dbo.Testview1 where categorie in ('Lange stop', 'korte stop') and model = x.model) as real))* (select avg(deltasec) from dbo.Testview1 where categorie = 'geen stop' and model = x.model ))/3600)   'tijd geen stop',
((select sum(deltasec) from dbo.Testview1 where categorie in ('Lange stop', 'korte stop') and model = x.model )/3600) - (select ((cast((select count(model) from dbo.Testview1 where categorie in ('Lange stop', 'korte stop') and model = x.model) as real))* (select avg(deltasec) from dbo.Testview1 where categorie = 'geen stop' and model = x.model ))/3600) 'tijd stop',
((select sum(deltasec) from dbo.Testview1 where categorie in ('setuptijd') and model = x.model )/3600) as 'setuptijd',
(select min(deltasec) from dbo.testview1 where categorie = 'geen stop' and model = x.model) as 'beste tijd'
from dbo.Testview1 x 
where actie_id = 48 and model <> 'UFO'
group by model
order by max(x.datum) desc

EDIT a quick draft of the design of the testview1 view

48  nieuw PCB   2019-08-29  14:40:08.7700000    UFO 201908291440    00:00:05.8600000    5,86    uitschieter
48  nieuw PCB   2019-08-29  14:40:02.9100000    UFO 201908291440    00:00:32.4730000    32,47333    geen stop
48  nieuw PCB   2019-08-29  14:39:30.4370000    UFO 201908291439    00:01:31.0300000    91,03001    korte stop
48  nieuw PCB   2019-08-29  14:37:59.4070000    UFO 201908291438    02:00:01.0770000    7201,077    Lange stop
48  nieuw PCB   2019-08-29  12:37:58.3300000    UFO 201908291238    00:00:14.2070000    14,20667    geen stop
48  nieuw PCB   2019-08-29  12:37:44.1230000    UFO 201908291237    00:13:27.4100000    807,41  Lange stop
48  nieuw PCB   2019-08-29  12:24:16.7130000    UFO 201908291224    00:00:26.1670000    26,16667    geen stop
48  nieuw PCB   2019-08-29  12:23:50.5470000    UFO 201908291224    00:00:25.1270000    25,12667    geen stop
48  nieuw PCB   2019-08-29  12:23:25.4200000    UFO 201908291223    00:00:26.4100000    26,41   geen stop
48  nieuw PCB   2019-08-29  12:22:59.0100000    UFO 201908291223    00:00:25.5400000    25,54   geen stop
48  nieuw PCB   2019-08-29  12:22:33.4700000    UFO 201908291222    00:00:25.0600000    25,06   geen stop
48  nieuw PCB   2019-08-29  12:22:08.4100000    UFO 201908291222    00:00:24.7230000    24,72334    geen stop
48  nieuw PCB   2019-08-29  12:21:43.6870000    UFO 201908291221    00:01:24.6670000    84,66667    korte stop

Query to select this view

select x.*, 
case
    when datum <> lead(datum) over (order by datum desc, tijd desc) then 'uitschieter'
    when lead(actie_id) over (order by datum desc, tijd desc) = 36 and (lead(custom1) over (order by datum desc, tijd desc) like 'Start%' or lead(custom1) over (order by datum desc, tijd desc) like 'begin%') then 'setuptijd'
    when lead(actie_id,2) over (order by datum desc, tijd desc) = 36 and (lead(custom1,2) over (order by datum desc, tijd desc) like 'Start%' or lead(custom1,2) over (order by datum desc, tijd desc) like 'begin%') then 'setuptijd'
    when lead(actie_id,3) over (order by datum desc, tijd desc) = 36 and (lead(custom1,3) over (order by datum desc, tijd desc) like 'Start%' or lead(custom1,3) over (order by datum desc, tijd desc) like 'begin%') then 'setuptijd'
    when lead(actie_id,4) over (order by datum desc, tijd desc) = 36 and (lead(custom1,4) over (order by datum desc, tijd desc) like 'Start%' or lead(custom1,4) over (order by datum desc, tijd desc) like 'begin%') then 'setuptijd'
    when lead(actie_id,5) over (order by datum desc, tijd desc) = 36 and (lead(custom1,5) over (order by datum desc, tijd desc) like 'Start%' or lead(custom1,5) over (order by datum desc, tijd desc) like 'begin%') then 'setuptijd'
    when actie_id = 36 then 'uitschieter'
    When deltasec > 200 then 'Lange stop'
    When deltasec <200 and deltasec >60 then 'korte stop'
    When deltasec < 60 and deltasec > 10 then 'geen stop'
    else 'uitschieter'
    end as [Categorie]
from 
(select T_event.Actie_ID, 
case 
    when actie_omschrijving = 'foto' then 'nieuw PCB'
    when actie_omschrijving = 'is leeg' then 'start/stop model'
end as omschrijving,
cast(event_timestamp as date) as datum, cast(event_timestamp as time) as tijd, 
case 
    when left(custom1,4) like '201_%' then 'UFO'
    when t_event.Actie_ID = 48 then left(custom1,4)
    when t_event.Actie_ID = 36 then 'start/stop'
end as model,
custom1, 
cast(T_event.event_timestamp - Lag(T_event.event_timestamp) over (order by T_event.Event_ID) AS time) [Delta],
cast(T_event.event_timestamp - Lag(T_event.event_timestamp) over (order by T_event.Event_ID) AS real) * 86400 as deltasec
from T_event
  inner join T_Actie ON T_event.Actie_ID = T_Actie.Actie_ID
WHERE T_EVENT.Actie_ID in (36,34,48) and t_event.custom1 not like 'einde%'
  and Event_Timestamp >= '2019-07-22') x

original T_Event dataset

Additional info: This is data represents a manufacturing plant. An event with corresponding timestamp is logged whenever a product enters the final phase of manufacturing. My employer wants me to analyse and visually present this data in a dashboard

83  28  2   2012-05-08 08:31:06.843 038200000168    NULL    NULL    NULL    NULL
84  28  2   2012-05-08 08:31:08.063 038200000170    NULL    NULL    NULL    NULL
85  28  2   2012-05-08 08:51:27.437 038200000164    NULL    NULL    NULL    NULL
86  28  2   2012-05-08 08:51:29.250 038200000166    NULL    NULL    NULL    NULL
87  28  2   2012-05-08 08:52:58.530 038200000160    NULL    NULL    NULL    NULL
88  28  2   2012-05-08 08:52:59.953 038200000162    NULL    NULL    NULL    NULL
89  28  2   2012-05-08 08:53:42.483 038200000152    NULL    NULL    NULL    NULL
90  28  2   2012-05-08 08:53:43.703 038200000154    NULL    NULL    NULL    NULL
91  28  2   2012-05-08 08:54:24.280 038200000156    NULL    NULL    NULL    NULL
92  28  2   2012-05-08 08:54:25.750 038200000158    NULL    NULL    NULL    NULL
93  28  2   2012-05-08 09:02:54.720 038200000148    NULL    NULL    NULL    NULL

It's not as easy as you think. We'd need to know what base tables are actually behind that 'Testview1'. Blindly trying to rewrite your query has low chances of getting to a good solution. For more helpful information, please share your execution plan. For that, please use [PasteThePlan](https://www.brentozar.com/pastetheplan/) — Radu Gheorghiu, Aug 29 '19 at 13:32
are all those subqueries really necessary or could you use conditional aggregation? — S3S, Aug 29 '19 at 13:34
How many rows are there when you query your `Testview1` view? If you could push the results from the view into a permanent table (`SELECT * INTO dbo.materialised_Testview1 FROM dbo.Testview1`), then add indexes, then run your query, then drop the table, then it would possibly be a lot faster. But you might not be able to do this for a number of reasons I guess? — Richard Hansell, Aug 29 '19 at 13:38
@RaduGheorghiu Sadly, i don't have permission to generate the XML plan. — Anthony Lemaire, Aug 29 '19 at 13:43
Have you considered adding this select statement to a [Table Function](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-2017)? — Attie Wagner, Aug 29 '19 at 14:12
explore using different aggregate conditions https://stackoverflow.com/questions/2654750/multiple-aggregate-functions-in-one-sql-query-from-the-same-table-using-differen/ — Jeremy, Aug 29 '19 at 14:17
@birel Sadly, the administrator has not given me permission to edit or create tables. I've been given permission to edit 3 views, being testview1, testview2, testview3. Additional info This is data represents a manufacturing plant. A timestamp is logged whenever a product enters the final phase of manufacturing. My employer wants me to analyse and visually present this data in a dashboard. — Anthony Lemaire, Aug 29 '19 at 14:18
Understood @AnthonyLemaire, but remember, a Table Function, is not a physical table, it just returns the data in your select statement as a table - similar to a view, but quicker... — Attie Wagner, Aug 29 '19 at 14:25

Attie Wagner · Answer 1 · 2019-08-30T08:15:04.137

So my suggestion is the following:

if exists(select 1 from sysobjects where name = 'fn_SelectQuery_AL')
drop function fn_SelectQuery_AL;

set ansi_nulls on
go

set quoted_identifier on
go

create function fn_SelectQuery_AL()
returns table
with encryption
as
return
(
select x.*, 
case
    when datum <> lead(datum) over (order by datum desc, tijd desc) then 'uitschieter'
    when lead(actie_id) over (order by datum desc, tijd desc) = 36 and (lead(custom1) over (order by datum desc, tijd desc) like 'Start%' or lead(custom1) over (order by datum desc, tijd desc) like 'begin%') then 'setuptijd'
    when lead(actie_id,2) over (order by datum desc, tijd desc) = 36 and (lead(custom1,2) over (order by datum desc, tijd desc) like 'Start%' or lead(custom1,2) over (order by datum desc, tijd desc) like 'begin%') then 'setuptijd'
    when lead(actie_id,3) over (order by datum desc, tijd desc) = 36 and (lead(custom1,3) over (order by datum desc, tijd desc) like 'Start%' or lead(custom1,3) over (order by datum desc, tijd desc) like 'begin%') then 'setuptijd'
    when lead(actie_id,4) over (order by datum desc, tijd desc) = 36 and (lead(custom1,4) over (order by datum desc, tijd desc) like 'Start%' or lead(custom1,4) over (order by datum desc, tijd desc) like 'begin%') then 'setuptijd'
    when lead(actie_id,5) over (order by datum desc, tijd desc) = 36 and (lead(custom1,5) over (order by datum desc, tijd desc) like 'Start%' or lead(custom1,5) over (order by datum desc, tijd desc) like 'begin%') then 'setuptijd'
    when actie_id = 36 then 'uitschieter'
    When deltasec > 200 then 'Lange stop'
    When deltasec <200 and deltasec >60 then 'korte stop'
    When deltasec < 60 and deltasec > 10 then 'geen stop'
    else 'uitschieter'
    end as [Categorie]
from 
(select T_event.Actie_ID, 
case 
    when actie_omschrijving = 'foto' then 'nieuw PCB'
    when actie_omschrijving = 'is leeg' then 'start/stop model'
end as omschrijving,
cast(event_timestamp as date) as datum, cast(event_timestamp as time) as tijd, 
case 
    when left(custom1,4) like '201_%' then 'UFO'
    when t_event.Actie_ID = 48 then left(custom1,4)
    when t_event.Actie_ID = 36 then 'start/stop'
end as model,
custom1, 
cast(T_event.event_timestamp - Lag(T_event.event_timestamp) over (order by T_event.Event_ID) AS time) [Delta],
cast(T_event.event_timestamp - Lag(T_event.event_timestamp) over (order by T_event.Event_ID) AS real) * 86400 as deltasec
from T_event
  inner join T_Actie ON T_event.Actie_ID = T_Actie.Actie_ID
WHERE T_EVENT.Actie_ID in (36,34,48) and t_event.custom1 not like 'einde%'
  and Event_Timestamp >= '2019-07-22') x
)

I think this will suffice?

You can then just run the following:

select
*
from    dbo.fn_SelectQuery_AL()

Let me know if it works.

When i execute the first string i get the following error code: Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'fn_SelectQuery_AL'. — Anthony Lemaire, Aug 30 '19 at 07:13
I added an else statement after the initial conditional statement. Seems like i don't have the authorisation to create functions either... I'll have a talk with the server admin because my restricted authorisation is really limiting my capacity atm — Anthony Lemaire, Aug 30 '19 at 08:05
@AnthonyLemaire, apologies, I forgot to add `dbo` in front of the function... Check if it works now? — Attie Wagner, Aug 30 '19 at 08:15
It fixes the initial error, still, i have no permission to create functions. As soon as it gets granted i will let you know. — Anthony Lemaire, Aug 30 '19 at 08:19

Is there any way to rephrase select statement so query will run faster

1 Answers1