Soci (Soczó Zsolt) szakmai blogja

2015.03.09.

SQL fejtörő 8. – megoldás

Filed under: Adatbázisok,SQL Server,Szakmai élet,Teaser — Soczó Zsolt @ 09:00

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.

2015.03.05.

FORCESEEK hint – szeretem

Filed under: SQL Server,SQL Server 2012,SQL Server 2014,Szakmai élet — Soczó Zsolt @ 20:40

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.

2015.02.22.

SQL fejtörő 7. – megoldás

Filed under: Adatbázisok,SQL Server,SQL Server 2014,Szakmai élet,Teaser — Soczó Zsolt @ 18:00

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:

CrossJoinPlan

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?

2015.02.13.

SQL fejtörő 9.

Filed under: Adatbázisok,SQL Server,Szakmai élet,Teaser — Soczó Zsolt @ 09:00

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.

2015.02.06.

SQL fejtörő 7.

Filed under: Adatbázisok,SQL Server,Szakmai élet,Teaser — Soczó Zsolt @ 09:00

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?

2015.01.28.

SQL fejtörő 6. megoldás

Filed under: Adatbázisok,SQL Server,Szakmai élet — Soczó Zsolt @ 23:47

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

2015.01.20.

SQL fejtörő 5. megoldás

Filed under: Adatbázisok,SQL Server,Szakmai élet — Soczó Zsolt @ 18:56

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.

2015.01.13.

SQL fejtörő 5.

Filed under: Adatbázisok,SQL Server,Szakmai élet,Teaser — Soczó Zsolt @ 08:00

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.

2015.01.12.

SQL fejtörő 4. megoldás

Filed under: Adatbázisok,SQL Server,Szakmai élet — Soczó Zsolt @ 12:20

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.

2015.01.11.

Delayed Durability sebességnövekedés

Filed under: Adatbázisok,SQL Server,SQL Server 2014,Szakmai élet — Soczó Zsolt @ 18:46

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.

2015.01.09.

SQL fejtörő 4.

Filed under: Adatbázisok,SQL Server,Szakmai élet,Teaser — Soczó Zsolt @ 08:00

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.

2015.01.07.

SQL fejtörő 2. megoldások

Filed under: Adatbázisok,SQL Server,Szakmai élet — Soczó Zsolt @ 11:12

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.

SQL fejtörő 3.

Filed under: Adatbázisok,SQL Server,Szakmai élet,Teaser — Soczó Zsolt @ 10:13

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.

2015.01.06.

SQL telepítés rendberakása

Filed under: SQL Server,Szakmai élet — Soczó Zsolt @ 01:58

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.

2015.01.05.

SQL fejtörő 2.

Filed under: Adatbázisok,SQL Server,Szakmai élet — Soczó Zsolt @ 19:27

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.

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

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.

2015.01.03.

SQL fejtörő

Filed under: Adatbázisok,SQL Server,Szakmai élet — Soczó Zsolt @ 17:50

Miért eredményez az alábbi query divide by zero hibát?

[source lang=”sql”]
SELECT 144/(12/12/12);
[/source]

Update: már jött két megoldás, de hétfő estig nem engedem megjelenni a kommenteket, hogy más is gondolkodhasson.

2014.12.22.

Hosszú műveletek állapotának megfigyelése SQL Serveren

Már fél napja fut egy alter index reorganize, honnan tudhatom, mennyi van még hátra?

Innen.

Az infó a sys.dm_exec_requestsben van. Ha még csak 0%-on áll a művelet, akkor nem jön vissza sor, pár perc kell ezeknek a lassú parancsoknak, hogy beinduljanak.

2014.09.24.

Gyors beszúrások sql server táblákba

Filed under: Adatbázisok,SQL Server,Szakmai élet — Soczó Zsolt @ 10:33

Nagyon jó kis lista, a 610-es trace flagről nem tudtam, nem is értem miért dugdossák, marha hasznosnak tűnik. A 11-es megoldás is trükkös benne.
(Utólag visszaolvasva az utolsó mondatot ez tiszta olyan lett, mint a facebook like gyűtő szövegek. :)

2014.07.28.

AlwaysOn ReadOnly replika olvasás

Hangyál Zoli hívta fel a figyelmem egy finomságra. AlwaysOn, ReadOnly replika, szinkron kapcsolat. Egy adott módosítás hatása látszik-e azonnal a replikán, ha a primary-n a tranzakció commitról kaptunk visszajelzést?

2014.05.19.

SQL Server Change Tracking

Ezt a cikket a technetben lehetett olvasni régebben, de kezdenek kiesni a régebbi tartalmak, azért bemásoltam ide a blogba is. Mivel tele van képekkel és táblázattal, inkább átkonvertáltam pdf-re, és nem htmlesítettem.

A teljes cikk itt olvasható:
SQL Server 2008 Change Tracking

« Older PostsNewer Posts »

Powered by WordPress