W systemie, nad którym pracuję, jest wiele procedur przechowywanych i skryptów SQL, które korzystają z tabel tymczasowych. Po użyciu tych tabel dobrą praktyką jest ich upuszczanie.
Wielu moich kolegów (prawie wszyscy są o wiele bardziej doświadczeni niż ja) zazwyczaj to robi:
TRUNCATE TABLE #mytemp
DROP TABLE #mytemp
Zwykle używam jednego DROP TABLEw moich skryptach.
Czy jest jakiś dobry powód, aby zrobić to TRUNCATEbezpośrednio przed DROP?
Odpowiedzi:
TRUNCATEi DROPsą prawie identyczne pod względem zachowania i prędkości, więc robienie TRUNCATEprawa przed znakiem DROPjest po prostu niepotrzebne.
Uwaga: Napisałem tę odpowiedź z perspektywy SQL Server i założyłem, że będzie ona miała zastosowanie w równym stopniu do Sybase. Wydaje się, że tak nie jest .
Uwaga: Kiedy po raz pierwszy opublikowałem tę odpowiedź, pojawiło się kilka innych wysoko ocenianych odpowiedzi - w tym również odpowiedź zaakceptowana - które złożyły kilka fałszywych stwierdzeń, takich jak: TRUNCATEnie jest rejestrowany; TRUNCATEnie może być wycofany; TRUNCATEjest szybszy niż DROP; itp.
Teraz, gdy wątek został oczyszczony, następujące obalenia mogą wydawać się styczne do pierwotnego pytania. Zostawiam je tutaj jako odniesienie dla innych, którzy chcą obalić te mity.
Istnieje kilka popularnych kłamstw - rozpowszechnionych nawet wśród doświadczonych DBA - które mogły motywować ten TRUNCATE-then-DROPwzorzec. Oni są:
TRUNCATEnie jest rejestrowany, dlatego nie można go przywrócić.TRUNCATEjest szybszy niż DROP.Pozwól mi obalić te kłamstwa. Piszę to obalenie z perspektywy SQL Servera, ale wszystko, co tu powiem, powinno mieć również zastosowanie do Sybase.
TRUNCATEOperacja jest zalogowany, więc to może być wycofana . Po prostu zawiń to w transakcji.
USE [tempdb];
SET NOCOUNT ON;
CREATE TABLE truncate_demo (
whatever VARCHAR(10)
);
INSERT INTO truncate_demo (whatever)
VALUES ('log this');
BEGIN TRANSACTION;
TRUNCATE TABLE truncate_demo;
ROLLBACK TRANSACTION;
SELECT *
FROM truncate_demo;
DROP TABLE truncate_demo;
Należy jednak pamiętać, że to nie jest prawdą dla Oracle . Mimo że jest zalogowany i chroniony przez funkcję cofania i ponawiania Oracle, TRUNCATEa inne instrukcje DDL nie mogą być przywrócone przez użytkownika, ponieważ Oracle wydaje niejawne zatwierdzenia bezpośrednio przed i po wszystkich instrukcjach DDL.
TRUNCATEjest minimalnie zalogowany , a nie w pełni zalogowany. Co to znaczy? Powiedz ci TRUNCATEstół. Zamiast umieszczać każdy usunięty wiersz w dzienniku transakcji, TRUNCATEpo prostu zaznacza strony danych, na których żyją, jako nieprzydzielone. Właśnie dlatego jest tak szybki. Dlatego też nie można odzyskać wierszy TRUNCATEtabeli z dziennikiem transakcji za pomocą czytnika dzienników. Znajdziesz tam tylko odniesienia do stron zwolnionych danych.
Porównaj to z DELETE. Jeśli wykonasz DELETEwszystkie wiersze w tabeli i zatwierdzisz transakcję, nadal możesz teoretycznie znaleźć usunięte wiersze w dzienniku transakcji i odzyskać je stamtąd. To dlatego, że DELETEzapisuje każdy usunięty wiersz do dziennika transakcji. W przypadku dużych stołów spowoduje to, że będzie on znacznie wolniejszy niż TRUNCATE.
TRUNCATE, DROPjest minimalnie zalogowany operacja. Oznacza to, że DROPmożna go również wycofać. Oznacza to również, że działa dokładnie tak samo jak TRUNCATE. Zamiast usuwać poszczególne wiersze, DROPoznacza odpowiednie strony danych jako nieprzydzielone i dodatkowo oznacza metadane tabeli jako usunięte .Ponieważ TRUNCATEi DROPdziałają dokładnie w ten sam sposób, biegną tak samo szybko jak siebie nawzajem. Nie ma sensu TRUNCATEstawianie stolików przed ich DROPstawianiem. Uruchom ten skrypt demonstracyjny na instancji programistycznej, jeśli mi nie wierzysz.
Na mojej lokalnej maszynie z ciepłą pamięcią podręczną wyniki są następujące:
table row count: 134,217,728
run# transaction duration (ms)
TRUNCATE TRUNCATE then DROP DROP
==========================================
01 0 1 4
02 0 39 1
03 0 1 1
04 0 2 1
05 0 1 1
06 0 25 1
07 0 1 1
08 0 1 1
09 0 1 1
10 0 12 1
------------------------------------------
avg 0 8.4 1.3
Tak więc, dla tabeli z 134 milionami wierszy zarówno DROPi nie TRUNCATEzajmuje to wcale czasu. (Na zimnym buforze one trwać około 2-3 sekund na pierwszym biegu lub dwa). Uważam też, że im wyższy średni czas dla TRUNCATEówczesnego DROPdziałania można przypisać do wahań obciążenia na moim komputerze lokalnym, a nie dlatego, że połączenie jest jakoś magicznie rząd wielkości gorszy niż poszczególne operacje. W końcu są prawie dokładnie tym samym.
Jeśli interesują Cię bardziej szczegółowe informacje na temat narzutu związanego z rejestrowaniem tych operacji, Martin ma proste wyjaśnienie tego.
Testowanie TRUNCATEnastępnie DROPvs po prostu robi to DROPbezpośrednio wynika, że pierwsze podejście rzeczywiście ma niewielkie zwiększone obciążenie rejestrowania tak może być nawet lekko nieprawdziwy.
Patrząc na poszczególne rekordy dziennika pokazuje, że TRUNCATE ... DROPwersja jest prawie identyczna z DROPwersją, z wyjątkiem tych dodatkowych wpisów.
+-----------------+---------------+-------------------------+
| Operation | Context | AllocUnitName |
+-----------------+---------------+-------------------------+
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysallocunits.clust |
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrowsets.clust |
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrscols.clst |
| LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrscols.clst |
| LOP_HOBT_DDL | LCX_NULL | NULL |
| LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysallocunits.clust |
| LOP_HOBT_DDL | LCX_NULL | NULL |
| LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysrowsets.clust |
| LOP_LOCK_XACT | LCX_NULL | NULL |
+-----------------+---------------+-------------------------+
Tak więc TRUNCATEpierwsza wersja marnuje trochę wysiłku na wykonanie niektórych aktualizacji różnych tabel systemowych w następujący sposób
rcmodifieddla wszystkich kolumn tabeli wsys.sysrscolsrcrowswsysrowsetspgfirst, pgroot, pgfirstiam, pcused, pcdata, pcreservedwsys.sysallocunitsTe wiersze tabeli systemowej zostają usunięte dopiero po upuszczeniu tabeli w następnej instrukcji.
Pełny podział rejestrowania przeprowadzonego przez TRUNCATEvs DROPznajduje się poniżej. Dodałem również DELETEw celach porównawczych.
+-------------------+-------------------+--------------------+------------------+-----------+---------------+-------------+------------------+-----------+---------------+-------------+
| | | | Bytes | Count |
+-------------------+-------------------+--------------------+------------------+-----------+---------------+-------------+------------------+-----------+---------------+-------------+
| Operation | Context | AllocUnitName | Truncate / Drop | Drop Only | Truncate Only | Delete Only | Truncate / Drop | Drop Only | Truncate Only | Delete Only |
+-------------------+-------------------+--------------------+------------------+-----------+---------------+-------------+------------------+-----------+---------------+-------------+
| LOP_BEGIN_XACT | LCX_NULL | | 132 | 132 | 132 | 132 | 1 | 1 | 1 | 1 |
| LOP_COMMIT_XACT | LCX_NULL | | 52 | 52 | 52 | 52 | 1 | 1 | 1 | 1 |
| LOP_COUNT_DELTA | LCX_CLUSTERED | System Table | 832 | | 832 | | 4 | | 4 | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | System Table | 2864 | 2864 | | | 22 | 22 | | |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | T | | | | 8108000 | | | | 1000 |
| LOP_HOBT_DDL | LCX_NULL | | 108 | 36 | 72 | | 3 | 1 | 2 | |
| LOP_LOCK_XACT | LCX_NULL | | 336 | 296 | 40 | | 8 | 7 | 1 | |
| LOP_MODIFY_HEADER | LCX_PFS | Unknown Alloc Unit | 76 | 76 | | 76 | 1 | 1 | | 1 |
| LOP_MODIFY_ROW | LCX_CLUSTERED | System Table | 644 | 348 | 296 | | 5 | 3 | 2 | |
| LOP_MODIFY_ROW | LCX_IAM | T | 800 | 800 | 800 | | 8 | 8 | 8 | |
| LOP_MODIFY_ROW | LCX_PFS | T | 11736 | 11736 | 11736 | | 133 | 133 | 133 | |
| LOP_MODIFY_ROW | LCX_PFS | Unknown Alloc Unit | 92 | 92 | 92 | | 1 | 1 | 1 | |
| LOP_SET_BITS | LCX_GAM | T | 9000 | 9000 | 9000 | | 125 | 125 | 125 | |
| LOP_SET_BITS | LCX_IAM | T | 9000 | 9000 | 9000 | | 125 | 125 | 125 | |
| LOP_SET_BITS | LCX_PFS | System Table | 896 | 896 | | | 16 | 16 | | |
| LOP_SET_BITS | LCX_PFS | T | | | | 56000 | | | | 1000 |
| LOP_SET_BITS | LCX_SGAM | Unknown Alloc Unit | 168 | 224 | 168 | | 3 | 4 | 3 | |
+-------------------+-------------------+--------------------+------------------+-----------+---------------+-------------+------------------+-----------+---------------+-------------+
| Total | | | 36736 | 35552 | 32220 | 8164260 | 456 | 448 | 406 | 2003 |
+-------------------+-------------------+--------------------+------------------+-----------+---------------+-------------+------------------+-----------+---------------+-------------+
Test został przeprowadzony w bazie danych z pełnym modelem odzyskiwania na podstawie tabeli 1000 wierszy z jednym rzędem na stronę. Tabela zużywa łącznie 1004 strony ze względu na główną stronę indeksu i 3 strony indeksu poziomu pośredniego.
8 z tych stron to alokacje pojedynczych stron w mieszanych zakresach, a pozostała część rozłożona na 125 jednolitych zakresów. 8 pojedynczych przydziałów pojedynczych stron pojawia się jako 8 LOP_MODIFY_ROW,LCX_IAMpozycji dziennika. 125 dezalokacji zakresu jako LOP_SET_BITS LCX_GAM,LCX_IAM. Obie te operacje wymagają również aktualizacji powiązanej PFSstrony, stąd połączone 133 LOP_MODIFY_ROW, LCX_PFSwpisy. Następnie, gdy tabela zostanie faktycznie upuszczona, należy usunąć metadane na jej temat z różnych tabel systemowych, stąd 22 LOP_DELETE_ROWSwpisy w dzienniku tabeli systemowej (rozliczone jak poniżej)
+----------------------+--------------+-------------------+-------------------+
| Object | Rows Deleted | Number of Indexes | Delete Operations |
+----------------------+--------------+-------------------+-------------------+
| sys.sysallocunits | 1 | 2 | 2 |
| sys.syscolpars | 2 | 2 | 4 |
| sys.sysidxstats | 1 | 2 | 2 |
| sys.sysiscols | 1 | 2 | 2 |
| sys.sysobjvalues | 1 | 1 | 1 |
| sys.sysrowsets | 1 | 1 | 1 |
| sys.sysrscols | 2 | 1 | 2 |
| sys.sysschobjs | 2 | 4 | 8 |
+----------------------+--------------+-------------------+-------------------+
| | | | 22 |
+----------------------+--------------+-------------------+-------------------+
Pełny skrypt poniżej
DECLARE @Results TABLE
(
Testing int NOT NULL,
Operation nvarchar(31) NOT NULL,
Context nvarchar(31) NULL,
AllocUnitName nvarchar(1000) NULL,
SumLen int NULL,
Cnt int NULL
)
DECLARE @I INT = 1
WHILE @I <= 4
BEGIN
IF OBJECT_ID('T','U') IS NULL
CREATE TABLE T(N INT PRIMARY KEY,Filler char(8000) NULL)
INSERT INTO T(N)
SELECT DISTINCT TOP 1000 number
FROM master..spt_values
CHECKPOINT
DECLARE @allocation_unit_id BIGINT
SELECT @allocation_unit_id = allocation_unit_id
FROM sys.partitions AS p
INNER JOIN sys.allocation_units AS a
ON p.hobt_id = a.container_id
WHERE p.object_id = object_id('T')
DECLARE @LSN NVARCHAR(25)
DECLARE @LSN_HEX NVARCHAR(25)
SELECT @LSN = MAX([Current LSN])
FROM fn_dblog(null, null)
SELECT @LSN_HEX=
CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 1, 8),2) AS INT) AS VARCHAR) + ':' +
CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 10, 8),2) AS INT) AS VARCHAR) + ':' +
CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 19, 4),2) AS INT) AS VARCHAR)
BEGIN TRAN
IF @I = 1
BEGIN
TRUNCATE TABLE T
DROP TABLE T
END
ELSE
IF @I = 2
BEGIN
DROP TABLE T
END
ELSE
IF @I = 3
BEGIN
TRUNCATE TABLE T
END
ELSE
IF @I = 4
BEGIN
DELETE FROM T
END
COMMIT
INSERT INTO @Results
SELECT @I,
CASE
WHEN GROUPING(Operation) = 1 THEN 'Total'
ELSE Operation
END,
Context,
CASE
WHEN AllocUnitId = @allocation_unit_id THEN 'T'
WHEN AllocUnitName LIKE 'sys.%' THEN 'System Table'
ELSE AllocUnitName
END,
COALESCE(SUM([Log Record Length]), 0) AS [Size in Bytes],
COUNT(*) AS Cnt
FROM fn_dblog(@LSN_HEX, null) AS D
WHERE [Current LSN] > @LSN
GROUP BY GROUPING SETS((Operation, Context,
CASE
WHEN AllocUnitId = @allocation_unit_id THEN 'T'
WHEN AllocUnitName LIKE 'sys.%' THEN 'System Table'
ELSE AllocUnitName
END),())
SET @I+=1
END
SELECT Operation,
Context,
AllocUnitName,
AVG(CASE WHEN Testing = 1 THEN SumLen END) AS [Truncate / Drop Bytes],
AVG(CASE WHEN Testing = 2 THEN SumLen END) AS [Drop Bytes],
AVG(CASE WHEN Testing = 3 THEN SumLen END) AS [Truncate Bytes],
AVG(CASE WHEN Testing = 4 THEN SumLen END) AS [Delete Bytes],
AVG(CASE WHEN Testing = 1 THEN Cnt END) AS [Truncate / Drop Count],
AVG(CASE WHEN Testing = 2 THEN Cnt END) AS [Drop Count],
AVG(CASE WHEN Testing = 3 THEN Cnt END) AS [Truncate Count],
AVG(CASE WHEN Testing = 4 THEN Cnt END) AS [Delete Count]
FROM @Results
GROUP BY Operation,
Context,
AllocUnitName
ORDER BY Operation, Context,AllocUnitName
DROP TABLE T
OK, pomyślałem, że spróbuję wykonać testy porównawcze, które nie polegałyby na „ciepłym buforowaniu”, aby, miejmy nadzieję, byłyby bardziej realistycznym testem (także przy użyciu Postgres, aby sprawdzić, czy odpowiada tym samym cechom innych opublikowanych odpowiedzi) :
Moje testy porównawcze przy użyciu postgres 9.3.4 z dużą bazą danych (mam nadzieję, że wystarczająco duże, aby nie zmieściły się w pamięci podręcznej RAM):
Za pomocą tego skryptu testowego DB: https://gist.github.com/rdp/8af84fbb54a430df8fc0
z rzędami 10 mln:
truncate: 1763ms
drop: 2091ms
truncate + drop: 1763ms (truncate) + 300ms (drop) (2063ms total)
drop + recreate: 2063ms (drop) + 242ms (recreate)
z rzędami 100 mln:
truncate: 5516ms
truncate + drop: 5592ms
drop: 5680ms (basically, the exact same ballpark)
Na podstawie tego przypuszczam, że: drop jest „w przybliżeniu” tak szybki (lub szybszy) jak obcinanie + drop (przynajmniej w przypadku nowoczesnych wersji Postgres), jednak jeśli planujesz również odwrócić się i odtworzyć stół, możesz: dobrze trzymaj się prostego obcięcia, które jest szybsze niż upuszczanie + odtwarzanie (ma sens). FWIW.
Uwaga 1: https://stackoverflow.com/questions/11419536/postgresql-truncation-speed/11423886#11423886 (mówi, że postgres 9.2 może mieć szybsze obcinanie niż poprzednie wersje). Jak zawsze porównuj z własnym systemem, aby zobaczyć jego cechy.
Uwaga 2: Obcinanie można wycofać w postgresie, jeśli jest to transakcja: http://www.postgresql.org/docs/8.4/static/sql-truncate.html
Uwaga 3: Obcinanie może przy małych tabelach czasem być wolniejsze niż usuwanie: https://stackoverflow.com/questions/11419536/postgresql-truncation-speed/11423886#11423886
Dodanie perspektywy historycznej ...
Usunięcie tabeli wymaga zaktualizowania kilku tabel systemowych, co z kolei zwykle wymaga wprowadzenia zmian w tabeli systemowej w jednej transakcji (pomyśl „rozpocznij tran, usuń kolumny systemowe, usuń obiekty systemowe, zatwierdzaj”).
W „drop table” uwzględniono także potrzebę cofnięcia przydziału wszystkich stron danych / indeksów powiązanych z tabelą.
Wiele, wiele, wiele lat temu ... proces dezalokacji przestrzeni został uwzględniony w transakcji, która również zaktualizowała tabele systemowe; wynik netto był taki, że im większa liczba przydzielonych stron, tym dłużej trwało cofnięcie alokacji wspomnianych stron, tym dłużej transakcja (w tabelach systemowych) pozostała otwarta, a zatem większa szansa na zablokowanie (w tabelach systemowych) innych procesów próbujących tworzyć / upuszczać tabele w tempdb (szczególnie nieprzyjemne w przypadku starszych stron == blokowanie na poziomie strony i potencjał dla tabeli - eskalacja blokady poziomu).
Jedną z wczesnych metod (stosowanych w tamtych czasach) w celu zmniejszenia rywalizacji o tabele systemowe było skrócenie czasu blokowania blokad w tabelach systemowych, a jednym (relatywnie) łatwym sposobem na to było cofnięcie przydziału stron danych / indeksów przed usunięciem stół.
Chociaż truncate tablenie zwalnia wszystkich stron danych / indeksów, zwalnia wszystkie 8-stronicowe zakresy danych. innym „hackiem” było upuszczenie wszystkich indeksów przed upuszczeniem tabeli (tak, oddziel txn na sysindexes, ale mniejszy txn dla upuszczenia tabeli).
Gdy weźmiesz pod uwagę, że (jeszcze wiele, wiele lat temu) istniała tylko jedna baza danych „tempdb”, a niektóre aplikacje korzystały z tej bazy HEAVY z tą pojedynczą bazą danych „tempdb”, wszelkie „włamania”, które mogłyby zmniejszyć rywalizację o tabele systemowe w „tempdb” były korzystne; z czasem sytuacja uległa poprawie ... wiele tymczasowych baz danych, blokowanie na poziomie wiersza tabel systemowych, lepsze metody dezalokacji itp.
W międzyczasie użycie polecenia truncate tablenic nie zaszkodzi, jeśli pozostanie w kodzie.
Sensowne jest wykonanie TRUNCATE dla tabel, które mają klucze obce. Jednak w przypadku tabel tymczasowych wystarczy DROP
Chodzi o truncateto, aby po prostu i nieodwołalnie usunąć wszystko z tabeli (niektóre specyfikacje techniczne oparte na silnikach magazynu danych mogą się nieznacznie różnić) - pomijając ciężkie logowanie itp.
drop tablerejestruje wszystkie zmiany w miarę ich wprowadzania. Tak więc, aby mieć minimalne rejestrowanie i zmniejszyć niepotrzebne odejście od systemu, podejrzewam, że bardzo duży stół można najpierw obciąć, a następnie upuścić.
truncate może być zawinięty w transakcję (co byłoby najbezpieczniejszą opcją), co oczywiście pozwoli ci wycofać operację.