Solved

How to speed up this query?

Posted on 2009-05-18
7
229 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:rwallacej
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 250 total points
ID: 24409988
Can't see how you can avoid running the subquery for every record in your table.

I assume you have tried it without the AND......<=.... clause and it's no better or produces the wrong answer.

My only other thought is to add the + and - 1 minutes times to the CLRTTI681261 table explicitly so that a direct comparison can be made without the use of the Dateadd function.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24410001
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
0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 250 total points
ID: 24410052
momi_sabag,

I guess your query will not boost up performance rather it will slow it up. More Author can update us, he can compare his and your query with query plan and check for performance.


rwallacej,

you can try this one and compare performance.




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] )
                )

Open in new window

0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 77

Expert Comment

by:peter57r
ID: 24410643
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.
0
 

Author Comment

by:rwallacej
ID: 24410721
hi,
my question follows on from http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_24411248.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
0
 
LVL 77

Expert Comment

by:peter57r
ID: 24410974
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.
0
 

Author Comment

by:rwallacej
ID: 24412989
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
0

Featured Post

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question