?
Solved

How to speed up this query?

Posted on 2009-05-18
7
Medium Priority
?
243 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 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

578 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