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
Solved

How to speed up this query?

Posted on 2009-05-18
7
225 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
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Clone table from one server.database to another server.database 24 36
SQL query and VBA 5 46
IIF in access query 19 25
Reformat SQL - so SSRS can read the columns 25 12
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

829 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