Datenbanken
Posts 1-3 of 3
-
Christoph Ingenhaag Premium MemberThe company name is only visible to registered members.Datenbank Allerlei
Hallo zusammen,
mit diesem Artikel verfolge ich die Absicht, Andere hier zu ermuntern eine entsprechende Lösung für andere Datenbanksystem zu schreiben. Es geht mir hier im Vordergrund nicht um das Detail, welches der Grund für diese Implementierung ist, sondern darum zu sehen, wie das mit Oracle, Firebird, MySQL, PostgreSQL, ... umgesetzt würde. Das nachfolgende Skript läuft unter MS SQL Server 2008. Also als Gegenüberstellung der verschiedenen Syntax, Objekte und Möglichkeiten usw. "delete ..." wäre natürlich eine Alternative, aber in diesem Zusammenhang dann eher langweilig.
Wenn also dieser Lösungsweg für das eine oder andere der Datenbanksysteme absurd ist, interessiert mich
trotzdem, wie man die nachfolgenden Funktionen und Prozeduren schreiben würde.
Ach ja, die Datentypen sind eher aus Faulheit gewählt.
Es wäre schön, wenn ohne Kindereien wie "Mein Datenbanksystem ist besser als deins" ausgekommen werden
könnte.
Vielleicht finden sich ja mal Interessierte und nicht nur die "Karteileichen", die sich in dieser
Gruppe nur so anmelden...
So, worum geht es:
Mittels eine Stored Procedure werden alte Logeinträge aus einer Logtabelle gelöscht.
Es soll immer eine bestimmte Anzahl der ältesten Datensätze aus dem Log entfernt werden.
Der Hintergrund für die Art der Implementierung ist dieser Blogeintrag
http://blogs.msdn.com/sqlcat/archive/2009/05/21/fast-ordered...
Hierdurch wird verhindert, dass einmal ein Scan ausgeführt wird um die zu löschenden Daten
zu identifizieren (Top) und noch einmal ein Scan bzw. Seek (je nach Anzahl der Datensätze)
zum Löschen der Daten ausgeführt wird.
Es erfolgt also nur e i n Scan. (siehe dazu auch Ausführungspläne am Ende)
/*Skriptanfang*/
use Test -- Datenbank Test verwenden
go
--von einem Tetslauf noch vorhandene Objekte löschen
if object_id('dbo.fnClearLog') is not NULL drop function dbo.fnClearLog
if object_id('dbo.prClearLog') is not NULL drop proc dbo.prClearLog
if object_id('dbo.Log') is not NULL drop table dbo.Log
go
--Tabelle anlegen
--LogEntry mit max. 2 GB Länge, hier um sich nicht um die Länge kümmern zu müssen
--Timestamp als Zeitstempel mit 100 Nanosekunden Genauigkeit als PK
--(Damit für dieses Beispiel das Einfügen von Datensätzen schneller geht)
create table dbo.Log
(
LogEntry varchar(max)
,Timestamp datetime2(7) default sysdatetime() not NULL --JJJJ-MM-DD HH:MM:SS.1234567
,constraint pk_log primary key clustered(Timestamp) -- clustered = "Tabelle als Index"
)
go
--Eine "Inline Table-Valued" Funktion anlegen
--vgl. mit "parametriesierbare View"
--Gibt soviele Datensätze zurück, wie mit @top übergeben
create function dbo.fnClearLog
(
@top int
)
returns table
with schemabinding
as
return
select top (@top) Timestamp
from dbo.Log
order by
timeStamp asc
go
--Eine Stored Procedure anlegen
--Löscht über die Funktion die mit @RowsToDelete übergebene
--Anzahl von Datensätzen (wenn vorhanden) und
--gibt die Anzahl der gelöschten Datensätze in einer
--Variablen zurück
create proc prClearLog
@RowsToDelete int
,@RowsDeleted int output
as
set xact_abort on
set nocount on
delete dbo.fnClearLog(@RowsToDelete)
set @RowsDeleted = @@rowcount
return @@error
go
--Einfügen von Testdaten
declare @i as int = 1
while @i < 52
begin
insert into dbo.Log(LogEntry)
select 'was ist denn hier passiert?'
waitfor delay '00:00:00.010'
set @i += 1
end
go
--Aufruf der Prozedur
declare
@rows int,
@err int
exec @err = prClearLog 50, @rows output
select
@err as Fehler,
@rows as [Gelöschte Zeilen]
--Zum Vergleich
delete
from dbo.log
where
timestamp in
(
select top(50) timestamp
from dbo.Log
order by
timeStamp asc
)
--Der Plan beim Löschen über die Funktion
|--Clustered Index Delete(OBJECT:([Test].[dbo].[Log].[pk_log]))
|--Top(ROWCOUNT est 0)
|--Top(TOP EXPRESSION:(CONVERT_IMPLICIT(bigint,[@RowsToDelete],0)))
|--Clustered Index Scan(OBJECT:([Test].[dbo].[Log].[pk_log]), ORDERED FORWARD)
--Der Plan beim Löschen über die Tabelle
|--Clustered Index Delete(OBJECT:([Test].[dbo].[Log].[pk_log]))
|--Table Spool
|--Top(ROWCOUNT est 0)
|--Nested Loops(Inner Join, OUTER REFERENCES:([Test].[dbo].[Log].[Timestamp]))
|--Top(TOP EXPRESSION:((50)))
| |--Clustered Index Scan(OBJECT:([Test].[dbo].[Log].[pk_log]), ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([Test].[dbo].[Log].[pk_log])
Viele Grüße
Christoph Ingenhaag
- 05 Feb 2010, 5:18 pm
-
Lukas GradlThe company name is only visible to registered members.Re: Datenbank Allerlei
Hmmm - also wenn ich das richtig verstanden habe, dann sollen aus einer Log-Tabelle soviele der ältesten Zeilen gelöscht werden, daß nur noch eine gewisse Anzahl Einträge da ist, damit z.B. Max. 1000 Zeilen im Log stehen.
Wenn ich das richtig verstanden habe, dann finde ich den Ansatz reichlich kompliziert.
Mit Firebird würde ich das ganz einfach lösen:
Die Tabelle schaut so aus:
CREATE TABLE "LogTest"
(
TS Timestamp,
"Text" Varchar(50)
);
Füllen kann ich sie so:
SET TERM ^ ;
CREATE PROCEDURE "FillLogTest"
AS
DECLARE VARIABLE Counter INTEGER;
BEGIN
Counter=0;
WHILE (Counter<10000) DO BEGIN
INSERT INTO "LogTest" ("TS","Text") VALUES ('now','Text '||CAST(:Counter AS VARCHAR(10)));
Counter=Counter+1;
END
END^
SET TERM ; ^
Wenn ich nun 1000 Einträge haben will und alle älteren löschen, dann wär das folgendes Statement:
DELETE FROM "LogTest" WHERE "TS">(SELECT FIRST 1 SKIP 1000 "TS" FROM "LogTest" ORDER BY "TS")
Einziges Manko: Wenn mehrere Einträge mit dem selben Timestamp existieren, dann werden erst die gelöscht, die noch älter sind, es sind daher nicht ganz sicher nur 1000 Zeilen, sonder eventuell ein paar mehr.
Je nach Anwendungszweck aber leicht zu verschmerzen...
Gruß
Luggi
(PS: Ich HASSE es daß hier keine Einrückungen funktionieren - oder bin ich nur zu dumm dazu?)
This post was modified on 07 Feb 2010 at 05:46 pm.- 07 Feb 2010, 5:46 pm
-
Christoph Ingenhaag Premium MemberThe company name is only visible to registered members.
Re^2: Datenbank Allerlei
Hallo Luggi!
Lukas Gradl schrieb:
Hmmm - also wenn ich das richtig verstanden habe, dann sollen aus einer Log-Tabelle soviele der ältesten Zeilen gelöscht werden, daß nur noch eine gewisse Anzahl Einträge da ist, damit z.B. Max. 1000 Zeilen im Log stehen.
Fast. Einfach eine bestimmte Anzahl der ältesten Datensätze löschen.
Wenn ich das richtig verstanden habe, dann finde ich den Ansatz reichlich kompliziert.
Das ist er auch. Der einfach Ansatz stand unten
delete
from dbo.log
where
timestamp in
(
select top(50) timestamp
from dbo.Log
order by
timeStamp asc
)
und ist
DELETE FROM "LogTest" WHERE "TS">(SELECT FIRST 1 SKIP 1000 "TS" FROM "LogTest" ORDER BY "TS")
ähnlich.
Die Kompliziertheit der Lösung mit der Funktion und der Procedure ergibt sich daraus, eine physikalische Operation einzusparen. Das ein solches Performancetuning generell nicht notwendig ist ist klar, insofern hat das Ganze experimentellen Charakter.
Einziges Manko: Wenn mehrere Einträge mit dem selben Timestamp existieren, dann werden erst die gelöscht, die noch älter sind, es sind daher nicht ganz sicher nur 1000 Zeilen, sonder eventuell ein paar mehr.
Je nach Anwendungszweck aber leicht zu verschmerzen...
Hier hatte ich einfach einen PK auf die Timestamp Spalte gelegt (daher auch waitfor beim Einfügen der Testdaten).
(PS: Ich HASSE es daß hier keine Einrückungen funktionieren - oder bin ich nur zu dumm dazu?)
Das hasse ich auch. XING entfernt alle führenden Leerzeichen.
Viele Grüße
Christoph
- 08 Feb 2010, 11:03 am
