Soci (Soczó Zsolt) szakmai blogja

Feladat: van egy trading accountunk, ezen hol nyerünk, hol vesztünk. Az account nem mehet 0 alá, de ha nagyobb a veszteségünk mint az account értéke, a broker behajtja rajtunk a veszteséget, vissza kell pótolni a pénzt. A feladat számla mozgásainak összegzése.

Az elvárt kimenet az SQL tartalomban látható.

[source language=”sql”]
USE tempdb;

IF OBJECT_ID(N’dbo.Trades’, N’U’) IS NOT NULL DROP TABLE dbo.Trades;

CREATE TABLE dbo.Trades
(
tradeId INT NOT NULL PRIMARY KEY,
amount INT NOT NULL
);

INSERT INTO dbo.Trades(tradeId, amount)
values (1,12),(2,-25),(3,40),(4,10),(5,-23),(6,-32),(7,14),(8,-23),(9,12),(10,-20),(11,13),(12,-90);
GO

–Elvárt kimenet:
–tradeId amount accountValue moneyBack
–1 12 12 0
–2 -25 0 13
–3 40 40 0
–4 10 50 0
–5 -23 27 0
–6 -32 0 5
–7 14 14 0
–8 -23 0 9
–9 12 12 0
–10 -20 0 8
–11 13 13 0
–12 -90 0 77
[/source]

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!

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.

[source language=”sql”]
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 + ‘%’
[/source]

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.

[source lang=”SQL”]
;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
[/source]

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:

[source lang=”sql”]
SET STATISTICS PROFILE ON;
GO
–Next, run your query in this session
select * from [dbo].[Nums] n1 cross join dbo.Nums n2;
[/source]

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:

[source lang=”sql”]
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;
[/source]

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):

[source lang=”text”]
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
[/source]

Egy perccel később:
[source lang=”text”]
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
[/source]

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.

[source language=”sql”]
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
[/source]

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?

[source lang=”sql”]
;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;
[/source]

Ennek kimenete:

[source lang=”text”]
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
[/source]

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:

[source lang=”sql”]
;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;
[/source]

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:

[source lang=”sql”]
;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
[/source]

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:

[source lang=”sql”]
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
[/source]

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:

[source lang=”sql”]
select E.Nev, Telefon = min(E.Telefon) from Ember E group by E.Nev
[/source]

Ezt egy covering nc indexszel szépen fel lehet gyorsítani:
[source lang=”sql”]
CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[Ember]
(
[Nev] ASC
)
INCLUDE ([Telefon])
[/source]

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):
[source lang=”sql”]
select *
from Ember e1
where not exists (
select *
from Ember e2
where e1.Nev = e2.Nev
and e1.Telefon > e2.Telefon
)
[/source]
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:
[source lang=”sql”]
;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
[/source]
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é:
[source lang=”sql”]
CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[Ember]
(
[Nev] ASC,
[Telefon] ASC
)
[/source]

Í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:
[source language=”text”]
ID Szamlaszam OsszAr Ado
1 Szamla1 500.00 Afa27
2 Szamla1 300.00 Afa16
3 Szamla2 4000.00 Afa38
[/source]

Számla tételek:
[source language=”text”]
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
[/source]

Elvárt kimenet, Adó, számlaszám és id szerint van rendezve:
[source language=”text”]
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
[/source]

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

[source language=”sql”]
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
[/source]

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:

[source language=”sql”]
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;
[/source]

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?

[source language=”sql”]
CREATE TRIGGER AFrancbaTrigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
ROLLBACK;
END;
[/source]

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?

[source language=”sql”]
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
[/source]

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.

[source lang=”powershell”]
[xml]$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}
[/source]

!!! 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:

[source lang=”text”]
msiexec /a sql_engine_core_inst_loc.msi /qb TARGETDIR=c:\temp\misi2
[/source]

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.