Sinnvoller Einsatz von Table-Partitioning in SQL Server

Wann macht Table-Partioning Sinn? Vor kurzem hatte ich die Aufgabe, eine Liste aus Rechnungen filterbar zu machen – das schwierige dabei war, dass die Anzahl der Rechnungen nicht bekannt ist und diese auch noch hierarchisch waren (1 Rechnung hat 0..n Positionen – wobei die Positionen von unterschiedlichem Typ sein können). Ebenfalls war die Anforderung “über Jahre hinweg eine stabile Performance zu haben”.

Herausforderung 1: Die Pagination in der Übersichtsliste von Rechnungen ist auf der Rechnung – d.h. 10 Element auf der Seite entspricht 10 Rechnungen mit 0..n Positionen. Was bei großen Tabelle gut funktioniert, ist das Suchen nach gewissen Kriterien: e.g. gib mir alle Positionen mit der SomeValue = 123:

INDEX [IX_Positions_SomeValue] NONCLUSTERED ([SomeValue]) INCLUDE ([BillId])

Allerdings bei Pagination lautet die Syntax wie folgt:

ORDER BY foo
OFFSET 100 ROWS
FETCH NEXT 10 ROWS ONLY

D.h. SQL Server (in diesem Fall) muss einmal alle Items sortieren (Dank Index nicht die Mega Aufgabe, da die Leaf-Nodes ja schon sortiert sind). Dann noch das Filtern – in Pseudocode also:

CREATE OR ALTER FUNCTION [Accounting].[vtInvoicePositions](
    @Years [varchar](50),
    @Offset [int],
    @Fetch [int],
    @BusinessKey [varchar](33),
    @InvoiceBusinessDay [date],       
    @Price [numeric](15, 6),
    @PriceOperator [char](1)  
)
RETURNS TABLE
AS
RETURN
(
    WITH [PagedInvoices] AS
    (
        SELECT
            [Invoices].[Id]
        FROM
            [Accounting].[Invoices]
        LEFT JOIN [Accounting].[InvoicePositions] ON
            [Invoices].[Id] = [InvoicePositions].[CostItemId] AND
            [InvoicePositions].[RedispatchYear] IN (SELECT [Number] FROM [Accounting].[GetIntegerFromString](@Years)) AND            
            (@Price IS NULL OR
                (@PriceOperator = '<' AND [InvoicePositions].[Price] < @Price) OR
                (@PriceOperator = '>' AND [InvoicePositions].[Price] > @Price) OR
                (@PriceOperator = '=' AND [InvoicePositions].[Price] = @Price) OR               
                (@PriceOperator = '<=' AND [InvoicePositions].[Price] <= @Price) OR
                (@PriceOperator = '>=' AND [InvoicePositions].[Price] >= @Price)
            )
        WHERE
            [Invoices].[RedispatchBusinessYear] IN (SELECT [Number] FROM [Accounting].[GetIntegerFromString](@Years)) AND        
            (@BusinessKey IS NULL OR [Invoices].[BusinessKey] LIKE CONCAT(@BusinessKey, '%')) AND
            (@InvoiceBusinessDay IS NULL OR [Invoices].[BusinessDay] = @InvoiceBusinessDay)
        GROUP BY [Invoices].[BusinessDay], [Invoices].[Id] 
        ORDER BY [Invoices].[BusinessDay], [Invoices].[Id]
        OFFSET @Offset ROWS FETCH NEXT @Fetch ROWS ONLY
    )
    SELECT
        [Invoices].[BusinessKey],
        [Invoices].[BusinessDay],
        [InvoicePositions].[State],
        [InvoicePositions].[Position],       
        [InvoicePositions].[Price],
        [InvoicePositions].[Comment]
    FROM
        [Accounting].[Invoices]
    INNER JOIN [PagedInvoices] ON [Invoices].[Id] = [PagedInvoices].[Id]
    LEFT JOIN [Accounting].[InvoicePositions] ON
        [Invoices].[Id] = [InvoicePositions].[CostItemId] AND
        [InvoicePositions].[RedispatchYear] IN (SELECT [Number] FROM [Accounting].[GetIntegerFromString](@Years)) AND        
        (@Price IS NULL OR
            (@PriceOperator = '<' AND [InvoicePositions].[Price] < @Price) OR
            (@PriceOperator = '>' AND [InvoicePositions].[Price] > @Price) OR
            (@PriceOperator = '=' AND [InvoicePositions].[Price] = @Price) OR               
            (@PriceOperator = '<=' AND [InvoicePositions].[Price] <= @Price) OR
            (@PriceOperator = '>=' AND [InvoicePositions].[Price] >= @Price)
        )
    WHERE
         [Invoices].[RedispatchBusinessYear] IN (SELECT [Number] FROM [Accounting].[GetIntegerFromString](@Years)) AND
         (@BusinessKey IS NULL OR [Invoices].[BusinessKey] LIKE CONCAT(@BusinessKey, '%')) AND
         (@InvoiceBusinessDay IS NULL OR [Invoices].[BusinessDay] = @InvoiceBusinessDay)
)
GO

Man sieht also, dass man die Rechnungspositionen filtern muss, um die Anzahl der Rechnungen für die Paginationen bestimmen zu können!. Da macht es natürlich Sinn, dass man einen Filter über die Tabelle setzt, der auf Jahre einschränkt. Zu beachten ist, dass ein Suchen über alle partionierten Tabellen im Regelfall langsamer ist, als in einer großen Tabelle.

Mit einer Table-Valued User-Defined Functions kann man das Ganze dann noch so verpacken, dass es für das Backend wie eine Tabelle mit Parameter ausschaut. Den conditional LEFT JOIN braucht man übrigens, weil man natürlich auch Rechnungen ohne Positionen berücksichtigen will.

Übrigens ein großer Nachteil von SQL Server (im Vergleich zu PostgreSQL): Es gibt keine Arrays. Da muss man tricksen:

CREATE OR ALTER FUNCTION [Controlling].[GetIntegerFromString]
(
    @String [varchar](50)
)
RETURNS TABLE
WITH SCHEMABINDING 
AS
RETURN
(
    WITH [SplitNumbers] AS (
        SELECT
            CAST([value] AS [int]) AS [Number]
        FROM
            STRING_SPLIT(@String, ',')     
    )
    SELECT [Number]
    FROM [SplitNumbers]
    WHERE @String NOT LIKE '%[^0-9,]%'
)
GO

Wichtig ist, dass die Function von SQL Server als deterministic (in PostgreSQL stable) erkannt wird - siehe https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-ver16. Erreicht wird es u.a. durch WITH SCHEMABINDING.

Herausforderung 2: Wie erwähnt, gibt es mehrere Arten von Positionen. Schaut man sich die Bibel von Martin Fowler an, kann man in einer DBMS Ableitungen wie folgt gestalten:

Man Fazit ist, dass Class Table Inheritance nicht funktioniert, weil JOINEN und Sortieren das ganze System stark belasten. Concrete Table Inheritance würde für eine Übersicht auch ein UNION benötigen - auch nicht ideal. So bleibt nur mehr Single Table Inheritance übrig. Mit CHECK Constraints kann man Fleder je nach Typ prüfen (NOT NULL, bestimmte Werte etc.)

Indizes in paritionierten Tabellen

Mit folgender Abfrage sieht man, dass Indizes pro Parition gemacht werden:

SELECT 
    i.name AS IndexName,
    i.type_desc AS IndexType,
    ps.name AS PartitionSchemeName,
    pf.name AS PartitionFunctionName,
    p.partition_number,
    p.rows AS RowsInPartition,
    p.data_compression_desc AS CompressionType
FROM 
    sys.indexes i
JOIN 
    sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
LEFT JOIN 
    sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
LEFT JOIN 
    sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE 
    i.object_id = OBJECT_ID('[MySchema].[MyTable]')
    AND i.index_id > 1
ORDER BY 
    i.name, p.partition_number;