Verwenden Sie Sparse-Spalten und Spaltensätze mit mssql-python

Sparse Columns sind eine Microsoft SQL-Speicheroptimierung für NULL-Werte in Tabellen mit vielen nullierbaren Spalten. Client-Anwendungen sehen spärliche Spalten als reguläre Spalten. Der mssql-python-Treiber liest und schreibt sie wie jede andere Spalte ohne spezielle Behandlung.

Am besten geeignet für:

• Tabellen mit 20-50%+ NULL-Werten.
• Dokumentenspeicherung mit variablen Attributen.
• EAV (Entität-Attribute-Value)-Muster.
• Sensordaten mit vielen optionalen Messwerten.

Erstellen Sie spärliche Spalten

Definiere spärliche Spalten in deinem Tabellenschema, indem du den SPARSE NULL Modifikator zu Spalten hinzufügst, die häufig NULL-Werte enthalten.

Grundlegende Tabelle mit dünn besetzten Spalten

Erstelle eine Tabelle mit spärlichen Spalten für optionale Attribute.

CREATE TABLE ProductAttributes (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL,
    -- Sparse columns for optional attributes
    Color NVARCHAR(50) SPARSE NULL,
    Size NVARCHAR(20) SPARSE NULL,
    Weight DECIMAL(10,2) SPARSE NULL,
    Material NVARCHAR(100) SPARSE NULL,
    Warranty INT SPARSE NULL,
    Manufacturer NVARCHAR(100) SPARSE NULL
);

Mit Spaltensatz

Fügen Sie eine Spaltenmenge hinzu, um XML-Zugriff auf alle spärlichen Spalten gleichzeitig zu gewährleisten.

CREATE TABLE ProductAttributesWithSet (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL,
    -- Column set provides XML access to all sparse columns
    SparseAttributes XML COLUMN_SET FOR ALL_SPARSE_COLUMNS,
    -- Sparse columns
    Color NVARCHAR(50) SPARSE NULL,
    Size NVARCHAR(20) SPARSE NULL,
    Weight DECIMAL(10,2) SPARSE NULL,
    Material NVARCHAR(100) SPARSE NULL,
    Warranty INT SPARSE NULL,
    Manufacturer NVARCHAR(100) SPARSE NULL
);

Sparse-Spaltendaten einfügen

Füge in spärliche Spalten nach Namen ein, genau wie bei regulären Spalten.

Einzelne Spalten einfügen

Fügen Sie Produkte mit bestimmten sparsen Spaltenwerten ein.

import mssql_python

conn = mssql_python.connect(connection_string)
cursor = conn.cursor()

# Create the table with sparse columns
cursor.execute("DROP TABLE IF EXISTS ProductAttributes")
cursor.execute("""
    CREATE TABLE ProductAttributes (
        ProductID INT PRIMARY KEY,
        ProductName NVARCHAR(100) NOT NULL,
        Color NVARCHAR(50) SPARSE NULL,
        Size NVARCHAR(20) SPARSE NULL,
        Weight DECIMAL(10,2) SPARSE NULL,
        Material NVARCHAR(100) SPARSE NULL,
        Warranty INT SPARSE NULL,
        Manufacturer NVARCHAR(100) SPARSE NULL
    )
""")

# Insert with some sparse columns populated
cursor.execute("""
    INSERT INTO ProductAttributes (ProductID, ProductName, Color, Size)
    VALUES (%(id)s, %(name)s, %(color)s, %(size)s)
""", {"id": 1, "name": "T-Shirt", "color": "Blue", "size": "Large"})

# Insert with different sparse columns
cursor.execute("""
    INSERT INTO ProductAttributes (ProductID, ProductName, Weight, Material)
    VALUES (%(id)s, %(name)s, %(weight)s, %(material)s)
""", {"id": 2, "name": "Coffee Mug", "weight": 0.35, "material": "Ceramic"})

conn.commit()

Einfügen durch Spaltensatz (XML)

Fügen Sie mehrere spärliche Spaltenwerte gleichzeitig ein, indem Sie XML an die Spaltenmenge übergeben.

# Create the table with a column set
cursor.execute("DROP TABLE IF EXISTS ProductAttributesWithSet")
cursor.execute("""
    CREATE TABLE ProductAttributesWithSet (
        ProductID INT PRIMARY KEY,
        ProductName NVARCHAR(100) NOT NULL,
        SparseAttributes XML COLUMN_SET FOR ALL_SPARSE_COLUMNS,
        Color NVARCHAR(50) SPARSE NULL,
        Size NVARCHAR(20) SPARSE NULL,
        Weight DECIMAL(10,2) SPARSE NULL,
        Material NVARCHAR(100) SPARSE NULL,
        Warranty INT SPARSE NULL,
        Manufacturer NVARCHAR(100) SPARSE NULL
    )
""")

# Insert using column set XML
cursor.execute("""
    INSERT INTO ProductAttributesWithSet (ProductID, ProductName, SparseAttributes)
    VALUES (%(id)s, %(name)s, %(xml)s)
""", {
    "id": 3,
    "name": "Laptop Bag",
    "xml": "<Color>Black</Color><Size>Medium</Size><Material>Nylon</Material><Warranty>24</Warranty>"
})
conn.commit()

Dynamische Attributeinfügung

Erstelle eine Funktion, die dynamische Attribute als Wörterbuch akzeptiert und das XML automatisch erstellt.

def insert_with_attributes(cursor, product_id: int, name: str, attributes: dict):
    """Insert product with dynamic sparse column attributes."""
    # Build XML for column set
    xml_parts = [f"<{key}>{value}</{key}>" for key, value in attributes.items()]
    attributes_xml = "".join(xml_parts)
    
    cursor.execute("""
        INSERT INTO ProductAttributesWithSet (ProductID, ProductName, SparseAttributes)
        VALUES (%(id)s, %(name)s, %(xml)s)
    """, {"id": product_id, "name": name, "xml": attributes_xml or None})

# Usage
insert_with_attributes(cursor, 4, "Headphones", {
    "Color": "Silver",
    "Warranty": 12,
    "Manufacturer": "AudioTech"
})
conn.commit()

Abfrage der spärlichen Spalten

Fragen Sie sparse Spalten nach Name ab, oder rufen Sie alle sparse Werte auf einmal über den Spaltensatz ab.

Abfrage einzelner Spalten

Abfrage spezifischer spärlicher Spalten mit der Standard-SELECT-Syntax.

# Query specific sparse columns
cursor.execute("""
    SELECT ProductID, ProductName, Color, Size
    FROM ProductAttributes
    WHERE Color IS NOT NULL
""")

for row in cursor:
    print(f"{row.ProductName}: {row.Color}, {row.Size}")

Abfrage durch Spaltensatz

Holen Sie alle spärlichen Spaltenwerte als XML aus dem Spaltensatz ab.

# Get column set XML
cursor.execute("""
    SELECT ProductID, ProductName, SparseAttributes
    FROM ProductAttributesWithSet
    WHERE ProductID = %(id)s
""", {"id": 3})

row = cursor.fetchone()
print(f"Product: {row.ProductName}")
print(f"Attributes XML: {row.SparseAttributes}")

XML eines Spaltensatzes in Python analysieren

Parse das XML aus dem Spaltensatz, um es in ein Python-Wörterbuch umzuwandeln und die Bearbeitung zu erleichtern.

from xml.etree import ElementTree as ET

def get_product_attributes(cursor, product_id: int) -> dict:
    """Get product with parsed sparse attributes."""
    cursor.execute("""
        SELECT ProductName, SparseAttributes
        FROM ProductAttributesWithSet
        WHERE ProductID = %(id)s
    """, {"id": product_id})
    
    row = cursor.fetchone()
    if row is None:
        return None
    
    result = {"ProductName": row.ProductName}
    
    # Parse XML column set
    if row.SparseAttributes:
        # Wrap in root element for parsing
        xml_str = f"<root>{row.SparseAttributes}</root>"
        root = ET.fromstring(xml_str)
        
        for elem in root:
            result[elem.tag] = elem.text
    
    return result

# Usage
product = get_product_attributes(cursor, 3)
print(product)
# {'ProductName': 'Laptop Bag', 'Color': 'Black', 'Size': 'Medium', 'Material': 'Nylon', 'Warranty': '24'}

Abfrage mit SELECT *

Wenn Sie SELECT * verwenden, wird die Spaltenmenge als einzelne XML-Spalte zurückgegeben, anstatt als einzelne, spärliche Spalten.

# SELECT * returns column set instead of individual sparse columns
cursor.execute("""
    SELECT * FROM ProductAttributesWithSet WHERE ProductID = %(id)s
""", {"id": 3})

row = cursor.fetchone()
# Returns: ProductID, ProductName, SparseAttributes (not individual columns)
print(f"Columns: {[col[0] for col in cursor.description]}")

Einzelne Spalten explizit abfragen

Um einzelne spärliche Spalten aus einer Tabelle mit Spaltensatz abzurufen, listen Sie sie explizit in der SELECT-Klausel auf.

# To get individual sparse columns with column set table, list them explicitly
cursor.execute("""
    SELECT ProductID, ProductName, Color, Size, Weight, Material, Warranty, Manufacturer
    FROM ProductAttributesWithSet
    WHERE ProductID = %(id)s
""", {"id": 3})

# Now each sparse column is available as separate property
row = cursor.fetchone()
print(f"Color: {row.Color}, Material: {row.Material}")

Aktualisierung der sparseligen Spalten

Aktualisieren Sie einzelne spärliche Spalten nach Namen oder ersetzen Sie alle spärlichen Werte auf einmal über das Spaltenset XML.

Aktualisierung einzelner Spalten

Aktualisieren Sie spezifische, sparsame Spaltenwerte mit Standard-SQL-Syntax UPDATE .

cursor.execute("""
    UPDATE ProductAttributes
    SET Color = %(color)s, Weight = %(weight)s
    WHERE ProductID = %(id)s
""", {"id": 1, "color": "Red", "weight": 0.2})
conn.commit()

Aktualisierung über Spaltensatz

Ersetzen Sie alle spärlichen Spaltenwerte, indem Sie die XML-Spaltenmenge direkt aktualisieren.

# Replace all sparse column values via column set
cursor.execute("""
    UPDATE ProductAttributesWithSet
    SET SparseAttributes = %(xml)s
    WHERE ProductID = %(id)s
""", {
    "id": 3,
    "xml": "<Color>Navy</Color><Size>Large</Size><Material>Leather</Material>"
})
conn.commit()
# Note: This clears any sparse columns not included in the XML

Teilweise Aktualisierung über den Spaltensatz

Aktualisieren Sie nur spezifische sparse-Attribute, während die Werte anderer Attribute, die nicht im Update enthalten sind, erhalten bleiben.

# To update only specific attributes, merge with existing
def update_attributes(cursor, product_id: int, updates: dict):
    """Update specific sparse attributes while preserving others."""
    # Get current attributes
    cursor.execute("""
        SELECT Color, Size, Weight, Material, Warranty, Manufacturer
        FROM ProductAttributesWithSet
        WHERE ProductID = %(id)s
    """, {"id": product_id})
    
    row = cursor.fetchone()
    if row is None:
        raise ValueError(f"Product {product_id} not found")
    
    # Merge updates
    current = {
        "Color": row.Color,
        "Size": row.Size,
        "Weight": row.Weight,
        "Material": row.Material,
        "Warranty": row.Warranty,
        "Manufacturer": row.Manufacturer
    }
    
    for key, value in updates.items():
        current[key] = value
    
    # Build XML with non-null values
    xml_parts = []
    for key, value in current.items():
        if value is not None:
            xml_parts.append(f"<{key}>{value}</{key}>")
    
    cursor.execute("""
        UPDATE ProductAttributesWithSet
        SET SparseAttributes = %(xml)s
        WHERE ProductID = %(id)s
    """, {"id": product_id, "xml": "".join(xml_parts) or None})

# Usage
update_attributes(cursor, 3, {"Color": "Brown", "Warranty": 36})
conn.commit()

Dynamische Spaltenmuster

Entwickeln Sie flexible Abfragen, die dynamisch über spärliche Spalten hinweg suchen, indem Sie Allowlists verwenden, um Spaltennamen zu validieren und SQL-Injection-Angriffe zu verhindern.

Daten im EAV-Stil abfragen

Suchen Sie nach Produkten nach einem bestimmten Attributnamen und Wert mit Entity-Attribute-Value (EAV) Musterabgleich.

def find_products_by_attribute(cursor, attribute_name: str, attribute_value: str) -> list:
    """Find products with specific attribute value."""
    # Validate column name against allowed sparse columns to prevent SQL injection
    allowed_columns = {"Color", "Size", "Weight", "Material", "Warranty", "Manufacturer"}
    if attribute_name not in allowed_columns:
        raise ValueError(f"Invalid attribute: {attribute_name}")
    
    cursor.execute(f"""
        SELECT ProductID, ProductName, {attribute_name}
        FROM ProductAttributesWithSet
        WHERE {attribute_name} = %(value)s
    """, {"value": attribute_value})
    
    return cursor.fetchall()

# Find all blue products
blue_products = find_products_by_attribute(cursor, "Color", "Blue")

Flexible Attributsuche

Suchen Sie nach Produkten, die gleichzeitig mehrere optionale Attribute haben.

def search_by_attributes(cursor, **attributes) -> list:
    """Search products by multiple optional attributes."""
    # Validate column names against allowed sparse columns to prevent SQL injection
    allowed_columns = {"Color", "Size", "Weight", "Material", "Warranty", "Manufacturer"}
    invalid = set(attributes.keys()) - allowed_columns
    if invalid:
        raise ValueError(f"Invalid attributes: {invalid}")
    
    conditions = ["1=1"]  # Always true base condition
    params = {}
    
    for i, (key, value) in enumerate(attributes.items()):
        if value is not None:
            conditions.append(f"{key} = %(attr_{i})s")
            params[f"attr_{i}"] = value
    
    query = f"""
        SELECT ProductID, ProductName, SparseAttributes
        FROM ProductAttributesWithSet
        WHERE {' AND '.join(conditions)}
    """
    
    cursor.execute(query, params)
    return cursor.fetchall()

# Search by multiple attributes
results = search_by_attributes(cursor, Color="Black", Material="Nylon")

Leistungsüberlegungen

Bewerten Sie, wann spärliche Säulen Lagervorteile bieten, beurteilen Sie die Overheadkosten und nutzen Sie Leistungsüberwachung, um Ihr Design mit spärlicher Säule zu optimieren.

Wann man spärliche Spalten verwenden sollte

Gute Kandidaten für spärliche Kolumnen sind:

• Spalten mit mehr als 60–70% NULL-Werten.
• Breite Tabellen mit vielen optionalen Spalten.
• Arbeitslasten, bei denen Speicheroptimierung Priorität hat.

Vermeiden Sie die Verwendung spärlicher Spalten, wenn:

• Die meisten Zeilen haben Werte (jeder nicht-NULL-Wert fügt 4 Bytes Overhead hinzu).
• Die Spalte wird häufig in WHERE-Klauseln verwendet.
• Die Spalte ist Teil des Cluster-Indexes.

Überprüfen Sie die Speichereinsparungen

Vergleichen Sie die Speichergröße der spärlichen und nicht-spärlichen Versionen derselben Tabelle.

-- Compare storage with and without sparse
EXEC sp_spaceused 'ProductAttributes';
EXEC sp_spaceused 'ProductAttributesWithoutSparse';

Indexüberlegungen

Du kannst spärliche Spalten indexieren. Gefilterte Indizes funktionieren gut für spärliche Daten, weil sie NULL-Zeilen überspringen:

CREATE INDEX IX_Products_Color
ON ProductAttributes(Color)
WHERE Color IS NOT NULL;

Massenvorgänge

Optimieren Sie das Einfügen mehrerer Produkte mit Sparse-Spalten, indem Sie die XML für den Spaltensatz in Python erstellen, bevor Sie die Zeilen an bulkcopy() übergeben.

Masseneinsatz mit spärlichen Säulen

Verwenden Sie Bulk Copy, um effizient mehrere Produkte mit sparsen Attributen einzufügen.

def bulk_insert_with_attributes(conn, products: list[dict]):
    """Bulk insert products with sparse attributes."""
    rows = []
    for product in products:
        attrs = product.get("attributes", {})
        xml = "".join(f"<{k}>{v}</{k}>" for k, v in attrs.items()) or None
        rows.append((product["id"], product["name"], xml))
    
    cursor = conn.cursor()
    result = cursor.bulkcopy("ProductAttributesWithSet", rows)
    return result["rows_copied"]

# Usage
products = [
    {"id": 100, "name": "Widget A", "attributes": {"Color": "Red", "Size": "Small"}},
    {"id": 101, "name": "Widget B", "attributes": {"Weight": 1.5, "Material": "Steel"}},
    {"id": 102, "name": "Widget C", "attributes": {}},  # No sparse attributes
]
bulk_insert_with_attributes(conn, products)
conn.commit()

Bewährte Methoden

Folgen Sie Validierungsmustern, verwenden Sie Spaltensets für Flexibilität und überwachen Sie die Säulensparsamkeit, um sicherzustellen, dass Ihr Sparse-Column-Design Leistungs- und Wartbarkeitsziele erreicht.

Validiere spärliche Spaltenwerte

Überprüfen Sie, dass alle spärlichen Attribute mit der erlaubten Menge übereinstimmen, bevor Sie die Daten einfügen.

# Sparse columns have the same constraints as regular columns
# The SPARSE keyword only affects storage

def validate_and_insert(cursor, product_id: int, name: str, attributes: dict):
    """Insert with validation."""
    allowed_attributes = {"Color", "Size", "Weight", "Material", "Warranty", "Manufacturer"}
    
    invalid = set(attributes.keys()) - allowed_attributes
    if invalid:
        raise ValueError(f"Unknown attributes: {invalid}")
    
    xml_parts = [f"<{k}>{v}</{k}>" for k, v in attributes.items()]
    
    cursor.execute("""
        INSERT INTO ProductAttributesWithSet (ProductID, ProductName, SparseAttributes)
        VALUES (%(id)s, %(name)s, %(xml)s)
    """, {"id": product_id, "name": name, "xml": "".join(xml_parts) or None})

Verwenden Sie Spaltensätze für Flexibilität

Spaltenmengen vereinfachen die Arbeit mit spärlichen Spalten:

• Fügen Sie neue spärliche Spalten ohne Codeänderungen hinzu.
• Speichere dynamische Attribute.
• Automatische Serialisierung und Desserialisierung von XML.

Ohne Spaltensatz brauchst du explizite Spaltenlisten. Bei einem Spaltensatz verarbeitet die XML-Spalte dynamische Attribute automatisch.

Überwachen Sie den Anteil der NULL-Werte

Analysiere den NULL-Prozentsatz für eine Spalte, um festzustellen, ob sie ein guter Kandidat für eine Sparse-Column-Optimierung ist.

def analyze_sparseness(cursor, table: str, column: str) -> float:
    """Check if column is a good sparse candidate."""
    # Validate identifiers to prevent SQL injection
    import re
    if not re.match(r'^[A-Za-z_][A-Za-z0-9_.]*$', table):
        raise ValueError(f"Invalid table name: {table}")
    if not re.match(r'^[A-Za-z_][A-Za-z0-9_]*$', column):
        raise ValueError(f"Invalid column name: {column}")

    cursor.execute(f"""
        SELECT 
            COUNT(*) AS TotalRows,
            SUM(CASE WHEN {column} IS NULL THEN 1 ELSE 0 END) AS NullRows
        FROM {table}
    """)
    
    row = cursor.fetchone()
    null_percentage = (row.NullRows / row.TotalRows * 100) if row.TotalRows > 0 else 0
    
    print(f"Column {column}: {null_percentage:.1f}% NULL")
    print(f"Recommendation: {'Good sparse candidate' if null_percentage > 60 else 'Keep as regular column'}")
    
    return null_percentage

Verwandte Inhalte

• Datentypzuordnungen
• XML-Daten
• Massenkopieroperationen
• Microsoft SQL sparse columns