Ik moet een query schrijven om een grote lijst met ID’s op te halen.
We ondersteunen veel backds (Mysql, Firebird, SQLSERVER, Oracle, PostgreSQL …), dus ik moet een standaard SQL schrijven.
De grootte van de ID-set kan groot zijn, de query zou programmatisch worden gegenereerd. Dus, wat is de beste aanpak?
1) Schrijven van een query met behulp van
SELECT * FROM TABLE WHERE ID IN (id1, id2, ..., idn)
Mijn vraag hier is. Wat gebeurt er als n erg groot is? Ook, hoe zit het met de prestaties?
2) Een query schrijven met of
SELECT * FROM TABLE WHERE ID = id1 OR ID = id2 OR ... OR ID = idn
Ik denk dat deze aanpak geen n limiet heeft, maar hoe zit het met de uitvoering als n erg groot is?
3) Een programmatische oplossing schrijven:
foreach (var id in myIdList)
{
var item = GetItemByQuery("SELECT * FROM TABLE WHERE ID = " + id);
myObjectList.Add(item);
}
We hebben een aantal problemen ondervonden met deze aanpak wanneer de databaseserver wordt opgevraagd via het netwerk. Normaal gesproken is het beter om één query te doen die alle resultaten ophalen versus maken van veel kleine query’s. Misschien heb ik het mis.
Wat zou een juiste oplossing voor dit probleem zijn?
Antwoord 1, Autoriteit 100%
Optie 1 is de enige goede oplossing.
Waarom?
-
Optie 2 Doet hetzelfde, maar u herhaalt de kolomnaam veel keren; Bovendien weet de SQL-motor niet meteen dat u wilt controleren of de waarde een van de waarden in een vaste lijst is. Een goede SQL-motor kan echter optimaliseren om gelijke prestaties te hebben zoals met
IN. Er is echter nog steeds het leesbaarheidsprobleem … -
Optie 3 is gewoon vreselijke prestaties. Het verzendt een vraag elke lus en hamert de database met kleine query’s. Het voorkomt ook het gebruik van optimalisaties voor “waarde is een van die in een opgegeven lijst”
Antwoord 2, Autoriteit 24%
Een alternatieve benadering is mogelijk om een andere tabel te gebruiken om ID-waarden te bevatten. Deze andere tabel kan dan innerlijk worden toegevoegd aan uw tafel om geretourneerde rijen te beperken. Dit heeft het grote voordeel dat u geen dynamische SQL (problematisch op het beste tijden) nodig heeft, en u zult geen oneindig lang in de clausule hebben.
U zou deze andere tabel afknippen, uw grote aantal rijen invoegen en maakt dan misschien een index aan om de uitvoering van de prestaties te ondersteunen. Het laat je ook de accumulatie van deze rijen losmaken van het ophalen van gegevens, misschien het geven van je meer opties om de prestaties af te stemmen.
Update : Hoewel je een tijdelijke tafel zou kunnen gebruiken, wilde ik niet impliceren dat je moet of zelfs zou moeten. Een permanente tabel die wordt gebruikt voor tijdelijke gegevens is een gemeenschappelijke oplossing met verdiensten die hier worden beschreven.
Antwoord 3, Autoriteit 20%
Wat voorgestelde Guiness is echt een prestatiebooster, ik had een vraag als deze
select * from table where id in (id1,id2.........long list)
wat ik deed:
DECLARE @temp table(
ID int
)
insert into @temp
select * from dbo.fnSplitter('#idlist#')
Inner voegde binnen bij de temperatuur met de hoofdtabel:
select * from table inner join temp on temp.id = table.id
en prestaties verbeterden drastisch.
Antwoord 4, Autoriteit 9%
eerste optie is absoluut de beste optie.
SELECT * FROM TABLE WHERE ID IN (id1, id2, ..., idn)
Echter Gezien het feit dat de lijst met ID’s erg groot is , zeg dan miljoenen, moet u chunk-maten als hieronder overwegen:
- Verdeel u een lijst met ID’s in brokken van vast nummer, zeg 100
- Chunk Size moet worden beslist op basis van de geheugengrootte van uw server
- Stel dat u 10000 ID’s hebt, u hebt 10000/100 = 100 brokken
- Proces één brok tegelijkertijd resulterend in 100 database-oproepen voor select
Waarom zou je deelnemen aan brokken?
U krijgt nooit geheugenoverloop-uitzondering die zeer gebruikelijk is in scenario’s zoals de uwe.
U hebt een geoptimaliseerd aantal database-oproepen, wat resulteert in betere prestaties.
Het heeft altijd gewerkt als charme voor mij. Ik hoop dat het ook voor mijn mede-ontwikkelaars zou werken 🙂
Antwoord 5, Autoriteit 6%
Doe de SELECT * van MyTable, waar ID in () opdracht op een Azure SQL-tabel met 500 miljoen records resulteerde in een wachttijd van & GT; 7min!
Dit doen in plaats daarvan geretourneerde resultaten onmiddellijk:
select b.id, a.* from MyTable a
join (values (250000), (2500001), (2600000)) as b(id)
ON a.id = b.id
Gebruik een join.
Antwoord 6, Autoriteit 2%
In de meeste databasesystemen zijn IN (val1, val2, …)en een reeks ORgeoptimaliseerd voor hetzelfde plan.
De derde manier is het importeren van de lijst met waarden in een tijdelijke tabel en deze toevoegen, wat in de meeste systemen efficiënter is, als er veel waarden zijn.
Misschien wil je deze artikelen lezen:
Antwoord 7, autoriteit 2%
Ik denk dat je SqlServer bedoelt, maar op Oracle heb je een harde limiet voor het aantal IN-elementen dat je kunt specificeren: 1000.
Antwoord 8, autoriteit 2%
Voorbeeld 3 zou het slechtst presteren, omdat je de database ontelbare keren bezoekt zonder duidelijke reden.
Het laden van de gegevens in een tijdelijke tabel en vervolgens meedoen zou verreweg het snelst zijn. Daarna zou de IN iets sneller moeten werken dan de groep OK’s.
Antwoord 9
Probeer dit
SELECT Position_ID , Position_Name
FROM
position
WHERE Position_ID IN (6 ,7 ,8)
ORDER BY Position_Name