Skip to content

Individuare il più piccolo elemento mancante in base a una formula specifica

Abbiamo le migliori informazioni che abbiamo scoperto online. Vogliamo che ti aiuti e se vuoi raccontarci qualche dettaglio che può aiutarci a crescere, fallo liberamente.

Soluzione:

Questa domanda presenta alcune difficoltà. Gli indici in SQL Server possono eseguire le seguenti operazioni in modo molto efficiente con poche letture logiche:

  • verificare l'esistenza di una riga
  • controllare che una riga non esista
  • trovare la riga successiva a partire da un certo punto
  • trova la riga precedente a partire da un certo punto

Tuttavia, non possono essere utilizzati per trovare l'ennesima riga di un indice. Per farlo, è necessario creare un proprio indice memorizzato come tabella o eseguire la scansione delle prime N righe dell'indice. Il vostro codice C# si basa molto sul fatto che potete trovare in modo efficiente il nono elemento dell'array, ma non potete farlo qui. Penso che questo algoritmo non sia utilizzabile per T-SQL senza una modifica del modello di dati.

La seconda sfida riguarda le restrizioni sull'array BINARY tipi di dati. Per quanto ne so, non è possibile eseguire addizioni, sottrazioni o divisioni nei modi consueti. È possibile convertire i dati BINARY(64) in un BIGINT e non si verificheranno errori di conversione, ma il comportamento non è definito:

Le conversioni tra qualsiasi tipo di dato e i tipi di dati binari non sono
garantito che siano uguali tra le versioni di SQL Server.

Inoltre, la mancanza di errori di conversione è un po' un problema. È possibile convertire qualsiasi cosa più grande del più grande possibile BIGINT ma si otterranno risultati sbagliati.

È vero che in questo momento avete valori più grandi di 9223372036854775807. Tuttavia, se si parte sempre da 1 e si cerca il valore minimo più piccolo, questi valori grandi non possono essere rilevanti a meno che la tabella non abbia più di 9223372036854775807 righe. Questo sembra improbabile, perché la vostra tabella a quel punto sarebbe di circa 2000 exabyte, quindi per rispondere alla vostra domanda assumerò che i valori molto grandi non debbano essere cercati. Farò anche la conversione dei tipi di dati perché sembra inevitabile.

Per i dati di prova, ho inserito l'equivalente di 50 milioni di numeri interi sequenziali in una tabella insieme ad altri 50 milioni di numeri interi con uno scarto di un singolo valore ogni 20 valori circa. Ho anche inserito un singolo valore che non si adatta correttamente a una tabella firmata. BIGINT:

CREATE TABLE dbo.BINARY_PROBLEMS (
    KeyCol BINARY(64) NOT NULL
);

INSERT INTO dbo.BINARY_PROBLEMS WITH (TABLOCK)
SELECT CAST(SUM(OFFSET) OVER (ORDER BY (SELECT NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BINARY(64))
FROM
(
    SELECT 1 + CASE WHEN t.RN > 50000000 THEN
        CASE WHEN ABS(CHECKSUM(NewId()) % 20)  = 10 THEN 1 ELSE 0 END
    ELSE 0 END OFFSET
    FROM
    (
        SELECT TOP (100000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
        FROM master..spt_values t1
        CROSS JOIN master..spt_values t2
        CROSS JOIN master..spt_values t3
    ) t
) tt
OPTION (MAXDOP 1);

CREATE UNIQUE CLUSTERED INDEX CI_BINARY_PROBLEMS ON dbo.BINARY_PROBLEMS (KeyCol);

-- add a value too large for BIGINT
INSERT INTO dbo.BINARY_PROBLEMS
SELECT CAST(0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000008000000000000000 AS BINARY(64));

Il codice ha richiesto alcuni minuti per essere eseguito sulla mia macchina. Ho fatto in modo che la prima metà della tabella non avesse spazi vuoti per rappresentare una sorta di caso peggiore per le prestazioni. Il codice che ho usato per risolvere il problema esegue la scansione dell'indice in ordine, quindi terminerà molto rapidamente se il primo spazio vuoto si trova all'inizio della tabella. Prima di arrivare a questo punto, verifichiamo che i dati siano come dovrebbero essere:

SELECT TOP (2) KeyColBigInt
FROM
(
    SELECT KeyCol
    , CAST(KeyCol AS BIGINT) KeyColBigInt
    FROM dbo.BINARY_PROBLEMS
) t
ORDER By KeyCol DESC;

I risultati suggeriscono che il valore massimo che convertiamo in BIGINT è 102500672:

╔══════════════════════╗
║     KeyColBigInt     ║
╠══════════════════════╣
║ -9223372036854775808 ║
║            102500672 ║
╚══════════════════════╝

Ci sono 100 milioni di righe con valori che si adattano a BIGINT come previsto:

SELECT COUNT(*) 
FROM dbo.BINARY_PROBLEMS
WHERE KeyCol < 0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007FFFFFFFFFFFFFFF;

Un approccio a questo problema è quello di eseguire la scansione dell'indice in ordine e di abbandonare non appena il valore di una riga non corrisponde a quello previsto ROW_NUMBER() atteso. Non è necessario scansionare l'intera tabella per ottenere la prima riga, ma solo le righe fino alla prima lacuna. Ecco un modo per scrivere codice che probabilmente otterrà questo piano di query:

SELECT TOP (1) KeyCol
FROM
(
    SELECT KeyCol
    , CAST(KeyCol AS BIGINT) KeyColBigInt
    , ROW_NUMBER() OVER (ORDER BY KeyCol) RN
    FROM dbo.BINARY_PROBLEMS
    WHERE KeyCol < 0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007FFFFFFFFFFFFFFF
) t
WHERE KeyColBigInt <> RN
ORDER BY KeyCol;

Per ragioni che non rientrano in questa risposta, questa query viene spesso eseguita in serie da SQL Server e SQL Server spesso sottostima il numero di righe che devono essere scansionate prima che venga trovata la prima corrispondenza. Sul mio computer, SQL Server esegue la scansione di 50000022 righe dall'indice prima di trovare la prima corrispondenza. L'esecuzione della query richiede 11 secondi. Si noti che viene restituito il primo valore dopo lo scarto. Non è chiaro quale riga si desideri esattamente, ma si dovrebbe essere in grado di modificare la query per adattarla alle proprie esigenze senza troppi problemi. Ecco come appare il piano:

piano seriale

L'unica altra idea che ho avuto è stata quella di convincere SQL Server a utilizzare il parallelismo per la query. Ho quattro CPU, quindi dividerò i dati in quattro intervalli e farò ricerche su questi intervalli. A ogni CPU verrà assegnato un intervallo. Per calcolare gli intervalli ho semplicemente preso il valore massimo e ho assunto che i dati fossero distribuiti in modo uniforme. Se si vuole essere più intelligenti, si può guardare a un istogramma di statistiche campionate per i valori delle colonne e costruire gli intervalli in questo modo. Il codice sottostante si basa su molti trucchi non documentati che non sono sicuri per la produzione, compreso il flag di traccia 8649:

SELECT TOP 1 ca.KeyCol
FROM (
    SELECT 1 bucket_min_value, 25625168 bucket_max_value
    UNION ALL
    SELECT 25625169, 51250336
    UNION ALL
    SELECT 51250337, 76875504
    UNION ALL
    SELECT 76875505, 102500672
) buckets
CROSS APPLY (
    SELECT TOP 1 t.KeyCol
    FROM
    (
        SELECT KeyCol
        , CAST(KeyCol AS BIGINT) KeyColBigInt
        , buckets.bucket_min_value - 1 + ROW_NUMBER() OVER (ORDER BY KeyCol) RN
        FROM dbo.BINARY_PROBLEMS
        WHERE KeyCol >= CAST(buckets.bucket_min_value AS BINARY(64)) AND KeyCol <=  CAST(buckets.bucket_max_value AS BINARY(64))
    ) t
    WHERE t.KeyColBigInt <> t.RN
    ORDER BY t.KeyCol
) ca
ORDER BY ca.KeyCol
OPTION (QUERYTRACEON 8649);

Ecco come appare lo schema del ciclo parallelo annidato:

piano parallelo

Nel complesso, la query fa più lavoro di prima, poiché scansiona più righe nella tabella. Tuttavia, ora viene eseguita in 7 secondi sul mio desktop. Potrebbe funzionare meglio in parallelo su un server reale. Ecco un link al piano effettivo.

Non riesco a pensare a un buon modo per risolvere questo problema. Fare il calcolo al di fuori di SQL o cambiare il modello di dati potrebbe essere la soluzione migliore.

Joe ha già affrontato la maggior parte dei punti che ho appena trascorso un'ora a scrivere, in sintesi:

  • è altamente improbabile che si finisca mai di KeyCol valori < bigint max (9,2e18), quindi le conversioni (se necessarie) da/verso bigint non dovrebbero essere un problema, purché si limitino le ricerche a KeyCol <= 0x00..007FFFFFFFFFFFFFFF
  • Non riesco a pensare a una query che riesca a trovare in modo "efficiente" una lacuna per tutto il tempoe; si può essere fortunati e trovare una lacuna all'inizio della ricerca, oppure si può pagare a caro prezzo per trovare la lacuna molto avanti nella ricerca.
  • Sebbene abbia pensato brevemente a come parallelizzare la query, ho rapidamente scartato l'idea (come DBA non vorrei scoprire che il vostro processo sta di routine impantanando il mio dataserver con un utilizzo del 100% della cpu... specialmente se potete avere più copie di questa query in esecuzione allo stesso tempo); noooo... la parallelizzazione è fuori questione

Quindi, cosa fare?

Mettiamo da parte l'idea della ricerca (ripetuta, ad alta intensità di cpu, a forza bruta) per un minuto e guardiamo al quadro generale.

  • in media, un'istanza di questa ricerca dovrà scansionare milioni di chiavi dell'indice (e richiederà un bel po' di cpu, il lavaggio della cache del db e un utente che guarda una clessidra che gira) solo per localizzare un singolo valore
  • moltiplicare l'uso della cpu, il lavaggio della cache e la lente d'ingrandimento per ... quante ricerche ci si aspetta in un giorno?
  • Tenete presente che, in generale, ogni di questa ricerca dovrà scansionare il file stesso insieme di (milioni di) chiavi di indice; è una TANTISSIMO di attività ripetute per un beneficio minimo

Quello che vorrei proporre sono alcune aggiunte al modello dei dati ...

  • una nuova tabella che tenga traccia di un insieme di "disponibili all'uso". KeyCol valori, ad esempio available_for_use(KeyCol binary(64) not null primary key)
  • Il numero di record da mantenere in questa tabella è a discrezione dell'utente, ad esempio: forse abbastanza per un mese di attività?
  • la tabella può essere periodicamente (settimanalmente?) "rabboccata" con un nuovo lotto di dati. KeyCol valori (forse creando un processo memorizzato "top off"?) [eg, update Joe's select/top/row_number() query to do a top 100000]
  • si potrebbe impostare un processo di monitoraggio per tenere traccia del numero di voci disponibili in available_for_useper ogni evenienza si cominci a esaurire i valori
  • un nuovo (o modificato) trigger DELETE sulla >tabella_principale< che inserisce i dati cancellati KeyCol nella nostra nuova tabella available_for_use ogni volta che una riga viene cancellata dalla tabella principale
  • se si consente l'aggiornamento della tabella KeyCol allora un trigger UPDATE nuovo/modificato sulla >tabella_principale< per mantenere anche la nostra nuova tabella available_for_use aggiornata
  • quando arriva il momento di 'cercare' una nuova colonna KeyCol valore si select min(KeyCol) from available_for_use (ovviamente c'è un po' di più da fare, dato che a) è necessario codificare per i problemi di concorrenza: non si vogliono due copie del processo che afferrano lo stesso valore min(KeyCol) e b) si dovrà cancellare min(KeyCol) dalla schedae; Questo dovrebbe essere relativamente facile da codificare, forse come stored proc, e può essere affrontato in un'altra Q&A se necessario)
  • nel peggiore dei casi, se il vostro select min(KeyCol) non trova righe disponibili, si potrebbe avviare il processo "top off" per generare un nuovo gruppo di righe.

Con queste modifiche proposte al modello dei dati:

  • si elimina un processo LOTTO di cicli cpu eccessivi [your DBA will thank you]
  • si eliminano TUTTO di quelle scansioni ripetitive degli indici e di quelle operazioni sulla cache [your DBA will thank you]
  • i vostri utenti non dovranno più guardare la lente d'ingrandimento (anche se non apprezzeranno la perdita di una scusa per allontanarsi dalla loro scrivania)
  • ci sono molti modi per monitorare le dimensioni del file available_for_use per assicurarsi di non esaurire mai i nuovi valori

Sì, la proposta available_for_use è solo una tabella di valori pre-generati della "chiave successiva"; e sì, c'è un potenziale di contesa quando si prende il valore "successivo", ma qualsiasi contesa a) è facilmente risolvibile attraverso una corretta progettazione di tabelle/indici/query e b) sarà minore/di breve durata rispetto al sovraccarico/ritardo con l'idea attuale di ricerche ripetute, a forza bruta, negli indici.

Puoi supportare la nostra missione aggiungendo un commento e valutandolo, ti saremo eternamente grati.



Utilizzate il nostro motore di ricerca

Ricerca
Generic filters

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.