SQL WHERE ID IN (id1, id2, …, idn)

Devo scrivere una query per recuperare una grande lista di id.

Supportiamo molti backend (MySQL, Firebird, SQLServer, Oracle, PostgreSQL …) quindi ho bisogno di scrivere un SQL standard.

La dimensione del set ID potrebbe essere grande, la query verrebbe generata a livello di codice. Quindi, qual è l’approccio migliore?

1) Scrivere una query usando IN

SELECT * FROM TABLE WHERE ID IN (id1, id2, ..., idn)

La mia domanda qui è. Cosa succede se n è molto grande? Inoltre, per quanto riguarda le prestazioni?

2) Scrivere una query usando OR

 SELECT * FROM TABLE WHERE ID = id1 OR ID = id2 OR ... OR ID = idn

Penso che questo approccio non abbia un limite, ma per quanto riguarda le prestazioni se n è molto grande?

3) Scrivere una soluzione programmatica:

  foreach (id in myIdList) { item = GetItemByQuery("SELECT * FROM TABLE WHERE ID = " + id); myObjectList.Add(item); }

Abbiamo riscontrato alcuni problemi con questo approccio quando il server del database viene interrogato sulla rete. Normalmente è meglio fare una query che recuperi tutti i risultati, meglio di molte piccole query. Forse sto sbagliando.

Quale sarebbe una soluzione corretta per questo problema?

L’opzione 1 è l’unica buona soluzione.

Perché?

L’opzione 2 fa lo stesso, ma ripetete il nome della colonna molte volte; Inoltre, il motore SQL non riconosce immediatamente che si desidera controllare se il valore è uno dei valori in una lista fissa. Tuttavia, un buon motore SQL potrebbe ottimizzarlo per avere prestazioni uguali come con IN . Rimane comunque il problema di leggibilità …
L’opzione 3 è semplicemente orribile in termini di prestazioni. Invia una query ad ogni ciclo e martella il database con piccole query. Impedisce inoltre di utilizzare eventuali ottimizzazioni per “il valore è uno di quelli in una data lista”

Un approccio alternativo potrebbe essere quello di utilizzare un’altra tabella per contenere valori di identificazione. Questa altra tabella può quindi essere unita internamente su TABLE per vincolare le righe restituite. Ciò avrà il grande vantaggio che non avrai bisogno di SQL dinamico (problematico nel migliore dei casi) e non avrai una clausola IN infinitamente lunga.

Dovresti troncare quest’altra tabella, inserire un numero elevato di righe, quindi creare un indice per facilitare il rendimento del join. Ti consente anche di staccare l’accumulo di queste righe dal recupero dei dati, offrendoti forse più opzioni per ottimizzare le prestazioni.

Aggiornamento : Sebbene tu possa usare una tabella temporanea, non intendevo implicare che tu debba o anche dovresti. Una tabella permanente utilizzata per i dati temporanei è una soluzione comune con pregi oltre a quella descritta qui.

Quello che suggeriva Ed Guiness è davvero un richiamo alle prestazioni, ho avuto una domanda come questa

 select * from table where id in (id1,id2.........long list)

cosa ho fatto :

  DECLARE @temp table( ID int ) insert into @temp select * from dbo.fnSplitter('#idlist#')

Quindi interno unito alla temp con la tabella principale:

 select * from table inner join temp on temp.id = table.id

E le prestazioni sono migliorate drasticamente.

La prima opzione è sicuramente l’opzione migliore.

 SELECT * FROM TABLE WHERE ID IN (id1, id2, ..., idn)

Tuttavia considerando che la lista di id è molto grande , ad esempio milioni, dovresti considerare le dimensioni del chunk come di seguito:

Dividi la tua lista di Id in blocchi di numero fisso, diciamo 100
La dimensione del blocco deve essere decisa in base alle dimensioni della memoria del server
Supponete di avere 10000 ID, avrete 10000/100 = 100 blocchi
Elaborare un chunk alla volta risultante in 100 chiamate al database per selezionare

Perché dovresti dividerti in pezzi?

Non riceverai mai un’eccezione di overflow di memoria che è molto comune in scenari come il tuo. Otterrai un numero ottimizzato di chiamate al database con prestazioni migliori.

Ha sempre funzionato come un fascino per me. Spero che possa funzionare anche per i miei colleghi sviluppatori 🙂

Nella maggior parte dei sistemi di database, IN (val1, val2, …) e una serie di OR sono ottimizzati per lo stesso piano.

Il terzo modo sarebbe importare la lista di valori in una tabella temporanea e unirla, che è più efficiente nella maggior parte dei sistemi, se ci sono molti valori.

Puoi leggere questo articolo:

Passare i parametri in MySQL: lista IN e tabella temporanea

Il campione 3 sarebbe il peggiore risultato di tutti perché si sta colpendo il database innumerevoli volte senza un motivo apparente.

Caricare i dati in una tabella temporanea e poi unirsi a quelli sarebbe di gran lunga il più veloce. Successivamente, l’IN dovrebbe funzionare leggermente più velocemente rispetto al gruppo di OR.

Penso che tu intenda SqlServer ma su Oracle hai un limite difficile quanti elementi IN puoi specificare: 1000.

Facendo SELECT * da MyTable dove il comando id in () su una tabella SQL di Azure con 500 milioni di record ha comportato un tempo di attesa di> 7min!

In questo caso, invece, restituiva immediatamente i risultati:

 select b.id, a.* from MyTable a join (values (250000), (2500001), (2600000)) as b(id) ON a.id = b.id

Usa un join.