en-USsv-SE

Active Forums

PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 10 Dec 2014 09:24 AM by  Mikael Eriksson
SQLug.se Challenge 2014
 73 Replies
Sort:
You are not authorized to post a reply.
Page 4 of 4 << < 1234
Author Messages
JAhlen
Veteran Member
Veteran Member
Posts: 144


--
01 Dec 2014 02:26 PM
Hej,

Tack för alla bidrag. Testning pågår för fullt och resultat kommer att meddelas när vi 100% har säkerställt en vinnare. Då går vi också ut med lite mer info om tävlingen.

MVH
Johan
Daniel Hutmacher
New Member
New Member
Posts: 17


--
01 Dec 2014 05:01 PM
Instämmer med Jonas. En rolig och relativt svår uppgift, sånt ser vi gärna mer av framöver! Såvitt jag vet, innan vi får slutresultatet, så var mitt snabbaste bidrag helt utan SQL Server 2014-features. Kanske för att det här inte är något typiskt OLTP-/concurrency-problem eller, mer sannolikt, för att jag inte begriper alla in-memory-grejor med deltastores, hash-index och buckets tillräckligt bra ännu.

Sjukt nyfiken på resultatet nu!
Preacher
New Member
New Member
Posts: 5


--
03 Dec 2014 10:27 AM
Ja, jag får tacka för en rolig uppgift. Dock insåg jag dagen efter inlämningsstopp att jag hade missat på dygnsbrytningsgränsen för T-kategorin. Men jag ser redan fram mot nästa års uppgift. Skam den som ger sig!
Preacher
New Member
New Member
Posts: 5


--
03 Dec 2014 10:27 AM
Ja, jag får tacka för en rolig uppgift. Dock insåg jag dagen efter inlämningsstopp att jag hade missat på dygnsbrytningsgränsen för T-kategorin. Men jag ser redan fram mot nästa års uppgift. Skam den som ger sig!
JAhlen
Veteran Member
Veteran Member
Posts: 144


--
09 Dec 2014 10:22 AM
SQLUG Challenge 2014 är avgjord.

Stort grattis till Mikael Eriksson, CGM, för 1:a plats!

Grattis också till Stefan Gustafsson, Acando, på 2:a plats och Sergey Klimkevich, Thomas Cook, på 3:e plats!

Ett hedersomnämnande till Daniel Hutmacher, som hade ett bidrag som låg på ungefär samma prestanda som vinnarens, men som föll i vårt sista test.

Tävlingen var ovanligt svår, då endast 6 personer hade skickat in en korrekt fungerande lösning. Totalt fick vi in 47 tävlingsbidrag.

Vi föreslår att vinnarna själva postar sina lösningar här på forumet, med kommentarer om metod.

MVH
Johan Åhlén
Problemkonstruktör och juryordförande

Jonas
New Member
New Member
Posts: 13


--
09 Dec 2014 10:35 AM
Stor grattis till vinnaren!

Ska bli jättekul att få se några lösningar! Hoppas top 3 delar med sig

Är det möjligt att posta den totala resultatlistan här? Skulle vara roligt att se om jag åtminstonde lyckades lösa uppgiften.
Martin Aronsson
New Member
New Member
Posts: 2


--
09 Dec 2014 11:18 AM
Grattis till vinnaren i denna kluriga tävling! Skulle vara mycket intressant att se några lösningar.

Martin
JAhlen
Veteran Member
Veteran Member
Posts: 144


--
09 Dec 2014 01:26 PM
Här är resultatlistan: http://sqlugchallenge.azurewebsites...ultat.xlsx

Om ert bidrag inte finns med så beror det på att det inte gick att köra eller att vi tidigare har konstaterat att det ger timeoutfel och har uteslutit det. Om du ändå har frågor så är du välkommen att maila på info@sqlug.se.

Vinnaren Mikael Eriksson har lovat att posta sitt tävlingsbidrag på forumet.

MVH
Johan
Daniel Hutmacher
New Member
New Member
Posts: 17


--
09 Dec 2014 01:32 PM
Stort grattis till de tre toppplaceringarna, Mikael, Stefan och Sergey!

Återigen, en kul tävling och en utmanande uppgift!
Preacher
New Member
New Member
Posts: 5


--
09 Dec 2014 03:48 PM
Stort grattis till Mikael, Stefan och Sergey, samt de övriga som hade rätt resultat.
Preacher
New Member
New Member
Posts: 5


--
09 Dec 2014 03:48 PM
Stort grattis till Mikael, Stefan och Sergey, samt de övriga som hade rätt resultat.
LarsOve
New Member
New Member
Posts: 6


--
09 Dec 2014 06:09 PM
Tack till SQLug för ännu en rolig tävling. Ser fram emot att ta del av vinnarnas lösningar, men är även mycket intresserad hur Daniel Hutmacher lyckats få en så bra tid utan hjälp av nyheterna i SQL Server 2014.

Nedan har ni min snabbaste lösning.


-------------------------------------------------------------------------
-- Initiate.
-------------------------------------------------------------------------

IF OBJECT_ID(&#39;JusteraT&#39;, &#39;P&#39;) IS NOT NULL DROP PROCEDURE [dbo].[JusteraT]
IF OBJECT_ID(&#39;Kolumnvis&#39;, &#39;U&#39;) IS NOT NULL DROP TABLE [dbo].[Kolumnvis]
IF OBJECT_ID(&#39;BeraknaErsattning&#39;, &#39;P&#39;) IS NOT NULL DROP PROCEDURE [dbo].[BeraknaErsattning]
GO


CREATE TABLE [dbo].[Kolumnvis]
(
	[Nummer] [int] NOT NULL
	,[Vardenhet] [char](5) COLLATE Finnish_Swedish_100_BIN2 NOT NULL
	,[PatientID] [uniqueidentifier] NOT NULL
	,[Datum] [DATE] NOT NULL
	,[AK] [int] NOT NULL
	,[TL] [int] NOT NULL
	,[TH] [int] NOT NULL

	-- Add a hash index for the equi-joins.
	,CONSTRAINT [PK_Kolumnvis] PRIMARY KEY NONCLUSTERED HASH
	(
		[Nummer]
	)
	WITH (BUCKET_COUNT = 2097152) -- Hopefully a good size.

	-- And a range index for getting multiple rows from Kolumnvis.
	,INDEX [IX_Kolumnvis] NONCLUSTERED 
	(
		[Vardenhet]
		,[PatientID]
		,[Datum]
	)
)

-- By using SCHEMA_ONLY instead of SCHEMA_AND_DATA we avoid adding 
-- the changes in the transaction log.
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);


GO


CREATE PROCEDURE dbo.JusteraT
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL =  SNAPSHOT, LANGUAGE=N&#39;us_english&#39;)

	DECLARE @Nummer INT = 1
	DECLARE @PatientID UNIQUEIDENTIFIER
	DECLARE @Datum DATE
	DECLARE @Vardenhet CHAR(5) 
	DECLARE @AK INT
	DECLARE @TL INT
	DECLARE @TH INT	
	DECLARE @Count INT

	-- For each row in Kolumnvis, check if TL and TH should be paid or not.	 
	SELECT @Count = MAX(Nummer) FROM dbo.Kolumnvis
	WHILE @Nummer <= @Count
	BEGIN
		SELECT
			@PatientID = PatientID
			,@Datum = Datum
			,@Vardenhet = Vardenhet
			,@AK = AK
			,@TL = TL
			,@TH = TH
		FROM
			dbo.Kolumnvis
		WHERE
			Nummer = @Nummer

		-- Clear TH 90 days forward, if more expensive than AK.
		IF @TH > @AK
		BEGIN
			UPDATE 
				dbo.Kolumnvis 
			SET
				TH = 0
			WHERE
				Vardenhet = @Vardenhet
				AND PatientID = @PatientID
				AND Datum > @Datum
				AND Datum <= DATEADD(DAY, 90, @Datum)
				AND TH > 0

			-- AK will hold the final price.
			UPDATE dbo.Kolumnvis SET AK = @TH WHERE Nummer = @Nummer
		END

		-- Clear TL 90 days forward, if more expensive than AK.
		ELSE IF @TL > @AK
		BEGIN

			UPDATE 
				dbo.Kolumnvis 
			SET
				TL = 0
			WHERE
				Vardenhet = @Vardenhet
				AND PatientID = @PatientID
				AND Datum > @Datum
				AND Datum <= DATEADD(DAY, 90, @Datum)
				AND TL > 0

			-- AK will hold the final price.
			UPDATE dbo.Kolumnvis SET AK = @TL WHERE Nummer = @Nummer

		END
		
		SET @Nummer = @Nummer + 1

	END
END

GO

CREATE PROCEDURE [dbo].[BeraknaErsattning]
AS
	SET NOCOUNT ON

	-- Since the price for T01 and T02 could change we must check which one 
	-- are the most expensive. According to the rules they will never have 
	-- the same price as other taxekoder.
	DECLARE @TL CHAR(3) = (SELECT TOP 1 Taxekod FROM dbo.Prislista WHERE Taxekod LIKE &#39;T%&#39; ORDER BY Pris)
	DECLARE @TH CHAR(3)
	IF @TL = &#39;T02&#39; 
		SET @TH = &#39;T01&#39;
	ELSE 
		SET @TH = &#39;T02&#39;


	-- Get the  different types of visits into different columns.
	;WITH BesokOchDatum AS
	(
		SELECT
			Besok.Vardenhet
			,Besok.PatientId
			,CAST(Besok.DatumOchTid AS Date) AS Datum
			,Prislista.Pris
			,Prislista.Taxekod
		FROM
			dbo.Besok

			INNER JOIN dbo.Prislista
			ON Besok.Taxekod = Prislista.Taxekod
	)

	,AKTHTL AS
	(
		-- Get the sum of the two different Kombinationsbes&#246;k groups.
		SELECT
			Vardenhet
			,PatientID
			,Datum
			,MAX(CASE WHEN Taxekod <= &#39;K02&#39; THEN Pris ELSE 0 END) + MAX(CASE WHEN Taxekod > &#39;K02&#39; THEN Pris ELSE 0 END) AS AK
			,0 AS TL
			,0 AS TH
		FROM
			BesokOchDatum
		WHERE
			Taxekod LIKE &#39;K%&#39;
		GROUP BY
			Vardenhet
			,PatientID
			,Datum

		UNION ALL

		-- Get the Allm&#228;nna bes&#246;k.
		SELECT
			Vardenhet
			,PatientID
			,Datum
			,Pris AS AK
			,0 AS TL
			,0 AS TH
		FROM
			BesokOchDatum
		WHERE
			Taxekod LIKE &#39;A%&#39;

		UNION ALL

		-- Cheapest 90-days visit.
		SELECT
			Vardenhet
			,PatientID
			,Datum
			,0 AS AK
			,Pris AS TL
			,0 AS TH
		FROM
			BesokOchDatum
		WHERE
			Taxekod = @TL

		UNION ALL

		-- Most expensive 90-days visit.
		SELECT
			Vardenhet
			,PatientID
			,Datum
			,0 AS AK
			,0 AS TL
			,Pris AS TH
		FROM
			BesokOchDatum
		WHERE
			Taxekod = @TH
	)

	-- Get one row for each vardenhet, patient and day.
	INSERT INTO dbo.[Kolumnvis] (Nummer, Vardenhet, PatientID, Datum, AK, TL, TH)
		SELECT
			ROW_NUMBER() OVER (ORDER BY Vardenhet, PatientID, Datum) AS Nummer
			,Vardenhet
			,PatientID
			,Datum
			,MAX(AK) AS AK
			,MAX(TL) AS TL
			,MAX(TH) AS TH
		FROM
			AKTHTL
		GROUP BY
			Vardenhet
			,PatientID
			,Datum


	-- Adjust the price for TL and TH.
	EXEC dbo.JusteraT


	-- Calculate and insert the ersattning.
	;WITH PatientSummor AS
	(
		SELECT
			 k.Vardenhet
			,k.Datum
			,k.AK AS Pris -- AK now holds either AK, TL or TH.
		FROM
			dbo.Kolumnvis AS k
					
		UNION ALL

		-- Add the behandlingsbes&#246;k into the price.
		SELECT
			Besok.Vardenhet
			,CAST(Besok.DatumOchTid AS Date) AS Datum
			,Prislista.Pris
		FROM
			dbo.Besok

			INNER JOIN dbo.Prislista
			ON Besok.Taxekod = Prislista.Taxekod
		WHERE
			Besok.Taxekod LIKE &#39;B%&#39;		

	)

	,ErsattningNy AS
	(
		SELECT
			Vardenhet
			,Datum
			,SUM(Pris) AS Belopp
		FROM
			PatientSummor
		GROUP BY
			Vardenhet
			,Datum
		HAVING
			SUM(Pris) > 0
	)
	MERGE dbo.Ersattning WITH (HOLDLOCK) AS T
	USING ErsattningNy AS S
	ON T.Vardenhet = S.Vardenhet AND T.Datum = S.Datum
	WHEN NOT MATCHED BY TARGET
		THEN INSERT(Vardenhet, Datum, Belopp) VALUES(S.Vardenhet, S.Datum, S.Belopp)
	WHEN MATCHED AND T.Belopp <> S.Belopp -- Only update if the price differs.
		THEN UPDATE SET T.Belopp = S.Belopp
	WHEN NOT MATCHED BY SOURCE
		THEN DELETE;

GO



-------------------------------------------------------------------------
-- Query.
-------------------------------------------------------------------------
EXEC [dbo].[BeraknaErsattning]

 
stefan gustafsson
New Member
New Member
Posts: 29


--
09 Dec 2014 06:30 PM
Tack för en intressant tävling. Kul med en uppgift där man fick användning för de nya finesserna i SQL2014.

Här är min snabbaste lösning:

-- l&#246;sning2.sql
--
-- Stefan Gustafsson, Acando
--
-- Processa T-bes&#246;k med hj&#228;lp av en natively compiled stored procedure
-- Processa &#246;vriga bes&#246;k med vanlig SQL f&#246;r att utnyttja parallellism s&#229; mycket som m&#246;jligt

go
if object_id(&#39;dbo.ProcessBesok&#39;) is not null drop procedure dbo.ProcessBesok
go
if object_id(&#39;dbo.RunSolution&#39;) is not null drop procedure dbo.RunSolution
go
if object_id(&#39;dbo.Ersattning2&#39;) is not null drop table dbo.Ersattning2
go
CREATE TABLE dbo.Ersattning2
(
	Vardenhet char(5) NOT NULL,
	Datum     date    NOT NULL,
	Belopp    int     NOT NULL,
	CONSTRAINT PK_Vardenhet_Datum PRIMARY KEY NONCLUSTERED HASH (Vardenhet, Datum) WITH (BUCKET_COUNT = 1000000) 
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
go
if type_id(&#39;dbo.ResultType&#39;) is not null drop type dbo.ResultType
go
CREATE TYPE dbo.ResultType AS TABLE (
	Vardenhet char(5) not null,
	Datum     date    not null,
	Belopp    int     not null,
	INDEX IX_Vardenhet_Datum HASH (Vardenhet, Datum) WITH ( BUCKET_COUNT = 1000000)
) WITH (MEMORY_OPTIMIZED = ON)
go

if type_id(&#39;dbo.BesokType&#39;) is not null drop type dbo.BesokType
go
CREATE TYPE dbo.BesokType AS TABLE (
	id        int              not null,
	Vardenhet char(5)          not null,
	PatientID uniqueidentifier not null,
	Taxekod   char(3)          not null,
	Datum     date             not null,
	INDEX IX_id HASH (id) WITH ( BUCKET_COUNT = 10000000)
) WITH (MEMORY_OPTIMIZED = ON)
go
if type_id(&#39;dbo.PrislistaType&#39;) is not null drop type dbo.PrislistaType
go
CREATE TYPE dbo.PrislistaType as table (
	Taxekod char(3) not null,
	Pris    int     not null,
	INDEX IX_Taxekod HASH (Taxekod) WITH ( BUCKET_COUNT = 30)
) WITH (MEMORY_OPTIMIZED = ON)
go
CREATE PROCEDURE dbo.ProcessBesok(@Besok dbo.BesokType READONLY, @Prislista dbo.PrislistaType READONLY)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS

BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N&#39;US_ENGLISH&#39;, DELAYED_DURABILITY=ON)

	declare @Vardenhet char(5) 
	declare @PatientID uniqueidentifier
	declare @Datum     date
	declare @Taxekod   char(3) 
	declare @Pris      int

	declare @Last_Vardenhet char(5) 
	declare @Last_PatientID uniqueidentifier
	declare @Last_Datum     date

	declare @BestA   int
	declare @SumB    int
	declare @BestK1  int
	declare @BestK2  int
	declare @BestT01 int
	declare @BestT02 int
	declare @Best    int
	declare @Next_T01_Datum date
	declare @Next_T02_Datum date

	declare @Result dbo.ResultType

	declare @id int = 1

	SELECT @Last_Vardenhet = &#39;&#39;, @Last_PatientID = newid(), @Last_Datum = &#39;19000101&#39;

	SELECT @vardenhet = b.Vardenhet, @PatientID = b.PatientID, @Datum = b.Datum, @Taxekod = b.Taxekod
	FROM   @Besok b
	WHERE  b.id = @id

	while @@rowcount > 0 begin

		SELECT @Pris = Pris
		FROM   @Prislista p 
		WHERE  p.Taxekod = @Taxekod

		if @Datum <> @Last_Datum or @PatientID <> @Last_PatientID or @Vardenhet <> @Last_Vardenhet begin
			if @Last_Vardenhet <> &#39;&#39; begin

				-- Nu har vi alla data f&#246;r en patient och dag, ber&#228;kna och spara resultat
				set @Best = @BestA
				if @BestK1 + @BestK2 > @Best 
					set @Best = @BestK1 + @BestK2
				if @BestT01 > @BestT02 begin
					if @BestT01 > @Best begin
						set @Best = @BestT01
						set @Next_T01_Datum = dateadd(day, 90, @Last_Datum)
					end
				end else begin
					if @BestT02 > @Best begin
						set @Best = @BestT02
						set @Next_T02_Datum = dateadd(day, 90, @Last_Datum)
					end
				end

				set @Best = @Best + @SumB

				if @Best <> 0 begin
					UPDATE @Result SET Belopp = Belopp + @Best WHERE Vardenhet = @Last_Vardenhet and Datum = @Last_Datum

					if @@rowcount = 0 
						INSERT INTO @Result (Vardenhet, Datum, Belopp) VALUES (@Last_Vardenhet, @Last_Datum, @Best)
				end

			end

			-- Nollst&#228;ll inf&#246;r n&#228;sta dag
			set @BestA   = 0
			set @SumB    = 0
			set @BestK1  = 0
			set @BestK2  = 0
			set @BestT01 = 0
			set @BestT02 = 0

			-- Nollst&#228;ll datum om ny patient eller v&#229;rdenhet
			if @PatientID <> @Last_PatientID or @Vardenhet <> @Last_Vardenhet begin
				set @Next_T01_Datum = &#39;19000101&#39;
				set @Next_T02_Datum = &#39;19000101&#39;
			end

		end

		declare @c char(1)
		set @c = substring(@Taxekod, 1,1)
		if @c = &#39;A&#39; begin
			-- Spara det dyraste A-bes&#246;ket f&#246;r dagen
			if @Pris > @BestA begin
				set @BestA = @Pris
			end
		end else if @c = &#39;B&#39; begin
			-- Summera alla B-bes&#246;k f&#246;r dagen
			set @SumB = @SumB + @Pris
		end else if @c = &#39;K&#39; begin
			-- Kom ih&#229;g h&#246;gsta beloppet i grupp 1 resp grupp 2
			if (@Taxekod = &#39;K01&#39; or @Taxekod = &#39;K02&#39;) and @Pris > @BestK1 begin
				set @BestK1 = @Pris
			end else if (@Taxekod = &#39;K03&#39; or @Taxekod = &#39;K04&#39;) and @Pris > @BestK2 begin
				set @BestK2 = @Pris
			end
		end else if @c = &#39;T&#39; begin
			if @Taxekod = &#39;T01&#39; begin
				if @Datum > @Next_T01_Datum begin
					set @BestT01 = @Pris
				end
			end else if @Taxekod = &#39;T02&#39; begin
				if @Datum > @Next_T02_Datum begin
					set @BestT02 = @Pris
				end
			end
		end

		SELECT @Last_Vardenhet = @Vardenhet, @Last_PatientID = @PatientID, @Last_Datum = @Datum

		set @id = @id + 1

		SELECT @vardenhet = b.Vardenhet, @PatientID = b.PatientID, @Datum = b.Datum, @Taxekod = b.Taxekod
		FROM   @Besok b
		WHERE  b.id = @id
	end

			if @Last_Vardenhet <> &#39;&#39; begin

				-- Nu har vi alla data f&#246;r en patient och dag, ber&#228;kna och spara resultat
				set @Best = @BestA
				if @BestK1 + @BestK2 > @Best 
					set @Best = @BestK1 + @BestK2
				if @BestT01 > @BestT02 begin
					if @BestT01 > @Best begin
						set @Best = @BestT01
						set @Next_T01_Datum = dateadd(day, 90, @Last_Datum)
					end
				end else begin
					if @BestT02 > @Best begin
						set @Best = @BestT02
						set @Next_T02_Datum = dateadd(day, 90, @Last_Datum)
					end
				end

				set @Best = @Best + @SumB

				if @Best <> 0 begin
					UPDATE @Result set Belopp = Belopp + @Best WHERE Vardenhet = @Last_Vardenhet and Datum = @Last_Datum

					if @@rowcount = 0 
						INSERT INTO @Result (Vardenhet, Datum, Belopp) VALUES (@Last_Vardenhet, @Last_Datum, @Best)
				end

			end

	-- Kopiera resultat fr&#229;n tabellvariabel till tabell
	INSERT INTO dbo.Ersattning2 (Vardenhet, Datum, Belopp)
	SELECT Vardenhet, Datum, Belopp
	FROM   @Result
END

go
CREATE PROCEDURE dbo.RunSolution
WITH EXECUTE AS OWNER
AS

set nocount on

declare @t1 datetime = getdate()

declare @Besok     dbo.BesokType
declare @Prislista dbo.PrislistaType

-- Copy all visits for Vardenhet, PatientID and Datum for Vardenhet, PatientID, Datum contains a T-visit

print &#39;*** Copy to #B ***&#39;
create table #B
(
	id int not null,
	Vardenhet char(5) not null,
	PatientID uniqueidentifier not null,
	Datum date,
	Taxekod char(3) not null
)

;with
tt as (
	select 
		Vardenhet, Datum = cast(DatumOchTid as date), PatientID
	from dbo.Besok
	where Taxekod like &#39;T%&#39;
	group by Vardenhet, cast(DatumOchTid as date), PatientID
)
INSERT INTO #B
	(id, PatientID, Taxekod, Datum, Vardenhet)
SELECT 
	id = row_number() over (order by b.Vardenhet, b.PatientID, b.DatumOchTid),
	b.PatientID, 
	b.Taxekod, 
	b.DatumOchTid, 
	b.Vardenhet
FROM dbo.Besok b with (tablock)
where exists(select 1 from tt t where t.Vardenhet = b.Vardenhet and t.Datum = cast(b.DatumOchTid as date) and t.PatientID = b.PatientID)
;

print &#39;*** Copy to @Besok ***&#39;
insert into @Besok
	(id, PatientID, Taxekod, Datum, Vardenhet)
select
	id, PatientID, Taxekod, Datum, Vardenhet
from #B with (tablock)

print &#39;*** Copy to @Prislista ***&#39;
INSERT INTO @Prislista
	(Taxekod, Pris)
SELECT
	Taxekod,
	Pris
FROM dbo.Prislista with (tablock)

-- Process data
print &#39;*** Process data ***&#39;
exec dbo.ProcessBesok @Besok, @Prislista

-- Copy result to ordinary temp table so the next step can use parallellism
print &#39;*** Copy to #E ***&#39;
select Vardenhet, Datum, Belopp
into #E
from dbo.Ersattning2

--truncate table Ersattning

-- Merge result from the procedure with result of plain SQL into final result table
print &#39;*** Merge result ***&#39;
;with
tt as (
	select 
		Vardenhet, Datum = cast(DatumOchTid as date), PatientID
	from dbo.Besok
	where Taxekod like &#39;T%&#39;
	group by Vardenhet, cast(DatumOchTid as date), PatientID
),
cte1 as (
	select 
		b1.Vardenhet, 
		b1.PatientID, 
		b1.Taxekod, 
		cast(b1.DatumOchTid as date) as Datum, 
		p.Pris
	from dbo.Besok b1
	join dbo.Prislista p on p.Taxekod = b1.Taxekod
	where not exists(select 1 from tt t where t.Vardenhet = b1.Vardenhet and t.Datum = cast(b1.DatumOchTid as date) and t.PatientID = b1.PatientID)
)
, cte2 as (
	select 
		*,
		A = case when Taxekod like &#39;A%&#39; then Pris else 0 end,
		B = case when Taxekod like &#39;B%&#39; then Pris else 0 end,
		K1 = case when Taxekod in (&#39;K01&#39;, &#39;K02&#39;) then Pris else 0 end,
		K2 = case when Taxekod in (&#39;K03&#39;, &#39;K04&#39;) then Pris else 0 end
	from cte1
)
, cte3 as (
	select
		Vardenhet,
		PatientID,
		Datum,
		A = max(A),
		B = sum(B),
		K = max(K1) + max(K2)
	from cte2
	group by Vardenhet, PatientID, Datum
)
, cte4 as (
	select
		*,
		Belopp = B + case when A >= K then A else K end
	from cte3
)
, cte5 as (
	select
		Vardenhet,
		Datum,
		Belopp = sum(Belopp)
	from cte4
	group by Vardenhet, Datum
	having sum(Belopp)<>0
)
,cte6 as (
	select Vardenhet, Datum, Belopp from cte5
	union all
	select Vardenhet, Datum, Belopp from #E
)
, cte7 as (
	select Vardenhet, Datum, Belopp = SUM(Belopp)
	from cte6
	group by Vardenhet, Datum
)
MERGE INTO dbo.Ersattning with (tablock) t 
USING cte7 s
	on s.Vardenhet = t.Vardenhet and s.Datum = t.Datum
WHEN matched and s.Belopp <> t.Belopp 
THEN
	UPDATE SET t.Belopp = s.Belopp
WHEN NOT MATCHED 
THEN
	INSERT (Vardenhet, Datum, Belopp)
	VALUES (s.Vardenhet, s.Datum, s.Belopp)
WHEN NOT MATCHED BY SOURCE 
THEN
	DELETE
option (maxdop 0)
;


declare @t2 datetime = getdate()

print &#39;*** Total time = &#39; + cast(datediff(ms, @t1, @t2) as varchar(10)) + &#39; ms ***&#39;
go

-- RunSolution.sql
--
-- Stefan Gustafsson, Acando

-- Prepare for rerun of solution
delete from dbo.Ersattning2

set statistics time on
exec dbo.RunSolution
set statistics time off
 


Uppgiften gick inte att lösa enbart med en effektiv set-orienterad select. Regeln om tremånadersbesök gjorde att man var tvungen att utföra beräkningen sekventiellt.

Hade man inte haft tillgång till SQL2014 så hade man varit tvungen att använda en cursor eller kanske en CLR-procedur. Nu kunde man i stället utnyttja en natively compiled stored procedure.

Min första lösning hade bara en natively compiled stored procedure. Själva proceduren gick mycket snabbt att köra, men det tog tid att kopiera all data från en tabell till en memory-optimized table som proceduren kan läsa från.

Nästa försök utnyttjar parallellism genom att processa alla dagar och patienter som inte involverar några tremånadersbesök med ren SQL. Denna SQL kan paralleliseras vilket utnyttjar alla CPUer på maskinen.

För att skicka in data till proceduren använde jag en memory-optimized table-valued parameter. Det går snabbare att skriva till en table-valued variabel än till en memory-optimized table.

Även resultatet från proceduren skrivs först till en table-variabel eftersom det gick snabbare än att skriva direkt till en memory-optimized table.

Skall bli intressant att se vad Mikael hittade på som gjorde hans lösning snabbare än min.

/SG
Mikael Eriksson
New Member
New Member
Posts: 12


--
09 Dec 2014 10:08 PM
Tack för en kul tävling och tack för möjligheten att utforska nya områden.

Jag byggde en äkta RBAR (row by agonizing row) lösning i en natively compiled stored procedures. De exekveras single threaded så för att få lite fart på exekveringen använde jag Service Broker för att kunna köra proceduren parallellt och jag partitionerade körningen på vårdenhet genom att generar ett meddelande per vårdenhet.

Ett problem med att använda SB för detta var att nya queue readers skapas av queue monitor som startar en ny reader om det finns meddelanden i kön som väntar på att bli processade. Denna queue monitor kickar igång vart femte sekund och startar då en ny reader, väntar 5 sekunder och startar en ny reader om det behövs osv. Det betyder att innan alla 8 queue readers i min lösning är uppe och kör så kommer det ta 40 sekunder. För att råda bot på detta så skapade jag en meddelandetyp som var till för att få igång alla queue readers redan i setup koden så när det var dags att göra körningen satt de där, alla 8 readers, snällt och väntade på att få jobba.

Det testdata jag använde var betydligt mer än som användes i tävlingen. Jag hade 32 miljoner rader fördelat på 200 enheter vilket processades på min dator med 16 queue readers på 14 sekunder. Så jag hade lite tur att lösningen även var bäst på endast 1 miljon rader och 10 enheter. Med en exekveringstid på 1.41 sekunder så är en hel del overhead för att skicka meddelanden med SB och tolka XML i meddelanden med xQuery samt en recursive CTE för att hitta unika vårdenheter och generera meddelanden.

Koden innehåller en del kommentarer som förklarar vad de olika delarna gör men jag svarar gärna på frågor om något behöver förtydligas.

Setup: http://pastebin.com/CNKRUmgC
Main: http://pastebin.com/YmhYA303


PS:

Thanks to Paul White for the query that uses a CTE instead of SELECT DISTINCT to build the unique list of vardenhet.

Go read his blog post "Performance Tuning the Whole Query Plan" to find out all about it:
http://sqlperformance.com/2014/10/t...whole-plan
Sergey
New Member
New Member
Posts: 16


--
09 Dec 2014 11:11 PM
Tack för en rolig och i min bedömning svåraste tävlingsuppgift hittills!



Grattis till Mikael och Stefan!



Jag lärde mig massor om inmemory på en månad men lyckades inte få det snabbt nog denna gång och får nu lära mig från dem bästa.




Min lösning går ut på att först identifiera alla T-besök som eventuellt kan debiteras och sedan med hjälp av rekursion ta reda på vilka T-besök som faktiskt ska debiteras. Därefter aggregera övriga besök enligt regler och lägga till kompensation från T-besök för att sedan slå ihop med Ersattning tabellen.





/*





SQLUGChallenge2014





Author    : Sergey Klimkevich


Company    : ThomasCook


Version    : Bidrag 3








*/


--  SETUP >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>



IF OBJECT_ID('dbo.Sergey5') IS NOT NULL

  DROP PROCEDURE dbo.Sergey5


GO


CREATE PROC dbo.Sergey5

AS

BEGIN

  SET NOCOUNT ON;





  CREATE TABLE #tmp (

    Vardenhet CHAR(5) COLLATE Finnish_Swedish_100_BIN2 NOT NULL,

    PatientID [UNIQUEIDENTIFIER] NOT NULL,

    Datum DATE NOT NULL,

    T1 INT NOT NULL,

    T2 INT NOT NULL,

    RowNum SMALLINT NOT NULL,

    INDEX CIX_tmp CLUSTERED

    (PatientID, Vardenhet)

  )







  ;

  WITH C1 AS

  (SELECT

      Vardenhet,

      PatientID,

      CONVERT(DATE, DatumOchTid)  AS Datum,

      b.Taxekod,

      p.Pris

    FROM dbo.Besok b WITH (TABLOCK)

    JOIN dbo.Prislista AS p WITH (TABLOCK) ON b.Taxekod = p.Taxekod

  ),

  C2 AS

  (SELECT

      Vardenhet,

      PatientID,

      Datum,

      MAX(IIF(Taxekod IN ('A01', 'A02'), Pris, 0))  AS [A],

      MAX(IIF(Taxekod IN ('K01', 'K02'), Pris, 0))  AS [K1],

      MAX(IIF(Taxekod IN ('K03', 'K04'), Pris, 0))  AS [K2],

      MAX(IIF(Taxekod IN ('T01'), Pris, 0))      AS [T1],

      MAX(IIF(Taxekod IN ('T02'), Pris, 0))      AS [T2]



    FROM C1

    GROUP BY  Vardenhet,

          PatientID,

          Datum

  ),

  C3 AS

  (SELECT

      Vardenhet,

      PatientID,

      Datum,

      T1 - AK AS T1,

      T2 - AK AS T2

    FROM C2 p

    CROSS APPLY

    (SELECT

        IIF(A > K1 + K2, A, K1 + K2) AS AK

    ) ca1

    WHERE T1 > AK OR T2 > AK

  )

  INSERT #tmp (Vardenhet, PatientID, Datum, T1, T2,RowNum)

    SELECT

      Vardenhet,

      PatientID,

      Datum,

      T1,

      T2,

      ROW_NUMBER() OVER (PARTITION BY PatientID, Vardenhet ORDER BY Datum) AS RowNum

    FROM C3

  OPTION (MAXDOP 0);







  ;WITH C5 AS

  (SELECT

      t.Vardenhet,

      t.PatientID,

      t.Datum,

      IIF(T1>T2,t.Datum,NULL) AS T1_deb_datum,

      IIF(T2>T1,t.Datum,NULL) AS T2_deb_datum,

      t.RowNum,

      IIF(T1>T2,T1,T2)AS T_deb

    FROM #tmp AS t

    WHERE RowNum = 1



    UNION ALL



    SELECT

      t.Vardenhet,

      t.PatientID,

      t.Datum,

      IIF(ca1.T_win = 1, t.Datum, c.T1_deb_datum) AS T1_deb_datum,

      IIF(ca1.T_win = 2, t.Datum, c.T2_deb_datum) AS T2_deb_datum,

      t.RowNum,

      IIF(ca1.T_win = 1, T1, IIF(ca1.T_win = 2, T2,0)) AS T_deb

    FROM #tmp t

    JOIN C5 c

      ON  t.RowNum    = c.RowNum + 1

      AND t.Vardenhet = c.Vardenhet

      AND t.PatientID = c.PatientID

    CROSS APPLY (

      SELECT

        CONVERT(BIT, IIF(T1 > 0 AND ISNULL(DATEDIFF(DAY,c.T1_deb_datum, t.Datum),91) > 90, 1, 0)) AS T1_kval,

        CONVERT(BIT, IIF(T2 > 0 AND ISNULL(DATEDIFF(DAY,c.T2_deb_datum, t.Datum),91) > 90, 1, 0)) AS T2_kval

    ) ca0

    CROSS APPLY (

      SELECT

        CONVERT(TINYINT, CASE

                  WHEN T1_kval = 1 AND T2_kval = 1 THEN IIF(T1 > T2, 1, 2)

                  WHEN T1_kval = 1 THEN 1

                  WHEN T2_kval = 1 THEN 2

          ELSE 0

                END)

        AS T_win

    ) ca1



  ), C6 AS (

    SELECT   Vardenhet,

        Datum,

        SUM(T_deb) AS T_deb

        FROM C5 c

    WHERE c.T_deb > 0

    GROUP BY c.Vardenhet,c.Datum



  ),

  C1 AS

  (SELECT

      Vardenhet,

      PatientID,

      CONVERT(DATE, DatumOchTid)  AS Datum,

      b.Taxekod,

      p.Pris

    FROM dbo.Besok b WITH (TABLOCK)

    JOIN dbo.Prislista AS p WITH (TABLOCK) ON b.Taxekod = p.Taxekod

  ),

  C2 AS

  (SELECT

      Vardenhet,

      PatientID,

      Datum,

      MAX(IIF(Taxekod IN ('A01', 'A02'), Pris, 0))  AS [A],

      SUM(IIF(Taxekod IN ('B01', 'B02'), Pris, 0))  AS [B],

      MAX(IIF(Taxekod IN ('K01', 'K02'), Pris, 0))  AS [K1],

      MAX(IIF(Taxekod IN ('K03', 'K04'), Pris, 0))  AS [K2]

    FROM C1

    GROUP BY  Vardenhet,

          PatientID,

          Datum

  ),

  C3 AS

  (SELECT

      Vardenhet,

      PatientID,

      Datum,

      AK,

      B

    FROM C2 p

    CROSS APPLY

    (SELECT

        IIF(A > K1 + K2, A, K1 + K2) AS AK

    ) ca1

  ),C4 AS (

    SELECT Vardenhet,Datum,SUM(AK + B) AS AKB FROM C3

    GROUP BY Vardenhet,Datum

  ),GC1 AS (

    SELECT

      r.Vardenhet,

      r.Datum,

      r.AKB + ISNULL(f.T_deb,0) AS Ersattning

    FROM C4 r

    LEFT JOIN C6 f ON r.Vardenhet = f.Vardenhet AND r.Datum = f.Datum

  )





  MERGE dbo.Ersattning WITH (TABLOCK) AS T

  USING

  (SELECT

      Vardenhet,

      Datum,

      Ersattning

    FROM GC1

    WHERE Ersattning > 0

  ) AS s

  ON s.Vardenhet = T.Vardenhet

  AND s.Datum = T.Datum

  WHEN MATCHED AND (s.Ersattning <> T.Belopp)

  THEN UPDATE

  SET Belopp = s.Ersattning

  WHEN NOT MATCHED BY SOURCE THEN DELETE

  WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (s.Vardenhet, s.Datum, s.Ersattning)



  OPTION (MAXRECURSION 0, MAXDOP 0);



ENDGO

--  SETUP <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<



-- EXECUTE SOLUTION >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>


EXEC dbo.Sergey5





-- EXECUTE SOLUTION <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<








//Sergey


stefan gustafsson
New Member
New Member
Posts: 29


--
10 Dec 2014 09:21 AM


Jag byggde en äkta RBAR (row by agonizing row) lösning i en natively compiled stored procedures. De exekveras single threaded så för att få lite fart på exekveringen använde jag Service Broker för att kunna köra proceduren parallellt och jag partitionerade körningen på vårdenhet genom att generar ett meddelande per vårdenhet.



Imponerande kreativ lösning!

Jag funderade både länge och hårt på hur man skulle kunna köra en native procedure parallellt men lyckades inte komma på något bra.

Att använda service broker för detta är genialiskt!

Gratulerar till en välförtjänt seger!

/SG
Mikael Eriksson
New Member
New Member
Posts: 12


--
10 Dec 2014 09:24 AM

Posted By stefan gustafsson on 2014-12-10 09:21


Gratulerar till en välförtjänt seger!



Tackar !!
You are not authorized to post a reply.
Page 4 of 4 << < 1234