en-USsv-SE

Active Forums

PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 18 Oct 2010 03:29 PM by  JAhlen
SQLug.se challenge 2010
 65 Replies
Sort:
You are not authorized to post a reply.
Page 2 of 4 << < 1234 > >>
Author Messages
JAhlen
Veteran Member
Veteran Member
Posts: 144


--
09 Nov 2010 09:57 AM
Preliminär ställning:

***************************************************************
* Namn * Företag * Tid *
***************************************************************
* Ola Hallengren * * 2,15 s *
* Seregak76 * * 2,23 s *
***************************************************************

Skriv gärna både ert namn och företag på era tävlingsbidrag.

MVH
Johan
Sergey
New Member
New Member
Posts: 16


--
09 Nov 2010 11:51 AM
Parallelism verkar kosta för mycket upptäckte jag om man kör på en maskin med flera CPU, därför lägger jag upp en ny variant:

/*
Author: Sergey Klimkevich
Company: Memnon Networks AB
*/
-- SETUP
CREATE UNIQUE NONCLUSTERED INDEX [ix_name] ON [dbo].[MeterReadings]
(
[VehicleName] ASC,
[StartTime] ASC
)
INCLUDE ( [MeterValue]) WITH (PAD_INDEX = OFF, FILLFACTOR = 100) ON [PRIMARY]
GO
truncate table dbo.[ConsumptionSummary]
GO


-- MAIN RUN

;with [Ordered List] as (
SELECT
row_number() over(partition by [VehicleName] order by starttime) rn
,[StartTime]
,[VehicleName]
,[MeterValue]
FROM [dbo].[MeterReadings]

), [Consumption Stats] as (
select
t1.[VehicleName]
,t1.[StartTime]
,t2.[MeterValue] - t1.[MeterValue] m
,datediff(ms,t1.[StartTime],t2.[StartTime]) ms
from [Ordered List] t1
join [Ordered List] t2
on t1.[VehicleName]=t2.[VehicleName]
and t1.rn + 1 = t2.rn

), [20% Slices] as (
select NTILE(5) over(
partition by [VehicleName]
order by (convert(float,m)/ms), [StartTime] -- consumption/ms
) as rn
,[VehicleName],m,ms
from [Consumption Stats]
)

INSERT INTO [dbo].[ConsumptionSummary]
([VehicleName]
,[Top20PctAveragePerMinute])
output inserted.*
select [VehicleName]
,(convert(float,(sum(m)))/sum(ms)) * 60000
from [20% Slices]
where rn = 5 -- last 20%
group by [VehicleName]
OPTION(MAXDOP 1)
;
GO

-- CLEAN UP
drop index ix_name ON [dbo].[MeterReadings]
GO
truncate table [ConsumptionSummary]
GO
Magnus Sörin
New Member
New Member
Posts: 3


--
09 Nov 2010 02:33 PM

Här är ett förslag, jag kan representera Avanade antar jag:

-- Skapar index, truncar tabell

truncate table dbo.ConsumptionSummary
create unique nonclustered index [IX_MeterReadings_VehicleName_StartTime] on [dbo].[MeterReadings]
(
[VehicleName] asc,
[StartTime] asc
)
include ( [MeterValue])
create nonclustered index [IX_MeterReadings_VehicleName] on [dbo].[MeterReadings]
(
[VehicleName] asc
)

-- Frågan

;with VehicleRowCount as
(
select
VehicleName,
count(1) - 1 as VehicleRowCount
from
dbo.MeterReadings
group by
VehicleName
),
Segment as
(
select
VehicleName,
StartTime,
MeterValue,
row_number() over (order by VehicleName,StartTime) as SegmentRowNumber
from
dbo.MeterReadings
),
SegmentConsumption as
(
select
SS.VehicleName,
convert(float,SE.MeterValue - SS.MeterValue) as Consumption,
convert(float,datediff(ms,SS.StartTime,SE.StartTime)) as Msecs,
row_number() over
(
partition by SS.VehicleName
order by
convert(float,(SE.MeterValue - SS.MeterValue)) /
convert(float,DATEDIFF(ms,SS.StartTime,SE.StartTime)) desc, SS.StartTime desc
) as ConsRowOrder
from
Segment SS
join Segment SE on
SS.SegmentRowNumber = SE.SegmentRowNumber - 1 and
SS.VehicleName = SE.VehicleName
)
insert into
dbo.ConsumptionSummary
select
VehicleName,
sum(Consumption) /
sum(Msecs) * 60000
from
SegmentConsumption SC
where exists
(
select
1
from
VehicleRowCount VRC
where
(VRC.VehicleRowCount + 4) / 5 >= SC.ConsRowOrder and
VRC.VehicleName = SC.VehicleName
)
group by
SC.VehicleName

-- Droppar index

drop index [IX_MeterReadings_VehicleName_StartTime] on [dbo].[MeterReadings]
drop index [IX_MeterReadings_VehicleName] on [dbo].[MeterReadings]

Magnus Sörin
New Member
New Member
Posts: 3


--
09 Nov 2010 02:46 PM

Här är ett annat förslag, men jag skulle inte godkänna det om jag satt i en jury :)

// Magnus Sörin


-- Skapa index

declare @SqlCreateIndex nvarchar(max)

truncate table
ConsumptionSummary

select
@SqlCreateIndex =
(
select
distinct
'
create unique index [TMPIX__MeterReadings__' + VehicleName + '] on dbo.MeterReadings
(
StartTime asc
)
include
(
Metervalue
)
where VehicleName = ''' + VehicleName + ''';
create index [TMP2IX__MeterReadings__' + VehicleName + '] on dbo.MeterReadings
(
MeterReadingID asc
)
where VehicleName = ''' + VehicleName + ''';
'
as 'text()'
from
MeterReadings
for xml path(''), type
).value('.','nvarchar(max)')

exec sp_executesql @SqlCreateIndex

-- Frågan

declare
@CTE1Sql nvarchar(max),
@CTE2Sql nvarchar(max),
@CTE3Sql nvarchar(max),
@CTE4Sql nvarchar(max),
@Sql nvarchar(max)

select

@CTE1Sql =
stuff(
(
select
',
[CTE1_' + SUBSTRING(name,23,50) + '] as
(
select
count(1) - 1 as VehicleNoSegs
from
MeterReadings with (index ([TMP2IX__MeterReadings__' + SUBSTRING(name,23,50) + ']))
where
VehicleName = ''' + SUBSTRING(name,23,50) + '''
)'
from
sys.indexes
where
object_id = object_id('MeterReadings') and
name like 'TMPIX__MeterReadings__%'
for xml path(''), type
).value('.','nvarchar(max)'),
1,1,'with'
)
,
@CTE2Sql =
(
select
',
[CTE2_' + SUBSTRING(name,23,50) + '] as
(
select
StartTime,
MeterValue,
row_number() over (order by StartTime asc) as RowNum
from
MeterReadings with (index ([TMPIX__MeterReadings__' + SUBSTRING(name,23,50) + ']))
where
VehicleName = ''' + SUBSTRING(name,23,50) + '''
)'
from
sys.indexes
where
object_id = object_id('MeterReadings') and
name like 'TMPIX__MeterReadings__%'
for xml path(''), type
).value('.','nvarchar(max)')
,
@CTE3Sql =
(
select
',
[CTE3_' + SUBSTRING(name,23,50) + '] as
(
select
convert(float,SE.MeterValue - SS.MeterValue) as Consumption,
convert(float,datediff(ms,SS.StartTime,SE.StartTime)) as SegTimeInMSecs,
row_number() over (order by convert(float,SE.MeterValue - SS.MeterValue) / convert(float,datediff(ms,SS.StartTime,SE.StartTime)) desc, SE.StartTime desc) as RowNum
from
[CTE2_' + SUBSTRING(name,23,50) + '] SS with (index ([TMPIX__MeterReadings__' + SUBSTRING(name,23,50) + ']))
join [CTE2_' + SUBSTRING(name,23,50) + '] SE with (index ([TMPIX__MeterReadings__' + SUBSTRING(name,23,50) + '])) on
SE.RowNum - 1 = SS.RowNum
)'
from
sys.indexes
where
object_id = object_id('MeterReadings') and
name like 'TMPIX__MeterReadings__%'
for xml path(''), type
).value('.','nvarchar(max)'),
@Sql =
(
stuff(
(
select
'
union all
select
''' + SUBSTRING(name,23,50) + ''' as VehicleName,
sum(Consumption) / sum(SegTimeInMSecs) * 60000 AS Top20PercentConsumption
from
[CTE3_' + SUBSTRING(name,23,50) + '] C
where ((select VehicleNoSegs from [CTE1_' + SUBSTRING(name,23,50) + ']) + 4) / 5 >= C.RowNum
'
from
sys.indexes
where
object_id = object_id('MeterReadings') and
name like 'TMPIX__MeterReadings__%'
for xml path(''), type
).value('.','nvarchar(max)'),
1,11,'insert into
ConsumptionSummary')
)

select
@Sql = @CTE1Sql + @CTE2Sql + @CTE3Sql + @Sql

exec sp_executesql @Sql

-- Rensa

declare @sqldropindex nvarchar(max)

truncate table
ConsumptionSummary

select
@sqldropindex =
(
select
distinct
'
drop index [TMPIX__MeterReadings__' + VehicleName + '] on dbo.MeterReadings
drop index [TMP2IX__MeterReadings__' + VehicleName + '] on dbo.MeterReadings
'
as 'text()'
from
MeterReadings
for xml path(''), type
).value('.','nvarchar(max)')

exec sp_executesql @sqldropindex

JAhlen
Veteran Member
Veteran Member
Posts: 144


--
10 Nov 2010 08:17 AM
seregak76 skrev:
Parallelism verkar kosta för mycket upptäckte jag om man kör på en maskin med flera CPU, därför lägger jag upp en ny variant:

/*
Author: Sergey Klimkevich
Company: Memnon Networks AB
*/
-- SETUP
CREATE UNIQUE NONCLUSTERED INDEX [ix_name] ON [dbo].[MeterReadings]
(
[VehicleName] ASC,
[StartTime] ASC
)
INCLUDE ( [MeterValue]) WITH (PAD_INDEX = OFF, FILLFACTOR = 100) ON [PRIMARY]
GO
truncate table dbo.[ConsumptionSummary]
GO


-- MAIN RUN

;with [Ordered List] as (
SELECT
row_number() over(partition by [VehicleName] order by starttime) rn
,[StartTime]
,[VehicleName]
,[MeterValue]
FROM [dbo].[MeterReadings]

), [Consumption Stats] as (
select
t1.[VehicleName]
,t1.[StartTime]
,t2.[MeterValue] - t1.[MeterValue] m
,datediff(ms,t1.[StartTime],t2.[StartTime]) ms
from [Ordered List] t1
join [Ordered List] t2
on t1.[VehicleName]=t2.[VehicleName]
and t1.rn + 1 = t2.rn

), [20% Slices] as (
select NTILE(5) over(
partition by [VehicleName]
order by (convert(float,m)/ms), [StartTime] -- consumption/ms
) as rn
,[VehicleName],m,ms
from [Consumption Stats]
)

INSERT INTO [dbo].[ConsumptionSummary]
([VehicleName]
,[Top20PctAveragePerMinute])
output inserted.*
select [VehicleName]
,(convert(float,(sum(m)))/sum(ms)) * 60000
from [20% Slices]
where rn = 5 -- last 20%
group by [VehicleName]
OPTION(MAXDOP 1)
;
GO

-- CLEAN UP
drop index ix_name ON [dbo].[MeterReadings]
GO
truncate table [ConsumptionSummary]
GO

Ingen skillnad i prestanda mot innan...

Mvh
Johan

JAhlen
Veteran Member
Veteran Member
Posts: 144


--
10 Nov 2010 08:24 AM
magnuss skrev:

Här är ett förslag, jag kan representera Avanade antar jag:

-- Skapar index, truncar tabell

truncate table dbo.ConsumptionSummary
create unique nonclustered index [IX_MeterReadings_VehicleName_StartTime] on [dbo].[MeterReadings]
(
[VehicleName] asc,
[StartTime] asc
)
include ( [MeterValue])
create nonclustered index [IX_MeterReadings_VehicleName] on [dbo].[MeterReadings]
(
[VehicleName] asc
)

-- Frågan

;with VehicleRowCount as
(
select
VehicleName,
count(1) - 1 as VehicleRowCount
from
dbo.MeterReadings
group by
VehicleName
),
Segment as
(
select
VehicleName,
StartTime,
MeterValue,
row_number() over (order by VehicleName,StartTime) as SegmentRowNumber
from
dbo.MeterReadings
),
SegmentConsumption as
(
select
SS.VehicleName,
convert(float,SE.MeterValue - SS.MeterValue) as Consumption,
convert(float,datediff(ms,SS.StartTime,SE.StartTime)) as Msecs,
row_number() over
(
partition by SS.VehicleName
order by
convert(float,(SE.MeterValue - SS.MeterValue)) /
convert(float,DATEDIFF(ms,SS.StartTime,SE.StartTime)) desc, SS.StartTime desc
) as ConsRowOrder
from
Segment SS
join Segment SE on
SS.SegmentRowNumber = SE.SegmentRowNumber - 1 and
SS.VehicleName = SE.VehicleName
)
insert into
dbo.ConsumptionSummary
select
VehicleName,
sum(Consumption) /
sum(Msecs) * 60000
from
SegmentConsumption SC
where exists
(
select
1
from
VehicleRowCount VRC
where
(VRC.VehicleRowCount + 4) / 5 >= SC.ConsRowOrder and
VRC.VehicleName = SC.VehicleName
)
group by
SC.VehicleName

-- Droppar index

drop index [IX_MeterReadings_VehicleName_StartTime] on [dbo].[MeterReadings]
drop index [IX_MeterReadings_VehicleName] on [dbo].[MeterReadings]

Resultat för ditt tävlingsbidrag:

Utresultat GODKäNT
Exekveringstid 1,28 s
CPU-tid 3,40 s
Antal reads 3054

MVH
Johan

JAhlen
Veteran Member
Veteran Member
Posts: 144


--
10 Nov 2010 08:32 AM
magnuss skrev:

Här är ett annat förslag, men jag skulle inte godkänna det om jag satt i en jury :)

// Magnus Sörin


-- Skapa index

declare @SqlCreateIndex nvarchar(max)

truncate table
ConsumptionSummary

select
@SqlCreateIndex =
(
select
distinct
'
create unique index [TMPIX__MeterReadings__' + VehicleName + '] on dbo.MeterReadings
(
StartTime asc
)
include
(
Metervalue
)
where VehicleName = ''' + VehicleName + ''';
create index [TMP2IX__MeterReadings__' + VehicleName + '] on dbo.MeterReadings
(
MeterReadingID asc
)
where VehicleName = ''' + VehicleName + ''';
'
as 'text()'
from
MeterReadings
for xml path(''), type
).value('.','nvarchar(max)')

exec sp_executesql @SqlCreateIndex

-- Frågan

declare
@CTE1Sql nvarchar(max),
@CTE2Sql nvarchar(max),
@CTE3Sql nvarchar(max),
@CTE4Sql nvarchar(max),
@Sql nvarchar(max)

select

@CTE1Sql =
stuff(
(
select
',
[CTE1_' + SUBSTRING(name,23,50) + '] as
(
select
count(1) - 1 as VehicleNoSegs
from
MeterReadings with (index ([TMP2IX__MeterReadings__' + SUBSTRING(name,23,50) + ']))
where
VehicleName = ''' + SUBSTRING(name,23,50) + '''
)'
from
sys.indexes
where
object_id = object_id('MeterReadings') and
name like 'TMPIX__MeterReadings__%'
for xml path(''), type
).value('.','nvarchar(max)'),
1,1,'with'
)
,
@CTE2Sql =
(
select
',
[CTE2_' + SUBSTRING(name,23,50) + '] as
(
select
StartTime,
MeterValue,
row_number() over (order by StartTime asc) as RowNum
from
MeterReadings with (index ([TMPIX__MeterReadings__' + SUBSTRING(name,23,50) + ']))
where
VehicleName = ''' + SUBSTRING(name,23,50) + '''
)'
from
sys.indexes
where
object_id = object_id('MeterReadings') and
name like 'TMPIX__MeterReadings__%'
for xml path(''), type
).value('.','nvarchar(max)')
,
@CTE3Sql =
(
select
',
[CTE3_' + SUBSTRING(name,23,50) + '] as
(
select
convert(float,SE.MeterValue - SS.MeterValue) as Consumption,
convert(float,datediff(ms,SS.StartTime,SE.StartTime)) as SegTimeInMSecs,
row_number() over (order by convert(float,SE.MeterValue - SS.MeterValue) / convert(float,datediff(ms,SS.StartTime,SE.StartTime)) desc, SE.StartTime desc) as RowNum
from
[CTE2_' + SUBSTRING(name,23,50) + '] SS with (index ([TMPIX__MeterReadings__' + SUBSTRING(name,23,50) + ']))
join [CTE2_' + SUBSTRING(name,23,50) + '] SE with (index ([TMPIX__MeterReadings__' + SUBSTRING(name,23,50) + '])) on
SE.RowNum - 1 = SS.RowNum
)'
from
sys.indexes
where
object_id = object_id('MeterReadings') and
name like 'TMPIX__MeterReadings__%'
for xml path(''), type
).value('.','nvarchar(max)'),
@Sql =
(
stuff(
(
select
'
union all
select
''' + SUBSTRING(name,23,50) + ''' as VehicleName,
sum(Consumption) / sum(SegTimeInMSecs) * 60000 AS Top20PercentConsumption
from
[CTE3_' + SUBSTRING(name,23,50) + '] C
where ((select VehicleNoSegs from [CTE1_' + SUBSTRING(name,23,50) + ']) + 4) / 5 >= C.RowNum
'
from
sys.indexes
where
object_id = object_id('MeterReadings') and
name like 'TMPIX__MeterReadings__%'
for xml path(''), type
).value('.','nvarchar(max)'),
1,11,'insert into
ConsumptionSummary')
)

select
@Sql = @CTE1Sql + @CTE2Sql + @CTE3Sql + @Sql

exec sp_executesql @Sql

-- Rensa

declare @sqldropindex nvarchar(max)

truncate table
ConsumptionSummary

select
@sqldropindex =
(
select
distinct
'
drop index [TMPIX__MeterReadings__' + VehicleName + '] on dbo.MeterReadings
drop index [TMP2IX__MeterReadings__' + VehicleName + '] on dbo.MeterReadings
'
as 'text()'
from
MeterReadings
for xml path(''), type
).value('.','nvarchar(max)')

exec sp_executesql @sqldropindex

Kreativ idé, men vi får ta en funderare över om detta skall godkännas...!

Resultat för ditt tävlingsbidrag:

Utresultat GODKäNT (men metod är tveksam)
Exekveringstid 0,58 s
CPU-tid 1,36 s
Antal reads 1481

MVH
Johan

SwePeso
New Member
New Member
Posts: 67


--
10 Nov 2010 08:59 AM
Metoden är kreativ, och snabb, men frågan är hur man anpassar den till 50, eller 500, fordon?
Magnus Sörin
New Member
New Member
Posts: 3


--
10 Nov 2010 11:25 AM
Jag vet, den skalar inte. Max antal index är väl 999 icke-klustrade per tabell, så den är ju inte säker för fem öre.
Men rolig...
JAhlen
Veteran Member
Veteran Member
Posts: 144


--
10 Nov 2010 03:31 PM
Vi kommer att köra avgörande testerna på en större mängd indata, så vi får mer utslagsgivande siffror. Då kommer dessutom icke-skalbara lösningar att gallras ut...

MVH
Johan
JAhlen
Veteran Member
Veteran Member
Posts: 144


--
10 Nov 2010 03:36 PM
Preliminär ställning:

***************************************************************
* Namn * Företag * Tid *
***************************************************************
* Magnus Sörin * Avanade * 1,28 s *
* Ola Hallengren * * 2,15 s *
* Sergey Klimkevich * Memnon Networks * 2,23 s *
***************************************************************

Obs att de avgörande körningarna kommer att göras på en större mängd indata!

MVH
Johan
Ola Hallengren
New Member
New Member
Posts: 2


--
11 Nov 2010 12:31 AM
Hej,

Jag representerar Saxo Bank.

Jag tänkte på en sak. Kör ni DBCC DROPCLEANBUFFERS och mäter sedan tiden för den första exekveringen av frågan eller hur gör ni?

M.v.h.

Ola
JAhlen
Veteran Member
Veteran Member
Posts: 144


--
11 Nov 2010 10:39 AM
olahallengren skrev:
Hej,

Jag representerar Saxo Bank.

Jag tänkte på en sak. Kör ni DBCC DROPCLEANBUFFERS och mäter sedan tiden för den första exekveringen av frågan eller hur gör ni?

M.v.h.

Ola

Hej Ola,

Vi kör samma utvärdering som Phil Factor Speed Phreak Challenge. Där finns en beskrivning under rubriken "How is timing done?". Vi kör minst 20 iterationer och tar medianen. DBCC DROPCLEANBUFFERS körs endast före den första iterationen.

MVH
Johan

Sergey
New Member
New Member
Posts: 16


--
11 Nov 2010 11:15 PM
Ett nytt försök...

/***************************************
Author: Sergey Klimkevich
Company: Memnon Networks AB
Version: 3
****************************************/
-- SETUP
CREATE UNIQUE NONCLUSTERED INDEX [ix_name] ON [dbo].[MeterReadings]
(
[VehicleName] ASC,
[StartTime] ASC
)
INCLUDE ( [MeterValue]) WITH (PAD_INDEX = OFF, FILLFACTOR = 100) ON [PRIMARY]
GO
truncate table dbo.[ConsumptionSummary]
GO


-- MAIN RUN

;with [Ordered List] as (
SELECT
row_number() over(partition by [VehicleName] order by starttime) rn
,[StartTime]
,[VehicleName]
,[MeterValue]
FROM [dbo].[MeterReadings]

), [20% limits] as (
select
[VehicleName] vn
,(max(rn)-1)/5 mrn
from [Ordered List]
group by [VehicleName]

), [Consumption Stats] as (
select
t2.[MeterValue] - t1.[MeterValue] m
,datediff(ms,t1.[StartTime],t2.[StartTime]) ms
,CASE WHEN row_number() over(
partition by t1.[VehicleName]
order by convert(float,t2.[MeterValue] - t1.[MeterValue])/datediff(ms,t1.[StartTime],t2.[StartTime]) desc ,t2.[StartTime] desc-- consumption/ms
) > (SELECT mrn FROM [20% limits] WHERE t1.[VehicleName] = vn) THEN NULL
ELSE t1.[VehicleName]
END as [VehicleName]
from [Ordered List] t1
join [Ordered List] t2
on t1.[VehicleName]=t2.[VehicleName]
and t1.rn = t2.rn -1
)

INSERT INTO [dbo].[ConsumptionSummary]
([VehicleName],[Top20PctAveragePerMinute])
--output inserted.*

select [VehicleName]
,convert(float,sum(m))/sum(ms) * 60000
from [Consumption Stats] cs
WHERE VehicleName IS NOT null
group by [VehicleName]
GO

-- CLEAN UP
drop index ix_name ON [dbo].[MeterReadings]
GO
truncate table [ConsumptionSummary]
GO

Sergey
New Member
New Member
Posts: 16


--
12 Nov 2010 07:57 AM

Vill bara lägga till en liten korrektion till min senaste inlägg.

/***************************************
Author: Sergey Klimkevich
Company: Memnon Networks AB
Version: 4
****************************************/

-- SETUP
CREATE UNIQUE NONCLUSTERED INDEX [ix_name] ON [dbo].[MeterReadings]
(
[VehicleName] ASC,
[StartTime] ASC
)
INCLUDE ( [MeterValue]) WITH (PAD_INDEX = OFF, FILLFACTOR = 100) ON [PRIMARY]
GO
truncate table dbo.[ConsumptionSummary]
GO


-- MAIN RUN

;with [Ordered List] as (
SELECT
row_number() over(partition by [VehicleName] order by starttime) rn
,[StartTime]
,[VehicleName]
,[MeterValue]
FROM [dbo].[MeterReadings]

), [20% limits] as (
select
[VehicleName] vn
,CEILING( (max(rn)-1)*0.2 ) mrn
from [Ordered List]
group by [VehicleName]

), [Consumption Stats] as (
select
t2.[MeterValue] - t1.[MeterValue] m
,datediff(ms,t1.[StartTime],t2.[StartTime]) ms
,CASE WHEN row_number() over(
partition by t1.[VehicleName]
order by convert(float,t2.[MeterValue] - t1.[MeterValue])/datediff(ms,t1.[StartTime],t2.[StartTime]) desc ,t2.[StartTime] desc-- consumption/ms
) > (SELECT mrn FROM [20% limits] WHERE t1.[VehicleName] = vn) THEN NULL
ELSE t1.[VehicleName]
END as [VehicleName]
from [Ordered List] t1
join [Ordered List] t2
on t1.[VehicleName]=t2.[VehicleName]
and t1.rn = t2.rn -1
)

INSERT INTO [dbo].[ConsumptionSummary]
([VehicleName],[Top20PctAveragePerMinute])
--output inserted.*

select [VehicleName]
,convert(float,sum(m))/sum(ms) * 60000
from [Consumption Stats] cs
WHERE VehicleName IS NOT null
group by [VehicleName]
GO

-- CLEAN UP
drop index ix_name ON [dbo].[MeterReadings]
GO
truncate table [ConsumptionSummary]
GO
Sergey
New Member
New Member
Posts: 16


--
12 Nov 2010 08:43 AM
Om jag får ångra mig en gång till, hoppas nu för sista gången:


/***************************************
Author: Sergey Klimkevich
Company: Memnon Networks AB
Version: 5
****************************************/

-- SETUP
CREATE UNIQUE NONCLUSTERED INDEX [ix_name] ON [dbo].[MeterReadings]
(
[VehicleName] ASC,
[StartTime] ASC
)
INCLUDE ( [MeterValue]) WITH (PAD_INDEX = OFF, FILLFACTOR = 100) ON [PRIMARY]
GO
truncate table dbo.[ConsumptionSummary]
GO


-- MAIN RUN

;with [Ordered List] as (
SELECT
row_number() over(partition by [VehicleName] order by starttime) rn
,[StartTime]
,[VehicleName]
,[MeterValue]
FROM [dbo].[MeterReadings]

), [20% limits] as (
select
[VehicleName] vn
, (max(rn)+3)/5 mrn
from [Ordered List]
group by [VehicleName]

), [Consumption Stats] as (
select
t2.[MeterValue] - t1.[MeterValue] m
,datediff(ms,t1.[StartTime],t2.[StartTime]) ms
,CASE WHEN row_number() over(
partition by t1.[VehicleName]
order by convert(float,t2.[MeterValue] - t1.[MeterValue])/datediff(ms,t1.[StartTime],t2.[StartTime]) desc ,t2.[StartTime] desc-- consumption/ms
) > (SELECT mrn FROM [20% limits] WHERE t1.[VehicleName] = vn) THEN NULL
ELSE t1.[VehicleName]
END as [VehicleName]
from [Ordered List] t1
join [Ordered List] t2
on t1.[VehicleName]=t2.[VehicleName]
and t1.rn = t2.rn -1
)

INSERT INTO [dbo].[ConsumptionSummary]
([VehicleName],[Top20PctAveragePerMinute])
--output inserted.*

select [VehicleName]
,convert(float,sum(m))/sum(ms) * 60000
from [Consumption Stats] cs
WHERE VehicleName IS NOT null
group by [VehicleName]
GO

-- CLEAN UP
drop index ix_name ON [dbo].[MeterReadings]
GO
truncate table [ConsumptionSummary]
GO

JAhlen
Veteran Member
Veteran Member
Posts: 144


--
13 Nov 2010 02:28 PM
seregak76 skrev:
Om jag får ångra mig en gång till, hoppas nu för sista gången:


/***************************************
Author: Sergey Klimkevich
Company: Memnon Networks AB
Version: 5
****************************************/

-- SETUP
CREATE UNIQUE NONCLUSTERED INDEX [ix_name] ON [dbo].[MeterReadings]
(
[VehicleName] ASC,
[StartTime] ASC
)
INCLUDE ( [MeterValue]) WITH (PAD_INDEX = OFF, FILLFACTOR = 100) ON [PRIMARY]
GO
truncate table dbo.[ConsumptionSummary]
GO


-- MAIN RUN

;with [Ordered List] as (
SELECT
row_number() over(partition by [VehicleName] order by starttime) rn
,[StartTime]
,[VehicleName]
,[MeterValue]
FROM [dbo].[MeterReadings]

), [20% limits] as (
select
[VehicleName] vn
, (max(rn)+3)/5 mrn
from [Ordered List]
group by [VehicleName]

), [Consumption Stats] as (
select
t2.[MeterValue] - t1.[MeterValue] m
,datediff(ms,t1.[StartTime],t2.[StartTime]) ms
,CASE WHEN row_number() over(
partition by t1.[VehicleName]
order by convert(float,t2.[MeterValue] - t1.[MeterValue])/datediff(ms,t1.[StartTime],t2.[StartTime]) desc ,t2.[StartTime] desc-- consumption/ms
) > (SELECT mrn FROM [20% limits] WHERE t1.[VehicleName] = vn) THEN NULL
ELSE t1.[VehicleName]
END as [VehicleName]
from [Ordered List] t1
join [Ordered List] t2
on t1.[VehicleName]=t2.[VehicleName]
and t1.rn = t2.rn -1
)

INSERT INTO [dbo].[ConsumptionSummary]
([VehicleName],[Top20PctAveragePerMinute])
--output inserted.*

select [VehicleName]
,convert(float,sum(m))/sum(ms) * 60000
from [Consumption Stats] cs
WHERE VehicleName IS NOT null
group by [VehicleName]
GO

-- CLEAN UP
drop index ix_name ON [dbo].[MeterReadings]
GO
truncate table [ConsumptionSummary]
GO


Du har till den 30/11 på dig att komma med nya förslag...!

Exekveringstid för denna lösning: 1,44 s
CPU-tid: 2,12 s

Utresultatet OK (men inga garantier för att det blir korrekt på annan indata)

MVH
Johan

kewin
New Member
New Member
Posts: 4


--
24 Nov 2010 03:02 PM
Får väl slänga in en 'klassisk' iterations-lösning som förslag...
Ska bli spännande att se hur den far, speciellt med fler fordon och fler mätpunkter...


/******************************************
Kenneth Wilhelmsson
Cybernetics AB
Edition 1, version 1
******************************************/
-- setup
create unique nonclustered index VehicleName_StartTime_uix
on dbo.MeterReadings(VehicleName asc, StartTime desc) include (MeterValue) with (fillfactor = 100)
create nonclustered index VehicleName_ix
on dbo.MeterReadings(VehicleName asc) with (fillfactor = 100)
go
truncate table dbo.ConsumptionSummary
go

-- Main run
set nocount on
declare @VehicleName varchar(50)

declare @c table ( VehicleName varchar(50) not null, cnt int not null )
insert @c select VehicleName, count(*) from dbo.MeterReadings group by VehicleName

while exists ( select * from @c )
begin
select @VehicleName = ( select top 1 VehicleName from @c )

insert dbo.ConsumptionSummary ( VehicleName, Top20PctAveragePerMinute )
select z.VehicleName,
((sum(z.conPerUnit * z.duration) / sum(z.duration)) * 1000 * 60) as Top20PctAveragePerMinute
from (
select i.VehicleName,
datediff(ms, u.StartTime, i.StartTime) as duration,
(cast(i.MeterValue as float) - cast(u.MeterValue as float)) /
cast(datediff(ms, u.StartTime, i.StartTime) as float) as conPerUnit,
row_number() over
( order by (cast(i.MeterValue as float) - cast(u.MeterValue as float)) /
cast(datediff(ms, u.StartTime, i.StartTime) as float) desc, i.StartTime desc ) as srow
from (
select row_number() over ( order by i.VehicleName asc, i.StartTime desc) as i_rownum,
i.VehicleName, i.StartTime, i.MeterValue
from dbo.MeterReadings i
where i.VehicleName = @VehicleName
) i
join
(
select row_number() over ( order by u.VehicleName asc, u.StartTime desc) as u_rownum,
u.VehicleName, u.StartTime, u.MeterValue
from dbo.MeterReadings u
where u.VehicleName = @VehicleName
) u
on i.i_rownum = u.u_rownum - 1
) z
join @c c
on z.srow <= c.cnt * 0.2
and z.VehicleName = c.VehicleName
group by z.VehicleName

delete @c where VehicleName = @VehicleName
end
go

-- cleanup
drop index MeterReadings.VehicleName_StartTime_uix
drop index MeterReadings.VehicleName_ix
set nocount off
go
JAhlen
Veteran Member
Veteran Member
Posts: 144


--
25 Nov 2010 09:23 AM
kewin skrev:
Får väl slänga in en 'klassisk' iterations-lösning som förslag...
Ska bli spännande att se hur den far, speciellt med fler fordon och fler mätpunkter...


/******************************************
Kenneth Wilhelmsson
Cybernetics AB
Edition 1, version 1
******************************************/
-- setup
create unique nonclustered index VehicleName_StartTime_uix
on dbo.MeterReadings(VehicleName asc, StartTime desc) include (MeterValue) with (fillfactor = 100)
create nonclustered index VehicleName_ix
on dbo.MeterReadings(VehicleName asc) with (fillfactor = 100)
go
truncate table dbo.ConsumptionSummary
go

-- Main run
set nocount on
declare @VehicleName varchar(50)

declare @c table ( VehicleName varchar(50) not null, cnt int not null )
insert @c select VehicleName, count(*) from dbo.MeterReadings group by VehicleName

while exists ( select * from @c )
begin
select @VehicleName = ( select top 1 VehicleName from @c )

insert dbo.ConsumptionSummary ( VehicleName, Top20PctAveragePerMinute )
select z.VehicleName,
((sum(z.conPerUnit * z.duration) / sum(z.duration)) * 1000 * 60) as Top20PctAveragePerMinute
from (
select i.VehicleName,
datediff(ms, u.StartTime, i.StartTime) as duration,
(cast(i.MeterValue as float) - cast(u.MeterValue as float)) /
cast(datediff(ms, u.StartTime, i.StartTime) as float) as conPerUnit,
row_number() over
( order by (cast(i.MeterValue as float) - cast(u.MeterValue as float)) /
cast(datediff(ms, u.StartTime, i.StartTime) as float) desc, i.StartTime desc ) as srow
from (
select row_number() over ( order by i.VehicleName asc, i.StartTime desc) as i_rownum,
i.VehicleName, i.StartTime, i.MeterValue
from dbo.MeterReadings i
where i.VehicleName = @VehicleName
) i
join
(
select row_number() over ( order by u.VehicleName asc, u.StartTime desc) as u_rownum,
u.VehicleName, u.StartTime, u.MeterValue
from dbo.MeterReadings u
where u.VehicleName = @VehicleName
) u
on i.i_rownum = u.u_rownum - 1
) z
join @c c
on z.srow <= c.cnt * 0.2
and z.VehicleName = c.VehicleName
group by z.VehicleName

delete @c where VehicleName = @VehicleName
end
go

-- cleanup
drop index MeterReadings.VehicleName_StartTime_uix
drop index MeterReadings.VehicleName_ix
set nocount off
go

Exekveringstid: 0,98 s
CPU-tid: 2,81 s

Utresultatet är OK!

MVH
Johan

JAhlen
Veteran Member
Veteran Member
Posts: 144


--
25 Nov 2010 09:30 AM

Preliminär ställning:

***************************************************************
* Namn * Företag * Tid *
***************************************************************
* Kenneth Wilhelmsson * Cybernetics AB * 0,98 s *
* Magnus Sörin * Avanade * 1,28 s *
* Sergey Klimkevich * Memnon Networks * 1,44 s *
* Ola Hallengren * Saxo Bank * 2,15 s *
***************************************************************

Obs att de avgörande körningarna kommer att göras på en större mängd indata och att resultatlistan då kan ändras radikalt!

MVH
Johan

You are not authorized to post a reply.
Page 2 of 4 << < 1234 > >>