asked on

How to speed up this query?

Hi

I have the following query but I'd like it sped up if possible...it is running very slow. There are 150000 rows to update

Thanks in advance for help!

UPDATE [HistorianData] 
SET [CLRTTI681261] = ( SELECT TOP 1 t2.[CLRTTI681261]
                 FROM [CLRTTI681261] t2 
                 WHERE t2.[TIMESTAMP] >= DATEADD(minute, -1, [HistorianData].[TIMESTAMP] )
                   AND t2.[TIMESTAMP] <= DATEADD(minute, 1, [HistorianData].[TIMESTAMP] )
                )

Open in new window

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

momi_sabag

try

with a as
(
select row_number() over(order by (select 1)) as rown, t2.CLRTTI681261
from [HistorianData] t1
inner join [CLRTTI681261] t2
on t2.[TIMESTAMP] >= DATEADD(minute, -1, [HistorianData].[TIMESTAMP] )
AND t2.[TIMESTAMP] <= DATEADD(minute, 1, [HistorianData].[TIMESTAMP] )
)

UPDATE a
SET [CLRTTI681261] = a.[CLRTTI681261]
WHERE rown= 1

SOLUTION

RiteshShah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

peter57r

Using Max() occurred to me as well, but there is no Order By in the TopN subquery, so I assumed that the poster wanted the first record (if any) in the specified period.

rwallacej

ASKER

hi,
my question follows on from https://www.experts-exchange.com/questions/24411248/SQL-update-matching-first-record-with-closest-time.html
whihc shows the logic, I have tried the
UPDATE [HistorianData]
SET [CLRTTI681261] = ( SELECT max(t2.[CLRTTI681261]) as [CLRTTI681261]
FROM [CLRTTI681261] t2
WHERE t2.[TIMESTAMP] >= DATEADD(minute, -1, [HistorianData].[TIMESTAMP] )
AND t2.[TIMESTAMP] <= DATEADD(minute, 1, [HistorianData].[TIMESTAMP] )
)
query and it runs in 17 secs (although I haven't checked the values!) opposed to over an hour

peter57r

So based on the previous Q you are saying explicitly that you want the first record value and not the max record value. Please confirm this.

But if so, then neither Max nor Min is of any use to you because the data values do not consistently increase or decrease within an interval.

rwallacej

ASKER

I want the first record value, so when the values are like this

1 15/10/08 00:00:00 NULL
2 15/10/08 00:01:00 NULL
3 15/10/08 00:02:00 NULL

I want record 1 value

In the data dump I have the TIMESTAMP is all in order from min to max - the data in table [CLRTTI681261] is in order from start date 15/10/08 to 15/10/09

hope this assists