?
Solved

How to speed up this query?

Posted on 2009-05-18
7
Medium Priority
?
231 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 1000 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 1000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

801 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