Spis treści:

Kategoria:Optymalizacja SQLSQL Server


Wydajność operacji INSERT bez transakcji i z transakcją

Błąd braku transakcji

Dość powszechną czynnością w zarządzaniu bazami danych SQL Server jest kopiowanie dużej ilości danych z jednego miejsca do innego. Jak pokazuje praktyka, nawet tak z pozoru proste i oczywiste operacje mogą być wykonane dobrze, mniej dobrze lub beznadziejnie źle. Jednym z częstszych błędów jest pomijanie transakcji. Jak taka transakcja może wpływać na wydajność? Posłużymy się prostym przykładem:

CREATE TABLE IndexedTableA
(ID INT IDENTITY(1,1),
C1 INT,
C2 INT,
C3 INT)
ALTER TABLE IndexedTableA
ADD CONSTRAINT PK_IDA PRIMARY KEY CLUSTERED (ID)
CREATE INDEX I1 ON IndexedTableA(C1,C2,C3)
CREATE INDEX I2 ON IndexedTableA(C2,C1,C3)
CREATE INDEX I3 ON IndexedTableA(C3,C1,C2)

CREATE TABLE IndexedTableB
(ID INT IDENTITY(1,1),
C1 INT,
C2 INT,
C3 INT)
ALTER TABLE IndexedTableB
ADD CONSTRAINT PK_IDB PRIMARY KEY CLUSTERED (ID)
CREATE INDEX I1 ON IndexedTableB(C1,C2,C3)
CREATE INDEX I2 ON IndexedTableB(C2,C1,C3)
CREATE INDEX I3 ON IndexedTableB(C3,C1,C2)

--skrypt A
DECLARE @t DATETIMEGETDATE()
SET NOCOUNT ON
DECLARE @i INT = 1;
WHILE @i<50000
BEGIN
  INSERT INTO IndexedTableA VALUES(@i,@i,@i)
  SET @i = @i+1
END
SELECT DATEDIFF(millisecond,@t,GETDATE())

--skrypt B
SELECT @t = GETDATE()
BEGIN TRANSACTION
  SET NOCOUNT ON
  DECLARE @j INT = 1;
  WHILE @j<50000
  BEGIN
    INSERT INTO IndexedTableB VALUES(@j,@j,@j)
    SET @j = @j+1
  END
COMMIT
SELECT DATEDIFF(millisecond,@t,GETDATE())

Kod porównawczy jest dość prosty. Tworzone są dwie identyczne tabele z kluczem głównym, a także, dla lepszego zobrazowania problemu, trzy indeksy. Sama struktura indeksów nie jest jakoś szczególnie istotna, a służy jedynie zasymulowaniu bardziej rzeczywistych tabel. Zwykłe produkcyjne tabele mają najczęściej jakieś indeksy założone. Różnice w metodach mierzących wydajność są niewielkie. Pierwsza od drugiej różni się tylko obecnością instrukcji BEGIN TRANSACTION oraz COMMIT. Różnica potrafi być zaskakująca: w pierwszym przypadku skrypt wykonuje się (średnio) około 19 sekund, natomiast w drugim przypadku 1,2 sekundy. Gdy dane do tabeli wstawiają się wolno, warto przypomnieć sobie o transakcji. Nie jest wykluczone, że operacje są rzeczywiście czasochłonne, ale być może to właśnie tutaj leży problem.

Kategoria:Optymalizacja SQLSQL Server

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
chcę dodać kolumnę, która będzie połączeniem dwóch innych istniejących już kolumn, jak powinien wyglądać scrypt?
Przydałyby się jeszcze 2 rzeczy do cz. 3 i byłoby superanckie.
1. Na starcie sortuje wg jakiejś kolumny i tam jest już strzałeczka. Widok takiej strzałeczki daje znać użytkownikowi, że taką tabele można sortować, a na razie pojawia się ona tylko po kliknięciu.
2. Uwzględnienie polskich znaków, bo np. przy sortowaniu Nazwisk i Imion jest to bardzo uciążliwe.
Ogólnie bardzo fajnie i prosto.
PS. Jest ten artykuł z jQuery już dostępny.
bardo ciekawe , można dzięki takim wpisom dostrzec wędkę..
Bardzo dziękuję za jasne tłumaczenie z dobrze dobranym przykładem!