Soci (Soczó Zsolt) szakmai blogja

Feladat: az alábbi lekérdezés nagyon lassan fut le, nálam a költsége SQL Server 2014 alatt 1145 sec. Hozzá kellene írni a selecthez valamit, amitől a költsége leesik sokkal kisebbre.

USE TempDB
GO

CREATE TABLE Table1 (A CHAR(5) NOT NULL)
CREATE TABLE Table2 (B VARCHAR(1000) NOT NULL)
GO

INSERT Table1
SELECT LEFT(AddressLine1, 5) AS A
FROM AdventureWorks2014.Person.Address

INSERT Table2
SELECT AddressLine1 AS B
FROM AdventureWorks2014.Person.Address
GO

SELECT *
FROM Table1
INNER JOIN Table2 ON
    Table2.B LIKE Table1.A + '%'

A megfejtéseket szokás szerint kommentben várom, amelyeket 4 nap múlva engedek ki.

A Test Driven Development tanfolyam következő felvonása április 20-án lesz, jövő hét szerdáig még 25% kedvezménnyel lehet jelentkezni!

Kérdés.

Az IO műveleteket csak SQL Server 2014-től lehet hathatósan kordában tartani, a Resource Governor képes tetszőleges számú IO művelet/sec-re bekorlátozni a zabolátlan processzt.

Korábbi verziókban értünk el sikereket azzal, hogy a MAXDOP-ot vettük kisebbre egy lekérdezéshez. Igaz, ez nem az IO-t fogja vissza, de egy szálon csak nem tud annyi IO-t kihasítani magának egy folyamat, mint többön. Ez csak a ha ló nincs jó a szamár is megoldás.

Valójában persze a legjobb megoldás megpróbálni optimalizálni a lékérdezést/módosítást, nem futtatni olyan sűrűn (cache-elés), éjszakára időzíteni ha nem sürgős, esetleg Always On-os vagy sima replikációs másodpéldányon futtatni.

Amint ezt beépítik az EF-be, attól a pillanattól kezdve az EF végre rendes versenytársa lesz az NHibernate-nek.

Ráadásul, a doski azt sejteti, hogy menni fog identity oszlopokra is, az nem megy nhibbel.

Izgalmas fejlemények, meg kéne venni az egész motyót az msnek, de gyorsan.

Mindig imádom, ha valami geekséget be tudok vetni a gyakorlatban. SQL Server 2012-ben jött be az az újdonság, hogy a FORCESEEK hintnek meg lehet adni egy index oszlopait is.

A tanfolyamok kedvéért lehet találni példákat, aminek segít egy hint, de végre találtam valamit, ami élőben is demonstrálja a dolgot.

Az alábbi lekérdezés 1 perces tőzsdei adatokban keres adathiányt. Az AllTH összeszedi minden napra a nyitvatartási időket.

;with AllTH as
(
	select 
	D, 
	dateadd(DAY, 1, D) D1,
	cast(DATEADD(HOUR, DATEPART(HOUR, OpeningTime), DATEADD(MINUTE, DATEPART(MINUTE, OpeningTime), cast(D as datetime2))) as datetime2) ExpectedOpeningTimeAsDate, 
	cast(DATEADD(HOUR, DATEPART(HOUR, ClosingTime), DATEADD(MINUTE, DATEPART(MINUTE, ClosingTime), cast(D as datetime2))) as datetime2) ExpectedClosingTimeAsDate, 
	OpeningTime, ClosingTime from TradingHoursFlat
	where TickerId = @thTickerId
	and D >= (select min(TradeTime) from Tick where TickerID = @tickerId and tradetime > dateadd(day, -180, getdate()))
	and D < dateadd(day, -1, GETDATE())
),
FilteredBars as
(
	select cast(TradeTime as datetime) TradeTime,
	t.D, t.OpeningTime, t.ClosingTime
	from AllTH t
	join Tick b
	with(forceseek (IX_Natural_Key (TickerId, TradeTime)))
	--on t.D = cast(TradeTime as date) 
	on TradeTime between t.D and t.D1
	--on TradeTime between t.D and t.D+1
	where b.TickerID = @tickerId
	--and cast(TradeTime as time) between t.OpeningTime and t.ClosingTime
	and tradetime between t.ExpectedOpeningTimeAsDate and t.ExpectedClosingTimeAsDate
),
T as
(
	select a.D, min(TradeTime) ActualOpeningTime, max(TradeTime) ActualClosingTime from FilteredBars b 
	right join AllTH a
	on a.D = cast(TradeTime as date)
	group by a.D
), U as
(
	select a.D, ExpectedOpeningTimeAsDate, ActualOpeningTime, ExpectedClosingTimeAsDate, ActualClosingTime,
	DATEDIFF(MINUTE, ExpectedOpeningTimeAsDate, ActualOpeningTime) OpeningGap,
	DATEDIFF(MINUTE, ActualClosingTime, ExpectedClosingTimeAsDate) ClosingGap
	from T
	right join AllTH a
	on a.D = cast(t.ActualOpeningTime as date)
)
,V as
(
	select * from U
	where OpeningGap > @tolerance or ClosingGap > @tolerance
	or ActualOpeningTime is null or ActualClosingTime is null
)
select 
ROW_NUMBER() over(order by D) Id,
ExpectedOpeningTimeAsDate ExpectedOpeningTime,
ActualOpeningTime,
ExpectedClosingTimeAsDate ExpectedClosingTime,
ActualClosingTime,
case when ActualOpeningTime is null then 'MissingDay' else 'MissingIntradayData' end GapKind 
from V
order by D

A hivatkozott Tick táblában ebben a pillanatban ennyi sor van: 61646572157. Nem írtam el, ez 61 milliárd sor!

Itt van az SQL 2012-es hint barátunk:
join Tick b with(forceseek (IX_Natural_Key (TickerId, TradeTime)))

Furcsa módon hint nélkül a becsült plan 60x olcsóbb, de mégis, a hinttel rákényszerített (számomra, aki ismeri az adatokat logikus) plan sokkal gyorsabban fut le.
Ha nem írom ki az oszlopokat, vagy csak foreceseek vagy forcessek + index név nem veszi rá, hogy az én tervemet valósítsa meg.

Ezzel nem azt sugallom, hogy hinteljetek mint az állat, csak azt, hogy egyes határesetekben jól jöhet, ha tudunk róla.

Egy tipp még. Vigyázni kell, hogy ne keverjük a datetime2 különböző hosszúságú változatait, mert ezeket implicit konvertálni fogja a szerver, megint elesve a seekektől.

Kérdés: meg tudom-e nézni egy hosszan futó SQL DML lekérdezésnél, hogy a mögötte futó operátorok (join, index seek, stb.) hány százaléknál járnak?

SQL Server 2014-től igen!

Nézzük a következő (buta) lekérdezést:

SET STATISTICS PROFILE ON;
GO
--Next, run your query in this session
select * from [dbo].[Nums] n1 cross join dbo.Nums n2;

Ez egy lassú, sok soros cross join, csak demó célra. A vizsgálandó lekérdezést olyan sessionben kell futtatni, amiben a STATISTICS PROFILE be van kapcsolva.

Így néz ki a végrehajtási terve:

A terv fontos a következőkhöz, azért raktam ide. És most jön a lényeg: sys.dm_exec_query_profiles. Ő mutatja meg, belül mi zajlik:

SELECT  
   node_id,physical_operator_name, SUM(row_count) row_count, SUM(estimate_row_count) AS estimate_row_count, 
   CAST(SUM(row_count)*100 AS float) / SUM(estimate_row_count) PercentComplete
FROM sys.dm_exec_query_profiles 
--WHERE session_id=54
GROUP BY node_id,physical_operator_name
ORDER BY node_id;

A végrehajtás kezdetén így néz ki a kimenete (ha valaki tud tippet adni, hogy lehet ezt SyntaxHighlighterrel jól formázni megköszönöm):

node_id	physical_operator_name	row_count	estimate_row_count	PercentComplete
0	Parallelism		1320422		4294967295		0.030743470422631
1	Nested Loops		1323284		4294967296		0.0308101065456867
2	Clustered Index Scan	15		100000			0.015
3	Table Spool		1323284		1410065408		0.0938455757082157
4	Clustered Index Scan	400000		400000			100

Egy perccel később:

node_id	physical_operator_name	row_count	estimate_row_count	PercentComplete
0	Parallelism	15917761	4294967295	0.370614254002137
1	Nested Loops	15920504	4294967296	0.370678119361401
2	Clustered Index Scan	161	100000	0.161
3	Table Spool	15920504	1410065408	1.12906138322911
4	Clustered Index Scan	400000	400000	100

Mit jelen ez? A terv alapján van két clustered index scanünk. Az egyik felolvasta a táblában található 400000 sort, azzal ő végzett is. Aztán a cilusos join elkezdi hozzávenni a tábla sorait, minden sorhoz 400000-et. Az egész eredménye belekerül a Table Spoolba, majd csurog ki a Parallelism operátoron keresztül (ami Gather Stream műveletet hajt végre, azaz összegyűjti a több szál által összerakott sorokat egy streambe, mivel párhuzamos a terv).

Lássuk be, marha érdekes látni, hogy áll belül egy nagyobb DML művelet, nem?

Van egy táblánk, amiben számokat tárolunk. A számok nem teljesen folytonosak, az összefüggő részeket kell azonosítani.

use tempdb;

IF OBJECT_ID('dbo.Tabla', 'U') IS NOT NULL DROP TABLE dbo.Tabla;
GO
CREATE TABLE dbo.Tabla
(
	szam INT NOT NULL CONSTRAINT PK_Tabla PRIMARY KEY
);
INSERT INTO dbo.Tabla(szam)
VALUES(12),(13),(111),(112),(113),(237),(238),(340),(350),(351);
GO

select * from Tabla;

-- Ezt szeretnénk kinyerni, az összefüggő tartományokat
--tartomanyeleje	tartomanyvege
--12				13
--111				113
--237				238
--340				340
--350				351

A megfejtéseket szokás szerint kommentben várom, amelyeket 2 nap múlva engedek ki.

SQL Serveren dolgozva van egy agresszív lekérdezés, amely szétzúzza a diszket, így a többi lekérdezés nem kap elég időt, kiéhezteti őket. Mit tudok tenni?

Meg tudom-e nézni egy hosszan futó SQL DML lekérdezésnél, hogy a mögötte futó operátorok (join, index seek, stb.) hány százaléknál járnak?

Ebben a feladatban meg kellett határozni azokat a sorokat, amelyek összege kiad egy sort egy másik táblában. Mivel nem lehet tudni, hány sort kell szummázni, ezért kombinációkat kellett képezni az elemekből.
A feladat nehezebb része tehát, hogyan kell SQL-ből kombinációkat képezni?

;WITH Combinations AS
(
	SELECT  [Level]=1,
			CombinationId = CONVERT(VARCHAR(MAX),id)+',',
			Szamlaszam,
			TotalPrice = CONVERT(DECIMAL(10,2), Ar),
			LastId = Id
	FROM  Szamlatetel

	UNION ALL

	SELECT  [Level]=[Level]+1,
			CombinationId = CombinationId + CONVERT(VARCHAR(3),id) + ',',
			c.Szamlaszam,
			TotalPrice = CONVERT(DECIMAL(10,2),TotalPrice + Ar),
			LastId = Id
	FROM  Combinations c  
	INNER JOIN Szamlatetel i 
		    ON  i.Szamlaszam = c.Szamlaszam AND i.ID > LastId 
)
select * from Combinations;

Ennek kimenete:

1	1,	Szamla1	20.00	1
1	2,	Szamla1	111.00	2
1	3,	Szamla1	250.00	3
1	4,	Szamla1	20.00	4
1	5,	Szamla1	15.00	5
1	6,	Szamla1	189.00	6
1	7,	Szamla1	100.00	7
1	8,	Szamla1	95.00	8
1	9,	Szamla2	1000.00	9
1	10,	Szamla2	3000.00	10
2	9,10,	Szamla2	4000.00	10
2	7,8,	Szamla1	195.00	8
2	6,7,	Szamla1	289.00	7
2	6,8,	Szamla1	284.00	8
3	6,7,8,	Szamla1	384.00	8
2	5,6,	Szamla1	204.00	6
2	5,7,	Szamla1	115.00	7
2	5,8,	Szamla1	110.00	8
3	5,7,8,	Szamla1	210.00	8
3	5,6,7,	Szamla1	304.00	7
3	5,6,8,	Szamla1	299.00	8
4	5,6,7,8,	Szamla1	399.00	8
2	4,5,	Szamla1	35.00	5
2	4,6,	Szamla1	209.00	6
2	4,7,	Szamla1	120.00	7
2	4,8,	Szamla1	115.00	8
3	4,7,8,	Szamla1	215.00	8
3	4,6,7,	Szamla1	309.00	7
3	4,6,8,	Szamla1	304.00	8
4	4,6,7,8,	Szamla1	404.00	8
3	4,5,6,	Szamla1	224.00	6
3	4,5,7,	Szamla1	135.00	7
3	4,5,8,	Szamla1	130.00	8
4	4,5,7,8,	Szamla1	230.00	8
4	4,5,6,7,	Szamla1	324.00	7
4	4,5,6,8,	Szamla1	319.00	8
5	4,5,6,7,8,	Szamla1	419.00	8
2	3,4,	Szamla1	270.00	4
2	3,5,	Szamla1	265.00	5
2	3,6,	Szamla1	439.00	6
2	3,7,	Szamla1	350.00	7
2	3,8,	Szamla1	345.00	8
3	3,7,8,	Szamla1	445.00	8
3	3,6,7,	Szamla1	539.00	7
3	3,6,8,	Szamla1	534.00	8
4	3,6,7,8,	Szamla1	634.00	8
3	3,5,6,	Szamla1	454.00	6
3	3,5,7,	Szamla1	365.00	7
3	3,5,8,	Szamla1	360.00	8
4	3,5,7,8,	Szamla1	460.00	8
4	3,5,6,7,	Szamla1	554.00	7
4	3,5,6,8,	Szamla1	549.00	8
5	3,5,6,7,8,	Szamla1	649.00	8
3	3,4,5,	Szamla1	285.00	5
3	3,4,6,	Szamla1	459.00	6
3	3,4,7,	Szamla1	370.00	7
3	3,4,8,	Szamla1	365.00	8
4	3,4,7,8,	Szamla1	465.00	8
4	3,4,6,7,	Szamla1	559.00	7
4	3,4,6,8,	Szamla1	554.00	8
5	3,4,6,7,8,	Szamla1	654.00	8
4	3,4,5,6,	Szamla1	474.00	6
4	3,4,5,7,	Szamla1	385.00	7
4	3,4,5,8,	Szamla1	380.00	8
5	3,4,5,7,8,	Szamla1	480.00	8
5	3,4,5,6,7,	Szamla1	574.00	7
5	3,4,5,6,8,	Szamla1	569.00	8
6	3,4,5,6,7,8,	Szamla1	669.00	8
2	2,3,	Szamla1	361.00	3
2	2,4,	Szamla1	131.00	4
2	2,5,	Szamla1	126.00	5
2	2,6,	Szamla1	300.00	6
2	2,7,	Szamla1	211.00	7
2	2,8,	Szamla1	206.00	8
3	2,7,8,	Szamla1	306.00	8
3	2,6,7,	Szamla1	400.00	7
3	2,6,8,	Szamla1	395.00	8
4	2,6,7,8,	Szamla1	495.00	8
3	2,5,6,	Szamla1	315.00	6
3	2,5,7,	Szamla1	226.00	7
3	2,5,8,	Szamla1	221.00	8
4	2,5,7,8,	Szamla1	321.00	8
4	2,5,6,7,	Szamla1	415.00	7
4	2,5,6,8,	Szamla1	410.00	8
5	2,5,6,7,8,	Szamla1	510.00	8
3	2,4,5,	Szamla1	146.00	5
3	2,4,6,	Szamla1	320.00	6
3	2,4,7,	Szamla1	231.00	7
3	2,4,8,	Szamla1	226.00	8
4	2,4,7,8,	Szamla1	326.00	8
4	2,4,6,7,	Szamla1	420.00	7
4	2,4,6,8,	Szamla1	415.00	8
5	2,4,6,7,8,	Szamla1	515.00	8
4	2,4,5,6,	Szamla1	335.00	6
4	2,4,5,7,	Szamla1	246.00	7
4	2,4,5,8,	Szamla1	241.00	8
5	2,4,5,7,8,	Szamla1	341.00	8
5	2,4,5,6,7,	Szamla1	435.00	7
5	2,4,5,6,8,	Szamla1	430.00	8
6	2,4,5,6,7,8,	Szamla1	530.00	8
3	2,3,4,	Szamla1	381.00	4
3	2,3,5,	Szamla1	376.00	5
3	2,3,6,	Szamla1	550.00	6
3	2,3,7,	Szamla1	461.00	7
3	2,3,8,	Szamla1	456.00	8
4	2,3,7,8,	Szamla1	556.00	8
4	2,3,6,7,	Szamla1	650.00	7
4	2,3,6,8,	Szamla1	645.00	8
5	2,3,6,7,8,	Szamla1	745.00	8
4	2,3,5,6,	Szamla1	565.00	6
4	2,3,5,7,	Szamla1	476.00	7
4	2,3,5,8,	Szamla1	471.00	8
5	2,3,5,7,8,	Szamla1	571.00	8
5	2,3,5,6,7,	Szamla1	665.00	7
5	2,3,5,6,8,	Szamla1	660.00	8
6	2,3,5,6,7,8,	Szamla1	760.00	8
4	2,3,4,5,	Szamla1	396.00	5
4	2,3,4,6,	Szamla1	570.00	6
4	2,3,4,7,	Szamla1	481.00	7
4	2,3,4,8,	Szamla1	476.00	8
5	2,3,4,7,8,	Szamla1	576.00	8
5	2,3,4,6,7,	Szamla1	670.00	7
5	2,3,4,6,8,	Szamla1	665.00	8
6	2,3,4,6,7,8,	Szamla1	765.00	8
5	2,3,4,5,6,	Szamla1	585.00	6
5	2,3,4,5,7,	Szamla1	496.00	7
5	2,3,4,5,8,	Szamla1	491.00	8
6	2,3,4,5,7,8,	Szamla1	591.00	8
6	2,3,4,5,6,7,	Szamla1	685.00	7
6	2,3,4,5,6,8,	Szamla1	680.00	8
7	2,3,4,5,6,7,8,	Szamla1	780.00	8
2	1,2,	Szamla1	131.00	2
2	1,3,	Szamla1	270.00	3
2	1,4,	Szamla1	40.00	4
2	1,5,	Szamla1	35.00	5
2	1,6,	Szamla1	209.00	6
2	1,7,	Szamla1	120.00	7
2	1,8,	Szamla1	115.00	8
3	1,7,8,	Szamla1	215.00	8
3	1,6,7,	Szamla1	309.00	7
3	1,6,8,	Szamla1	304.00	8
4	1,6,7,8,	Szamla1	404.00	8
3	1,5,6,	Szamla1	224.00	6
3	1,5,7,	Szamla1	135.00	7
3	1,5,8,	Szamla1	130.00	8
4	1,5,7,8,	Szamla1	230.00	8
4	1,5,6,7,	Szamla1	324.00	7
4	1,5,6,8,	Szamla1	319.00	8
5	1,5,6,7,8,	Szamla1	419.00	8
3	1,4,5,	Szamla1	55.00	5
3	1,4,6,	Szamla1	229.00	6
3	1,4,7,	Szamla1	140.00	7
3	1,4,8,	Szamla1	135.00	8
4	1,4,7,8,	Szamla1	235.00	8
4	1,4,6,7,	Szamla1	329.00	7
4	1,4,6,8,	Szamla1	324.00	8
5	1,4,6,7,8,	Szamla1	424.00	8
4	1,4,5,6,	Szamla1	244.00	6
4	1,4,5,7,	Szamla1	155.00	7
4	1,4,5,8,	Szamla1	150.00	8
5	1,4,5,7,8,	Szamla1	250.00	8
5	1,4,5,6,7,	Szamla1	344.00	7
5	1,4,5,6,8,	Szamla1	339.00	8
6	1,4,5,6,7,8,	Szamla1	439.00	8
3	1,3,4,	Szamla1	290.00	4
3	1,3,5,	Szamla1	285.00	5
3	1,3,6,	Szamla1	459.00	6
3	1,3,7,	Szamla1	370.00	7
3	1,3,8,	Szamla1	365.00	8
4	1,3,7,8,	Szamla1	465.00	8
4	1,3,6,7,	Szamla1	559.00	7
4	1,3,6,8,	Szamla1	554.00	8
5	1,3,6,7,8,	Szamla1	654.00	8
4	1,3,5,6,	Szamla1	474.00	6
4	1,3,5,7,	Szamla1	385.00	7
4	1,3,5,8,	Szamla1	380.00	8
5	1,3,5,7,8,	Szamla1	480.00	8
5	1,3,5,6,7,	Szamla1	574.00	7
5	1,3,5,6,8,	Szamla1	569.00	8
6	1,3,5,6,7,8,	Szamla1	669.00	8
4	1,3,4,5,	Szamla1	305.00	5
4	1,3,4,6,	Szamla1	479.00	6
4	1,3,4,7,	Szamla1	390.00	7
4	1,3,4,8,	Szamla1	385.00	8
5	1,3,4,7,8,	Szamla1	485.00	8
5	1,3,4,6,7,	Szamla1	579.00	7
5	1,3,4,6,8,	Szamla1	574.00	8
6	1,3,4,6,7,8,	Szamla1	674.00	8
5	1,3,4,5,6,	Szamla1	494.00	6
5	1,3,4,5,7,	Szamla1	405.00	7
5	1,3,4,5,8,	Szamla1	400.00	8
6	1,3,4,5,7,8,	Szamla1	500.00	8
6	1,3,4,5,6,7,	Szamla1	594.00	7
6	1,3,4,5,6,8,	Szamla1	589.00	8
7	1,3,4,5,6,7,8,	Szamla1	689.00	8
3	1,2,3,	Szamla1	381.00	3
3	1,2,4,	Szamla1	151.00	4
3	1,2,5,	Szamla1	146.00	5
3	1,2,6,	Szamla1	320.00	6
3	1,2,7,	Szamla1	231.00	7
3	1,2,8,	Szamla1	226.00	8
4	1,2,7,8,	Szamla1	326.00	8
4	1,2,6,7,	Szamla1	420.00	7
4	1,2,6,8,	Szamla1	415.00	8
5	1,2,6,7,8,	Szamla1	515.00	8
4	1,2,5,6,	Szamla1	335.00	6
4	1,2,5,7,	Szamla1	246.00	7
4	1,2,5,8,	Szamla1	241.00	8
5	1,2,5,7,8,	Szamla1	341.00	8
5	1,2,5,6,7,	Szamla1	435.00	7
5	1,2,5,6,8,	Szamla1	430.00	8
6	1,2,5,6,7,8,	Szamla1	530.00	8
4	1,2,4,5,	Szamla1	166.00	5
4	1,2,4,6,	Szamla1	340.00	6
4	1,2,4,7,	Szamla1	251.00	7
4	1,2,4,8,	Szamla1	246.00	8
5	1,2,4,7,8,	Szamla1	346.00	8
5	1,2,4,6,7,	Szamla1	440.00	7
5	1,2,4,6,8,	Szamla1	435.00	8
6	1,2,4,6,7,8,	Szamla1	535.00	8
5	1,2,4,5,6,	Szamla1	355.00	6
5	1,2,4,5,7,	Szamla1	266.00	7
5	1,2,4,5,8,	Szamla1	261.00	8
6	1,2,4,5,7,8,	Szamla1	361.00	8
6	1,2,4,5,6,7,	Szamla1	455.00	7
6	1,2,4,5,6,8,	Szamla1	450.00	8
7	1,2,4,5,6,7,8,	Szamla1	550.00	8
4	1,2,3,4,	Szamla1	401.00	4
4	1,2,3,5,	Szamla1	396.00	5
4	1,2,3,6,	Szamla1	570.00	6
4	1,2,3,7,	Szamla1	481.00	7
4	1,2,3,8,	Szamla1	476.00	8
5	1,2,3,7,8,	Szamla1	576.00	8
5	1,2,3,6,7,	Szamla1	670.00	7
5	1,2,3,6,8,	Szamla1	665.00	8
6	1,2,3,6,7,8,	Szamla1	765.00	8
5	1,2,3,5,6,	Szamla1	585.00	6
5	1,2,3,5,7,	Szamla1	496.00	7
5	1,2,3,5,8,	Szamla1	491.00	8
6	1,2,3,5,7,8,	Szamla1	591.00	8
6	1,2,3,5,6,7,	Szamla1	685.00	7
6	1,2,3,5,6,8,	Szamla1	680.00	8
7	1,2,3,5,6,7,8,	Szamla1	780.00	8
5	1,2,3,4,5,	Szamla1	416.00	5
5	1,2,3,4,6,	Szamla1	590.00	6
5	1,2,3,4,7,	Szamla1	501.00	7
5	1,2,3,4,8,	Szamla1	496.00	8
6	1,2,3,4,7,8,	Szamla1	596.00	8
6	1,2,3,4,6,7,	Szamla1	690.00	7
6	1,2,3,4,6,8,	Szamla1	685.00	8
7	1,2,3,4,6,7,8,	Szamla1	785.00	8
6	1,2,3,4,5,6,	Szamla1	605.00	6
6	1,2,3,4,5,7,	Szamla1	516.00	7
6	1,2,3,4,5,8,	Szamla1	511.00	8
7	1,2,3,4,5,7,8,	Szamla1	611.00	8
7	1,2,3,4,5,6,7,	Szamla1	705.00	7
7	1,2,3,4,5,6,8,	Szamla1	700.00	8
8	1,2,3,4,5,6,7,8,	Szamla1	800.00	8

Mivel minden sor egy kombinációt kell leírjon, kénytelenek vagyunk valahogy tömöríteni sok értéket egy oszlopba, ezért ez a varchar(max)-os pakolás.
A második rész már egyszerűbb, a kombinációk mindegyikére kiszámolt összeget kell összeveti a fej táblával:

;WITH Combinations AS
(
	SELECT  [Level]=1,
			CombinationId = CONVERT(VARCHAR(MAX),id)+',',
			Szamlaszam,
			TotalPrice = CONVERT(DECIMAL(10,2), Ar),
			LastId = Id
	FROM  Szamlatetel

	UNION ALL

	SELECT  [Level]=[Level]+1,
			CombinationId = CombinationId + CONVERT(VARCHAR(3),id) + ',',
			c.Szamlaszam,
			TotalPrice = CONVERT(DECIMAL(10,2),TotalPrice + Ar),
			LastId = Id
	FROM  Combinations c  
	INNER JOIN Szamlatetel i 
		    ON  i.Szamlaszam = c.Szamlaszam AND i.ID > LastId 
)
SELECT i.id, i.Szamlaszam, i.Ar, s.Ado 
FROM Combinations c
INNER JOIN Szamlafej s
	    ON s.OsszAr = c.TotalPrice
INNER JOIN Szamlatetel i 
	    ON CombinationId LIKE '%' + CONVERT(VARCHAR(3),i.id) + ',%'
ORDER BY s.Ado, s.Szamlaszam, s.ID;

Két megoldás is érkezett, mindkettő jó, gratulálok a megfejtőknek, ez tényleg nehéz feladat volt.

Az egyik megoldás hasonlít az enyémre:

;WITH CTE as
( 
	SELECT OsszAr, Ado, t.ID, Ar, cast(Ar as decimal(18,2)) as CSum,
	right('0' + CAST(t.Id as Varchar(max)),2) as path
	from Szamlatetel t inner join Szamlafej f on t.Szamlaszam=f.Szamlaszam

	UNION all

	SELECT OsszAr,
	Ado,
	Szamlatetel.ID,
	Szamlatetel.Ar,
	cast(Szamlatetel.Ar+CTE.CSum as decimal(18,2)) as CSum,
	CTE.path+','+ right('0' + CAST(Szamlatetel.Id as Varchar(max)),2) as path
	from Szamlatetel
	JOIN CTE on Szamlatetel.Ar+CTE.CSum<=OsszAr and CTE.ID<Szamlatetel.ID
)
select Szamlatetel.ID, Szamlaszam, Szamlatetel.Ar, Ado
from Szamlatetel, CTE
where CTE.path like '%' + right('0' + CAST(Szamlatetel.ID as Varchar(max)),2) + '%' and CTE.CSum=OsszAr
order by Ado, Szamlaszam, Szamlatetel.ID

A másik megoldás is hasonló, de ebben a kombinációkat a szerző tábla típusú változóban tárolja átmenetileg:

declare @Osszeg decimal(18,2), @SzamlaTetelId int

declare @Kombinacio table
(
	Id int identity(1,1) not null primary key,
	ElozoId int null,
	OsszAr decimal(18,2) not null,
	SzamlaTetelId int not null
)

set @SzamlaTetelId = 0

while 1 = 1
begin
	select top 1 @Osszeg = T.Ar, @SzamlaTetelId = T.ID
	from
	Szamlatetel T
	where
	T.ID > @SzamlaTetelId order by T.ID

	if @@rowcount = 0
	break

	insert into @Kombinacio(ElozoId, OsszAr, SzamlaTetelId)
	select S.Id, S.OsszAr + @Osszeg, @SzamlaTetelId
	from
	(select OsszAr, Id from @Kombinacio union select 0, null) S
	where
	S.OsszAr + @Osszeg not in (select OsszAr from @Kombinacio)
end

;with Elozmeny as
(
	select
	K.Id, K.ElozoId, K.SzamlaTetelId, K.OsszAr
	from
	@Kombinacio K
	inner join Szamlafej F on F.OsszAr = K.OsszAr

	union all

	select
	K.Id, K.ElozoId, K.SzamlaTetelId, E.OsszAr
	from
	Elozmeny E
	inner join @Kombinacio K on E.ElozoId = K.Id
)
select SzamlaTetelId = T.ID ,Szamlaszam = F.Szamlaszam ,Ar = T.Ar ,Ado = F.Ado
from Elozmeny E
inner join Szamlafej F on F.OsszAr = E.OsszAr
inner join Szamlatetel T on T.ID = E.SzamlaTetelId
order by
E.OsszAr, T.ID

Ez egy olyan feladat, amin érdemes pár percig gondolkodni, és rácsodálkozni, hogy még egy ilyen ciklusért ordító feladatot is meg lehet oldani CTE-kkel (a hatékonyság persze más kérdés).

Egyszerű csoportosítós példa.

A legkézenfekvőbb megoldás egy sima group by és min:

select E.Nev, Telefon = min(E.Telefon) from Ember E group by E.Nev

Ezt egy covering nc indexszel szépen fel lehet gyorsítani:

CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[Ember]
(
	[Nev] ASC
)
INCLUDE ([Telefon]) 

Egy másik megoldás már költségesebb, de helyes eredményt ad (a komment szerzője szerintem ismerte az elsőt, csak valami trükkösebb megoldást akart adni):

select *
from Ember e1
where not exists (
select *
from Ember e2
where e1.Nev = e2.Nev
and e1.Telefon > e2.Telefon
)

Ennek logikája már körülményesebb, kérem azokat a sorokat, amelyekhez nem létezik olyan sor, aminek ugyanaz a neve, de kisebb a telefonszáma. Ezt csak joinnal tudja megoldani a szerver, jelentősen nagyobb költséggel.

Egy jelentősen más logikájú megoldás is érkezett:

;WITH Data AS
(
	SELECT Nev, Telefon, ROW_NUMBER() OVER (PARTITION BY Nev ORDER BY Telefon) AS RowNumber
	FROM Ember
)
SELECT Nev, Telefon
FROM Data
WHERE RowNumber = 1

Felsorszámozzuk a sorokat a Telefon szerint rendezve, partícionálva a Nev alapján, majd ebből a halmazból kiszedjük azokat a sorokat, amelyek sorszáma 1. Minden Nev-hez 1 ilyen lesz, értelemszerűen.
Ez a megoldás nem szereti az előbbi indexünket. Ahhoz, hogy ez is gyors legyen, a Telefon oszlopot is bele kell rakni az index kulcsai közé:

CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[Ember]
(
	[Nev] ASC,
	[Telefon] ASC
)

Így a group by-os és ezen megoldás költsége már azonos lesz, bár a tervük nem. Ez esetben sok sor esetén lehetne eldönteni, van-e perf különbség a két megoldás között, akinek van erre ideje, érdekelne az eredménye. Mondjuk 10 millió sorra. Érzésre nem lesz, de cáfoljatok meg, konkrét terveket mutatva.

Köszönöm mindenkinek a megoldásokat, és külön köszönöm Molnár Csabának, hogy a nem nyilvánvaló megoldást is megmutatta.

Ez a fejtörő most sokkal nehezebb lesz. Adottak számlák és számla tételek. A számla tételek különböző ÁFA kategóriákba tartoznak. Elveszett viszont, hogy melyik tétel melyik ÁFA kategóriába tartozik. Csak összesítve tudjuk, hogy egy adott számlán mennyi volt az egyes ÁFA tételű termékek össz értéke. A feladat visszaállítani minden egyes tétel ÁFA kulcsát. Az adatok úgy vannak összerakva, hogy a feladat egyértelműen megoldható legyen.

Bemeneti adatok:

Számlák:

ID	Szamlaszam	OsszAr	Ado
1	Szamla1	500.00	Afa27
2	Szamla1	300.00	Afa16
3	Szamla2	4000.00	Afa38

Számla tételek:

ID	Szamlaszam	Ar
1	Szamla1	20.00
2	Szamla1	111.00
3	Szamla1	250.00
4	Szamla1	20.00
5	Szamla1	15.00
6	Szamla1	189.00
7	Szamla1	100.00
8	Szamla1	95.00
9	Szamla2	1000.00
10	Szamla2	3000.00

Elvárt kimenet, Adó, számlaszám és id szerint van rendezve:

id	Szamlaszam	Ar	Ado
2	Szamla1	111.00	Afa16
6	Szamla1	189.00	Afa16
1	Szamla1	20.00	Afa27
3	Szamla1	250.00	Afa27
4	Szamla1	20.00	Afa27
5	Szamla1	15.00	Afa27
7	Szamla1	100.00	Afa27
8	Szamla1	95.00	Afa27
9	Szamla2	1000.00	Afa38
10	Szamla2	3000.00	Afa38

Többféle megoldás is adható, mindegyik legalább 15 soros.

use tempdb;

IF OBJECT_ID('Szamlatetel','U') IS NOT NULL BEGIN
  DROP TABLE Szamlatetel
END 
GO

CREATE TABLE Szamlatetel(
	[ID] [INT] IDENTITY(1,1) NOT NULL,
	Szamlaszam [VARCHAR](20) NOT NULL,
	Ar [DECIMAL](18,2) NOT NULL	
)
GO

INSERT INTO Szamlatetel(Szamlaszam,Ar) 
SELECT 'Szamla1', 20 UNION ALL
SELECT 'Szamla1', 111 UNION ALL
SELECT 'Szamla1', 250 UNION ALL
SELECT 'Szamla1', 20  UNION ALL
SELECT 'Szamla1', 15  UNION ALL
SELECT 'Szamla1', 189 UNION ALL
SELECT 'Szamla1', 100 UNION ALL
SELECT 'Szamla1', 95 UNION ALL
SELECT 'Szamla2', 1000 UNION ALL
SELECT 'Szamla2', 3000
go

IF OBJECT_ID('Szamlafej','U') IS NOT NULL BEGIN
  DROP TABLE Szamlafej
END 
GO

CREATE TABLE Szamlafej(
	[ID] [INT] IDENTITY(1,1) NOT NULL,
	Szamlaszam [VARCHAR](20) NOT NULL,
	OsszAr [DECIMAL](18,2) NOT NULL DEFAULT(0),
	[Ado] [VARCHAR](5) NOT NULL
)
GO

INSERT INTO Szamlafej(Szamlaszam, OsszAr, Ado) 
SELECT 'Szamla1', 500, 'Afa27' UNION ALL
SELECT 'Szamla1', 300, 'Afa16' UNION ALL
SELECT 'Szamla2', 4000,  'Afa38'

SELECT * FROM Szamlafej;
GO
SELECT * FROM Szamlatetel order by id;
GO

A megfejtéseket szokás szerint kommentben várom, amelyeket ezúttal a feladat nehézsége miatt 5 nap múlva engedek ki.

A Test Driven Development tanfolyam következő felvonása február kilencedikén lesz, szeretettel várom az érdeklődőket.

Egyszerű lekérdezéses feladat:

use tempdb;

create table Ember
(
	Nev nvarchar(50) not null,
	Telefon nvarchar(50) not null
);
go

insert Ember(Nev, Telefon)
values 
('Gizi', '22222222'),
('Gizi', '00000000'),
('Géza', '11111111'),
('Mari', '33333333');
go

--Ide jön a te query-d:

--A lekérdezés elvárt kimenetében minden név egyszer szerepel, és mindhez egy telefonszám, az, amelyik abc sorrendben az első:
--Géza	11111111
--Gizi	00000000
--Mari	33333333

drop table Ember;

A megfejtéseket szokás szerint kommentben várom, amelyeket 2 nap múlva engedek ki.

A Test Driven Development tanfolyam következő felvonása február kilencedikén lesz, szeretettel várom az érdeklődőket.

Meglepő módon csak 1 megoldás érdekezett erre a fejtörőre, azt hittem egy ez sima guglizós példa lesz.

A példában egy login trigger volt, ami minden login kísérletet visszautasít, azaz ezzel kizárhatjuk magunkat is a szerver példányról, hiába vagyunk sysadminok.

A legegyszerűbb megoldás ilyenkor a 2005-ben bevezetett Dedicated Admin Connection. Simán a szerver neve elé be kell írni, hogy ADMIN:, és máris be tudunk menni a szerverre, hogy töröljük a triggert.

Köszönöm Atcomnak a megoldást, aki egy másik módszert is leírt:
“az SQL Server instanciát minimális konfigurációs módban indítjuk el (-f startup option), ilyenkor az SQL kiszolgáló “single-user” módban kerül.”

Ez már macerásabb, inkább ez durvább problémáknál szoktuk használni, de mindenképpen jó tudni róla.

Kipróbáltam az SQL Server 2014-ben megjelent Delayed Durability-t többféle scenarioban, ezzel a scripttel.
Laptopon, SSD háttérrel de kevés RAM-mal (8G) nem igazán okozott sebességnövekedést. Valaki használja már ezt élőben, rendes diszkekkel? Érdekelne, ott mennyit javít.

Az előző fejtörőben ez volt a kérdés:
“Hogyan biztosítanád egy SQL Server táblában, hogy egy oszlop csak egyedi értékeket tartalmazzon, de NULL-ból bármennyi lehessen benne?”

A unique contranint vagy index alapban nem engedi meg a duplikált nullokat, de SQL Server 2008-tól van filtered index, ami segítségével egy where feltételt lehet megfogalmazni az indexelendő adatokra. Ezzel már passzol a unique index a feladatra.

[soruce lang=’sql]
CREATE UNIQUE INDEX IX_Oszlop1 ON AlmaTabla (Oszlop1) WHERE Oszlop1 IS NOT NULL
[/source]

Ez most könnyű lesz. Tegyük fel ügyetlen voltam, és lefuttattam az alábbi kódot. Nincs is megnyitott kapcsolatom a szerverre. Mit tehetek? Reinstall?

CREATE TRIGGER AFrancbaTrigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
    ROLLBACK;
END;

A megfejtéseket szokás szerint kommentben várom, amelyeket 2 nap múlva engedek ki.

A Test Driven Development tanfolyam következő felvonása február kilencedikén lesz, szeretettel várlak.

Köszönöm a beérkezett megoldásokat a feladatra. Hárman a left joinra építették a megoldást, a bal oldali táblát felszaporítva két sorra, így az eredményhalmaz is két soros lett.
Egy másik megoldás is érkezett, ez ezért tetszik, mivel teljesen másként közelítette meg a feladatot.
select null union all-lal egyszerűen hozzácsapott még egy nullos sort a kimenethez. Ügyes. :)

Közben kiraktam a következő fejtörőt is, fel lehet venni a kesztyűt.

Hogyan biztosítanád egy SQL Server táblában, hogy egy oszlop csak egyedi értékeket tartalmazzon, de NULL-ból bármennyi lehessen benne?

USE TempDB
GO

set ansi_nulls on;
go
CREATE TABLE AlmaTabla (Oszlop1 INT NULL);
GO

--------------------------------------
-- Ide kellene valami, amitol az alabb lathato eredmenyek szuletnek
--------------------------------------

--Elvart eredmeny:

INSERT INTO AlmaTabla (Oszlop1) VALUES (1)		--ok
GO
INSERT INTO AlmaTabla (Oszlop1) VALUES (2)		--ok
GO
INSERT INTO AlmaTabla (Oszlop1) VALUES (NULL)	--ok
GO
INSERT INTO AlmaTabla (Oszlop1) VALUES (NULL)	--ok
GO
INSERT INTO AlmaTabla (Oszlop1) VALUES (2)		--exception
GO

DROP TABLE AlmaTabla;
GO

A Test Driven Development tanfolyam következő felvonása február kilencedikén lesz, szeretettel várlak.

Kevés volt a hely a gép vinyóján, ezért, minden tiltás ellenére kitöröltem a c:\windows\installer könyvár tartalmát. Ez egy hónapig nem is okozott gondot, amíg a hétvégén hozzá nem akartam adni egy új komponenst az SQL Server 2014-hez.
A setup hiányolta az msi fájlokat a c:\windows\installer könyvtárban. De nem olyan egyszerű ezeket pótolni, mivel nem az eredeti néven szerepelnek a misik benne. A registryben meg lehet őket találni, majd onnan a nevük segítségével meg lehet próbálni megtalálni az eredeti msiket az SQL telepítő DVD-n. Ezeket lefuttatva helyre lehet rakni őket, a telepítők bemásolják a c:\windows\installer-be a szükséges fájlokat. De persze nem ilyen egyszerű az élet. Több órai szívás után feladtam, és leinstalláltam a szervert. Persze, ezek után már nem megy fel egy új példány. Sok kis darabka ott marad még az uninstall után, ami megzavarja a telepítőt. Itt van egy leírás, hogyan lehet levakarni a maradványokat. Mivel kézzel kellene sokat matyizni vele, írtam egy kis powershell scriptet a cikk alapján.

$oXMLDocument = Get-Content -Path "C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\20150105_092215\Datastore\Datastore_Discovery.xml" 
$oXMLDocument.SelectNodes("//@MsiId") | select Value | %{ msiexec /quiet /passive /x $_.Value}

!!! Vigyázat, ez szemrebbenés nélkül levakarja a telepített SQL Server példányokat!!! Ésszel tessék futtatni, és csak dev gépen, ha gáz van.

De ez még mindig kevés volt, a perf countereket telepítő ininiket a telepítő nem másolta be a telepített SQL könyvtárba, így továbbra is beakadt a telepítő. sqlagtctr.ini és sqlctr.ini a két fájl. Egyik fenn van közvetlenül a telepítő DVD-n, a másik egy msiben van, amit ki kell csomagolni a felmásoláshoz:

msiexec /a sql_engine_core_inst_loc.msi  /qb TARGETDIR=c:\temp\misi2

Ezek után se volt még sok minden ok, pl. az SQLWriter nem tudott feltelepülni. Ezen meg egy sfc /scannow segített, elég sokat kellett neki rendbe rakni.

Még ezek után se ment fel hiba nélkül a szerver, valami WMI problémája még volt, de emiatt már szerencsére nem görgette vissza a telepítést.
Huh.
De, a Management Studio viszont visszagörgette magát, a WMI MOF hiba miatt.
(“The MOF Compiler could not connect with the WMI Server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI Server to start”.)

WMIDiag letölt, átír a kommentek alapján, hogy menjen Win 8.1-en is.

Ebből kiderült, hogy 1 file hiányzott az sql telepítésből:
WARNING: Some WMI providers EXE/DLL file(s) are missing: …………………………………………………… 1 WARNING(S)!
60906 23:04:13 (0) ** – ROOT/MICROSOFT/SQLSERVER/COMPUTERMANAGEMENT12, MSSQL_ManagementProvider, C:\Program Files\Microsoft SQL Server\120\Shared\sqlmgmprovider.dll

Ezt bemásoltam neki kézzel.

Emellett újraélesztettem az össze MOF-ját a C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn könyvtárból a mofcomp.exe segítségével.
Az egyiknek nem mof a kiterjesztése, mivel azt átírja a telepítő az instance névre:
mofcomp Sqlwep-uni.mof.transformed

Aztán SQL install repair újra, még egy hiányzó msi futattása, hogy az installer könyvtárban még mindig hiányzi misit pótolja.

A fenti lépéseket persze nem csak egyszer kellett megtenni, és nem pont ebben a sorrendben.

És végül győzelem, minden pipa zöld a repair után. :)

2 napot elvitt az életemből, feleslegesen. Ne csináljátok utánam.

Tanulságok:
1. Ne piszkáljuk a c:\windows\installer könyvtárat, mert napokat fog elvinni a kijavítása.
2. Ne vegyünk laptopot 120 G-s SSD-vel, minimum 256G kell egy fejlesztői gépbe, főleg, ha egy-két VM is van rajta.
3. A WmiDiag hasznos eszköz, ez nélkül nem tudtam volna megjavítani a telepítést.

Az alábbi példa 1 sornyi és 1 oszlopnyi eredményhalmazt ad vissza, amiben NULL található. A feladat, hogy a középre be kellene írni egy darab sql kódot, amitől két null jön vissza 1 helyett.

USE TempDB
GO

CREATE TABLE AlmaTabla (Oszlop1 INT NULL)
CREATE TABLE KorteTabla (Oszlop1 INT NULL)
GO
ALTER TABLE AlmaTabla ADD CONSTRAINT
UX_AlmaTabla UNIQUE NONCLUSTERED (Oszlop1);
ALTER TABLE KorteTabla ADD CONSTRAINT
UX_KorteTabla UNIQUE NONCLUSTERED (Oszlop1);
GO
INSERT INTO AlmaTabla (Oszlop1)
VALUES (NULL)
GO
INSERT INTO KorteTabla (Oszlop1)
VALUES (NULL)
GO

--------------------------------------
-- Ide kellene valamit beirni, hogy 2 NULL-os sor jojjon vissza
--------------------------------------

SELECT st.Oszlop1
FROM AlmaTabla ft
LEFT JOIN KorteTabla st ON st.Oszlop1 = ft.Oszlop1
GO

DROP TABLE AlmaTabla;
DROP TABLE KorteTabla;
GO

Kommenteket most se engedek ki, hogy mindenki gondolkodhasson, csak szerdán, addig jöhetnek a megoldások. :)

A Test Driven Development tanfolyam következő felvonása február 9-edikén lesz, szeretettel várlak.