en-USsv-SE

Active Forums

PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 16 Apr 2013 12:51 PM by  HenrikF
SQLug.se challenge 2012
 30 Replies
Sort:
You are not authorized to post a reply.
Page 2 of 2 << < 12
Author Messages
ToineR
New Member
New Member
Posts: 12


--
06 Jul 2012 08:30 PM
Och har är min snabbaste kod:

;WITH Blankning AS (
SELECT TransId
, Konto
, Instrument
, AckKvantitet
, SUM(IsBeginning) OVER(PARTITION BY Konto, Instrument ORDER BY TransId ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BlankningsNr
FROM (VALUES(1)) AS Fake(Value)
CROSS APPLY (
SELECT TransId
, Konto
, Instrument
, Kvantitet
, SUM(Kvantitet) OVER(PARTITION BY Konto, Instrument ORDER BY TransID ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS AckKvantitet
FROM Transaktioner
) AS RunningTotals
CROSS APPLY (
SELECT AckKvantitet - Kvantitet AS PrevKvantitet
) AS Calculation
CROSS APPLY (
SELECT CASE WHEN (AckKvantitet < 0 AND PrevKvantitet >= 0) THEN 1 ELSE 0 END AS IsBeginning
) AS Flags
WHERE AckKvantitet < 0 OR PrevKvantitet < 0
)

SELECT TransId
, Konto
, Instrument
, AckKvantitet
, BlankningsNr
, ROW_NUMBER() OVER(PARTITION BY Konto, Instrument, BlankningsNr ORDER BY TransId ASC) AS RadNr
FROM Blankning
ORDER BY TransId;
Mikael Eriksson
New Member
New Member
Posts: 12


--
06 Jul 2012 08:58 PM
Min snabbaste.

with Ack as (
select TransId,
Konto,
Instrument,
Kvantitet,
sum(Kvantitet) over(
partition by Konto, Instrument
order by TransId rows unbounded preceding
) as AckKvantitet
from dbo.Transaktioner
),
BNr as (
select TransId,
Konto,
Instrument,
AckKvantitet,
sum(case when AckKvantitet < Kvantitet then 0 else 1 end)
over(
partition by Konto, Instrument
order by TransId rows unbounded preceding
) as BlankningsNr
from Ack
where AckKvantitet < Kvantitet or
AckKvantitet < 0
)
select TransId,
Konto,
Instrument,
AckKvantitet,
BlankningsNr,
row_number() over(
partition by Konto, Instrument, BlankningsNr
order by TransId
) as RadNr
from BNr
order by
TransId;



JAhlen
Veteran Member
Veteran Member
Posts: 144


--
06 Jul 2012 10:08 PM
Ja, vi skall publicera dem!
/Johan

HenrikF skrev:
Stort Grattis till Vinnaren!

Det skulle vara väldigt kul att få se i alla fall de top 5 bästa lösningarna.
//henrik

Christian J
New Member
New Member
Posts: 9


--
09 Jul 2012 01:46 AM
Tack för en återigen fantastisk tävling! Visst är det roligt att se koden lira precis innan semestern och att vinna en mobil precis när man längtar efter en ny när den befintliga börjar strula. Det var ett motiverande pris och ett lärorikt arbetspass med SQL Server 2012.

De 11 högst rankande lösningarna ligger väldigt nära varann och ser säkert väldigt lika ut. Själv skickade jag in nästa samma kod i 3 variationer samt en kopia av varje där ett index skapas och droppas. Jag valde en CTE med Windows Functions där en variation är identisk med Mickes lösning. Jag kan rekommendera boken "Microsoft SQL Server 2012 High-Performance T-SQL Using Windows Functions" om detta, en till rolig och inspirerande läsning från Itzik Ben-Gan.

För att lösa problemet effektivt måste man nog börja med en running total via de nya fönsterfunktionerna för att få AckKvantitet partitionerat på Konto+Instrument. Sedan är det intressant vilka rader som tillhör en blankningsaffär och vilka som är den första inom en sådan. I just den vinnande lösningen använde jag den nya funktionen lag() för att jämföra den aktuella AckKvantitet med den föregående inom partitionen. Att använda Kvantitet och AckKvantitet istället var lite långsammare.

Det lönar sig förresten inte att skapa en generisk nyckel till Konto+Instrument eller att skapa index. Däremot hade first_value() kanske hjälpt, men den funktionen kunde inte jag under tävlingen än. Den vinnande lösningen var inte snabbast hos mig, men jag kunde inte använda mig av parallelisering i min testmiljö.

Tack Johan, Peter samt Tibor och Lars för allt jobb med uppgiften. Det är inte lätt att arrangera och det fanns väldigt få oklarheter i problemställningen, bra jobbat!

Hoppas vi ses på nästa SQLUG träff,
Christian
stefan gustafsson
New Member
New Member
Posts: 29


--
13 Jul 2012 10:10 PM
Hej

Nu har det gått en vecka sedan tävlingen avgjordes. Jag är mycket nyfiken på hur de olika lösningarna ser ut - i synnerhet vinnaren.

NäR tänker ni egentligen publicera lösningarna?

/SG

JAhlen wrote:
Ja, vi skall publicera dem!
/Johan

HenrikF skrev:
Stort Grattis till Vinnaren!

Det skulle vara väldigt kul att få se i alla fall de top 5 bästa lösningarna.
//henrik

Christian J
New Member
New Member
Posts: 9


--
13 Jul 2012 10:52 PM
/*** Solution3 Christian Janssen */

WITH	   Step1
		AS (SELECT tr.TransId,
				tr.Konto,
				tr.Instrument,
				AckKvantitet = sum(tr.Kvantitet) OVER (PARTITION BY tr.Konto, tr.Instrument 
														ORDER BY tr.TransId
														ROWS BETWEEN UNBOUNDED PRECEDING
														AND CURRENT ROW)
			FROM dbo.Transaktioner AS tr),				
	   Step2
		AS (SELECT s1.TransId,
				s1.Konto,
				s1.Instrument,
				s1.AckKvantitet,
				IsFirstInBlanking = CASE	WHEN s1.AckKvantitet >= 0 
								 OR isnull(Lag(s1.AckKvantitet,1) 
									OVER (PARTITION BY s1.Konto, s1.Instrument
										  ORDER BY s1.TransId),0) < 0
											THEN 0
											ELSE 1
								END,
				IsNotInBlanking = CASE	WHEN s1.AckKvantitet >= 0 
								 AND isnull(Lag(s1.AckKvantitet,1) 
									OVER (PARTITION BY s1.Konto, s1.Instrument
										  ORDER BY s1.TransId),0) >= 0
											THEN 1
											ELSE 0
								END
			 FROM Step1 AS s1),
	   Step3
		AS (SELECT s2.TransId,
				s2.Konto,
				s2.Instrument,
				s2.AckKvantitet,
				BlankingsNr1 = sum(s2.IsFirstInBlanking) OVER (PARTITION BY s2.Konto, s2.Instrument
															ORDER BY s2.TransId
															ROWS BETWEEN UNBOUNDED PRECEDING
															AND CURRENT ROW)
			 FROM Step2 AS s2
			 WHERE s2.IsNotInBlanking=0)
	SELECT s3.TransId,
		  s3.Konto,
		  s3.Instrument,
		  s3.AckKvantitet,
			s3.BlankingsNr,
		  RadNr = row_number() OVER (PARTITION BY s3.Konto, s3.Instrument, s3.BlankingsNr1
									 ORDER BY s3.TransId)
	   FROM Step3 AS s3
	   ORDER BY s3.TransId
GO
/**** end of script :CJ: ***/
JAhlen
Veteran Member
Veteran Member
Posts: 144


--
16 Jul 2012 01:38 PM
Det är semestertider... :-)

Jag har själv varit ute och rest. Skall publicera så snart som möjligt.

stefang skrev:
Hej

Nu har det gått en vecka sedan tävlingen avgjordes. Jag är mycket nyfiken på hur de olika lösningarna ser ut - i synnerhet vinnaren.

NäR tänker ni egentligen publicera lösningarna?

/SG

JAhlen wrote:
Ja, vi skall publicera dem!
/Johan

HenrikF skrev:
Stort Grattis till Vinnaren!

Det skulle vara väldigt kul att få se i alla fall de top 5 bästa lösningarna.
//henrik

JAhlen
Veteran Member
Veteran Member
Posts: 144


--
16 Jul 2012 01:53 PM
Här är de 5 bästa lösningarna.

/*********************************************************************************************/
/*** Christian Janssen v3 */
/*** run query **/
WITH Step1
AS (SELECT tr.TransId,
tr.Konto,
tr.Instrument,
AckKvantitet = sum(tr.Kvantitet) OVER (PARTITION BY tr.Konto, tr.Instrument
ORDER BY tr.TransId
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
FROM dbo.Transaktioner AS tr),
Step2
AS (SELECT s1.TransId,
s1.Konto,
s1.Instrument,
s1.AckKvantitet,
IsFirstInBlanking = CASE WHEN s1.AckKvantitet >= 0
OR isnull(Lag(s1.AckKvantitet,1)
OVER (PARTITION BY s1.Konto, s1.Instrument
ORDER BY s1.TransId),0) < 0
THEN 0
ELSE 1
END,
IsNotInBlanking = CASE WHEN s1.AckKvantitet >= 0
AND isnull(Lag(s1.AckKvantitet,1)
OVER (PARTITION BY s1.Konto, s1.Instrument
ORDER BY s1.TransId),0) >= 0
THEN 1
ELSE 0
END
FROM Step1 AS s1),
Step3
AS (SELECT s2.TransId,
s2.Konto,
s2.Instrument,
s2.AckKvantitet,
BlankingsNr1 = sum(s2.IsFirstInBlanking) OVER (PARTITION BY s2.Konto, s2.Instrument
ORDER BY s2.TransId
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
FROM Step2 AS s2
WHERE s2.IsNotInBlanking=0)
SELECT s3.TransId,
s3.Konto,
s3.Instrument,
s3.AckKvantitet,
s3.BlankingsNr1,
RadNr1 = row_number() OVER (PARTITION BY s3.Konto, s3.Instrument, s3.BlankingsNr1
ORDER BY s3.TransId)
FROM Step3 AS s3
ORDER BY s3.TransId
GO
/**** end of script :CJ: ***/
/*********************************************************************************************/
-- Stefan Gustafsson, Acando AB
-- v7
-- Använd en binary collation för att snabba upp partitionering
;with
cte0 as (
-- Byt till binary collation på Instrument för att snabba upp sorteringen
select TransId, Konto, Kvantitet, Instrument collate Finnish_Swedish_BIN as Instrument
from Transaktioner
)
,cte1 as (
-- Running sum per konto och instrument
select *,
sum(Kvantitet) over (partition by Konto, Instrument order by TransId rows unbounded preceding) as AckKvantitet
from cte0
)
, cte2 as (
-- Filtrera fram raderna vi är intresserade av och markera första raden i varje blankningsaffär
select *,
case when AckKvantitet < 0 and AckKvantitet-Kvantitet >= 0 then 1 else 0 end as isFirst
from cte1
where AckKvantitet < 0 or AckKvantitet-Kvantitet < 0
)
, cte3 as (
-- Lägg till ett Blankningsnummer som running sum av isFirst
select *,
sum(isFirst) over (partition by Konto, Instrument order by TransId rows unbounded preceding) as BlankningsNr
from cte2
)
, cte4 as (
-- Lägg till ett radnummer inom varje blankningsaffär
select *,
row_number() over (partition by Konto, Instrument, BlankningsNr order by TransId) as RadNr
from cte3
)
-- Returnera önskat resultat sorterat på TransId
select TransId, Konto, Instrument, AckKvantitet, BlankningsNr, RadNr
from cte4
order by TransId
/*********************************************************************************************/
/* Johan Jersby v3 */
WITH CTE_1
AS
(
SELECT
TRANSID
,KONTO
,INSTRUMENT
,KVANTITET
,SUM(KVANTITET)
OVER (PARTITION BY KONTO, INSTRUMENT
ORDER BY TRANSID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 'ACKKVANTITET'
,SUM(KVANTITET)
OVER (PARTITION BY KONTO, INSTRUMENT
ORDER BY TRANSID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - KVANTITET AS 'PREVKVANTITET'
FROM
TRANSAKTIONER
)
, CTE_2 AS
(
SELECT
TRANSID
,KONTO
,INSTRUMENT
,ACKKVANTITET
,SUM(CASE WHEN ACKKVANTITET < 0 AND PREVKVANTITET >= 0 THEN 1 ELSE 0 END)
OVER (PARTITION BY KONTO, INSTRUMENT
ORDER BY TRANSID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BLANKNINGSNUMMER
FROM
CTE_1
WHERE
ACKKVANTITET < 0
OR (ACKKVANTITET >= 0 AND PREVKVANTITET < 0)
)
SELECT
TRANSID
,KONTO
,INSTRUMENT
,ACKKVANTITET
,BLANKNINGSNUMMER
,ROW_NUMBER()
OVER(PARTITION BY KONTO, INSTRUMENT, BLANKNINGSNUMMER
ORDER BY TRANSID) AS RADNR
FROM
CTE_2
ORDER BY
TRANSID
/*********************************************************************************************/
/* Toine Rozemeijer v4 */
WITH Blankning AS (
SELECT TransId
, Konto
, Instrument
, AckKvantitet
, SUM(IsBeginning) OVER(PARTITION BY Konto, Instrument ORDER BY TransId ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BlankningsNr
FROM (VALUES(1)) AS Fake(Value)
CROSS APPLY (
SELECT TransId
, Konto
, Instrument
, Kvantitet
, SUM(Kvantitet) OVER(PARTITION BY Konto, Instrument ORDER BY TransID ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS AckKvantitet
FROM Transaktioner
) AS RunningTotals
CROSS APPLY (
SELECT AckKvantitet - Kvantitet AS PrevKvantitet
) AS Calculation
CROSS APPLY (
SELECT CASE WHEN (AckKvantitet < 0 AND PrevKvantitet >= 0) THEN 1 ELSE 0 END AS IsBeginning
) AS Flags
WHERE AckKvantitet < 0 OR PrevKvantitet < 0
)
SELECT TransId
, Konto
, Instrument
, AckKvantitet
, BlankningsNr
, ROW_NUMBER() OVER(PARTITION BY Konto, Instrument, BlankningsNr ORDER BY TransId ASC) AS RadNr
FROM Blankning
ORDER BY TransId;
/*********************************************************************************************/
/* Steinar Andersen v11 */
with cte1 as (
select transid, konto, instrument, kvantitet,
SUM(kvantitet) OVER(PARTITION BY konto, instrument ORDER BY transid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS AckKvantitet
from dbo.transaktioner ),
cte2 as (
select transid, konto, instrument, kvantitet, ackkvantitet
,case when (ackkvantitet < 0) and ((ackkvantitet - kvantitet) >= 0) then 1 else 0 end as NyBlankning
from cte1
where
(ackkvantitet < 0) or
(ackkvantitet - kvantitet) < 0
)
,
cte3 as (
select transid, konto, instrument, ackkvantitet
, sum (Nyblankning) over (PARTITION BY konto, instrument ORDER BY transid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as BlankningsNr
from cte2
)
Select transid, konto, instrument, ackkvantitet, BlankningsNr
,ROW_NUMBER() OVER(PARTITION BY konto, instrument, blankningsnr ORDER BY transid ) AS RadNr
from cte3
order by transid
stefan gustafsson
New Member
New Member
Posts: 29


--
10 Sep 2012 11:28 AM
Hej


Till att börja med vill jag tacka för en väl genomförd tävling, med en intressant och mycket väldefinierad uppgift.


Jag tycker dock att resultatet är konstigt. I mina tester har jag aldrig lyckats få det vinnande bidraget att bli snabbare än mitt eget bidrag som kom tvåa. Jag har ju dock inte tillgång till exakt den maskin som ni körde testerna på, jag har inte heller tillgång till exakt det dataset som användes.


Jag är helt enkelt nyfiken på vad det kan vara som gör att det vinnande bidraget går snabbare på tävlingsmaskinen men betydligt långsammare på min egen maskin. Observera att jag inte är intresserad av att ändra tävlingens resultat. Jag är bara intresserad av att förstå hur det kommer sig att resultaten blivit som de blivit.


Mina egna resultat (från min laptop med en dual core cpu och 4G RAM) ser ut så här:


Vinnande lösning:

Duration=12 043
CPU=15 099
Rows=446 888


Min lösning:

Duration=6 768
CPU=9 327
Rows=446 888


Datasetet som använt i detta test har genererats med följande script:

-- create performance test data
-- parameters
declare @trancount int = 1000000 -- total number of transactions
declare @account_count int = 100 -- number of accounts
declare @instr_count int = 10 -- number of different instruments
declare @max_amount int = 1000 -- max amount
declare @offset_amount int = 5 -- nollpunktsjustering (Lägre värde ger mer blankning, dvs fler rader i resultatet)
-- variables
declare @i int
declare @acc int
declare @instr int
declare @amt int
declare @sinstr char(6)
-- for repeatability
set @i=rand(1)
set nocount on
truncate table transaktioner
begin tran
set @i=0
while @i < @trancount begin
set @acc=rand()*@account_count+1000
set @instr=rand()*@instr_count
set @amt=2*rand()*@max_amount-@max_amount+@offset_amount
set @sinstr=cast(@instr as char(6))
set @sinstr=replicate('A',6-len(@sinstr))+@sinstr
INSERT INTO [dbo].[Transaktioner]
([TransId]
,[Konto]
,[Instrument]
,[Kvantitet])
VALUES
(@i, @acc, @sinstr, @amt)
set @i=@i+1
end
commit


Som synes är i detta test min lösning nästan dubbelt så snabb som den vinnande lösningen&hellip;


För att få fram tiderna ovan har jag använt ett gratisverktyg som heter SQL Sentry Plan Explorer (Actual Plan)
http://www.sqlsentry.com/plan-explo...y-view.asp


Det verktyget ställer en fråga och ignorerar resultatet vilket borde göra att siffrorna liknar det som man får från den test harness som användes i tävlingen. Kör man med SSMS får man en för hög duration eftersom tiden för att lagra resultatet i SSMS tas med.


Jag är mycket nyfiken på vad som händer om man gör exakt samma test på maskinen som användes i tävlingen.


Det vore också mycket intresssant att få se en actual execution plan (i form av en .sqlplan) från en körning med det dataset som användes i tävlingen både med min lösning och den vinnande lösningen.


Tack på förhand


Stefan Gustafsson

JAhlen
Veteran Member
Veteran Member
Posts: 144


--
19 Sep 2012 02:19 PM
Hej Stefan,

Det är flera faktorer som spelar roll, såsom hur testdatasetet ser ut och vad man kör på för hårdvara (i vårt fall en hyfsat kraftfull server). Vi hade 5 miljoner transaktioner fördelat på 400 instrument och 1500 olika konton, och en högre andel blankningsaffärer än den testdata som genereras av ditt script. Vi tar gärna detaljerna via email.

Nästa tävling funderar vi på att köra i SQL Azure, så får alla möjlighet att testa själva i liknande miljö.

MVH
Johan
HenrikF
New Member
New Member
Posts: 8


--
16 Apr 2013 12:51 PM
Någon ny tävling på gång?

Att ha den &iacute; SQL Azure tycker jag låter som en utmärkt ide!
//henrik
You are not authorized to post a reply.
Page 2 of 2 << < 12