Optimize update that uses subquerys with aggregates

Question

Post reply

Optimize update that uses subquerys with aggregates

francescvalenzuela

Grasshopper

Points: 23
More actions
March 28, 2008 at 3:26 am

#184656

Hello All,
I would like to optimize a TSQL update using SS2005 new capabilities. Here is the script:
use [InsertHereYourTestDBName]
go
CREATE TABLE [dbo].[MyTable](
[Date] [datetime] NOT NULL,
[Value1] [int] NULL,
[Value2] [int] NULL,
[SumValue1] [int] NULL,
[SumValue2] [int] NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
go
insert [dbo].[MyTable] ([Date], [Value1], [Value2])
values ('01/01/2008 0:00:00', 1, 2)
insert [dbo].[MyTable] ([Date], [Value1], [Value2])
values ('02/01/2008 0:00:00', 2, 4)
insert [dbo].[MyTable] ([Date], [Value1], [Value2])
values ('03/01/2008 0:00:00', 3, 6)
insert [dbo].[MyTable] ([Date], [Value1], [Value2])
values ('04/01/2008 0:00:00', 4, 8)
insert [dbo].[MyTable] ([Date], [Value1], [Value2])
values ('05/01/2008 0:00:00', 5, 10)
insert [dbo].[MyTable] ([Date], [Value1], [Value2])
values ('06/01/2008 0:00:00', 6, 12)
update MyTable
set MyTable.SumValue1=(select sum(Value1) from MyTable SumTable where SumTable.Date <= MyTable.Date)
,MyTable.SumValue2=(select sum(Value2) from MyTable SumTable where SumTable.Date <= MyTable.Date)
where MyTable.Date < '2008-01-05T00:00:00'
select * from MyTable
Thank you for your assistance.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

It's not new 2005 features, but the Update ... FROM will probably help here.

update MyTable

set MyTable.SumValue1= SumOfValue1,

MyTable.SumValue2= SumOfValue2

FROM

(SELECT Sum(Value1) AS SumOfValue1, SUM(Value2) AS SumOfValue2

FROM MyTable SumTable

WHERE SumTable.Date <= MyTable.Date AND MyTable.Date < '2008/01/05') Sub

Are you doing running total here? If so, look up the running totals article[/url] by Jeff Modan.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

francescvalenzuela Grasshopper Points: 23 More actions · Answer 2

Hi,

I've tried to execute this query but it doesn't runs. It returns this error:

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "MyTable.Date" could not be bound.

Any idea?

francescvalenzuela Grasshopper Points: 23 More actions · Answer 3

Well...

after reading the article I'm doing this:

update MyTable

set MyTable.SumValue1=RTC.RunningTotal1

,MyTable.SumValue2=RTC.RunningTotal2

from

(select

Date

,RunningTotal1

,RunningTotal2

from MyTable T1

CROSS APPLY

(select

RunningTotal1=sum(Value1)

,RunningTotal2=sum(Value2)

from MyTable T2

where T2.Date <= T1.Date

) as RT

) as RTC

where MyTable.Date=RTC.Date

I think that there is no performance gain respect the previous sentence when I look Execution Plan.

update MyTable

set MyTable.SumValue1=(select sum(Value1) from MyTable SumTable where SumTable.Date <= MyTable.Date)

,MyTable.SumValue2=(select sum(Value2) from MyTable SumTable where SumTable.Date <= MyTable.Date)

So...

Suggestions / Opinions / Ideas?