Waarom is. Bevat langzaam? Meest efficiënte manier om meerdere entiteiten op primaire sleutel te krijgen?

Wat is de meest efficiënte manier om meerdere entiteiten per primaire sleutel te selecteren?

public IEnumerable GetImagesById(IEnumerable ids)
{

    //return ids.Select(id => Images.Find(id));       //is this cool?
    return Images.Where( im => ids.Contains(im.Id));  //is this better, worse or the same?
    //is there a (better) third way?

}

Ik realiseer me dat ik een aantal prestatietests zou kunnen doen om te vergelijken, maar ik vraag me af of er eigenlijk een betere manier is dan beide, en ben op zoek naar enige verlichting over wat het verschil tussen deze twee vragen is, als die er zijn, als ze eenmaal zijn geweest 'vertaald'.

52
Welnu, dat is onbepaald, meestal geen enorm aantal, maar zou graag grote aantallen op een schaalbare manier kunnen ondersteunen.
toegevoegd de auteur Tom, de bron
Ik heb de vrijheid genomen om uw vraag opnieuw te titelleren om het gebruikersvriendelijker te maken om mensen te zoeken voor een snellere vervanging van . bevat .
toegevoegd de auteur spender, de bron
Man, waarom blijf ik terugkomen op deze vraag ...?
toegevoegd de auteur spender, de bron
Merk op dat dit is opgelost in EF 6 - zie de 5de bullet - blogs.msdn.com/b/adonet/archive/2012/12/10/…
toegevoegd de auteur Ian Gregory, de bron
toegevoegd de auteur Gert Arnold, de bron

5 antwoord

UPDATE: met de toevoeging van InExpression in EF6 zijn de prestaties van het verwerken van Enumerable.Contains drastisch verbeterd. De analyse in dit antwoord is geweldig maar grotendeels verouderd sinds 2013.

Het gebruik van Contains in Entity Framework is eigenlijk heel traag. Het klopt dat het zich vertaalt in een IN -clausule in SQL en dat de SQL-query zelf snel wordt uitgevoerd. Maar het probleem en het knelpunt in de prestaties zit in de vertaling van uw LINQ-query in SQL. De expressiestructuur die wordt gemaakt, wordt uitgebreid tot een lange keten van OR aaneenschakelingen omdat er geen native expressie is die een IN vertegenwoordigt. Wanneer de SQL wordt gemaakt, wordt deze uitdrukking van veel OF s herkend en samengevouwen in de SQL IN -clausule.

Dit betekent niet dat het gebruik van bevat slechter is dan het uitvaardigen van één zoekopdracht per element in uw ids verzameling (uw eerste optie). Het is waarschijnlijk nog beter - in ieder geval voor niet al te grote collecties. Maar voor grote collecties is het echt slecht. Ik herinner me dat ik een tijd geleden een Contains -query had getest met ongeveer 12.000 elementen die werkte maar ongeveer een minuut duurde, hoewel de query in SQL in minder dan een seconde werd uitgevoerd.

Het is misschien de moeite waard om de prestaties van een combinatie van meerdere roundtrips naar de database te testen met een kleiner aantal elementen in een bevat -uitdrukking voor elke roundtrip.

Deze aanpak en ook de beperkingen van het gebruik van Contains met Entity Framework wordt hier getoond en uitgelegd:

Why does the Contains() operator degrade Entity Framework's performance so dramatically?

It's possible that a raw SQL command will perform best in this situation which would mean that you call dbContext.Database.SqlQuery(sqlString) or dbContext.Images.SqlQuery(sqlString) where sqlString is the SQL shown in @Rune's answer.

Bewerken

Hier zijn enkele metingen:

Ik heb dit gedaan op een tabel met 550000 records en 11 kolommen (ID's beginnen bij 1 zonder hiaten) en willekeurig 20000 ID's gekozen:

using (var context = new MyDbContext())
{
    Random rand = new Random();
    var ids = new List();
    for (int i = 0; i < 20000; i++)
        ids.Add(rand.Next(550000));

    Stopwatch watch = new Stopwatch();
    watch.Start();

   //here are the code snippets from below

    watch.Stop();
    var msec = watch.ElapsedMilliseconds;
}

Test 1

var result = context.Set()
    .Where(e => ids.Contains(e.ID))
    .ToList();

Result -> msec = 85.5 sec

Test 2

var result = context.Set().AsNoTracking()
    .Where(e => ids.Contains(e.ID))
    .ToList();

Result -> msec = 84.5 sec

Dit kleine effect van AsNoTracking is zeer ongebruikelijk. Het geeft aan dat het knelpunt geen materialisatie van objecten is (en geen SQL, zoals hieronder wordt getoond).

Voor beide tests is in SQL Profiler te zien dat de SQL-query erg laat in de database arriveert. (Ik heb niet precies gemeten, maar het was later dan 70 seconden.) Uiteraard is de vertaling van deze LINQ-query in SQL erg duur.

Test 3

var values = new StringBuilder();
values.AppendFormat("{0}", ids[0]);
for (int i = 1; i < ids.Count; i++)
    values.AppendFormat(", {0}", ids[i]);

var sql = string.Format(
    "SELECT * FROM [MyDb].[dbo].[MyEntities] WHERE [ID] IN ({0})",
    values);

var result = context.Set().SqlQuery(sql).ToList();

Result -> msec = 5.1 sec

Test 4

// same as Test 3 but this time including AsNoTracking
var result = context.Set().SqlQuery(sql).AsNoTracking().ToList();

Result -> msec = 3.8 sec

Deze keer is het effect van het uitschakelen van tracking merkbaarder.

Test 5

// same as Test 3 but this time using Database.SqlQuery
var result = context.Database.SqlQuery(sql).ToList();

Result -> msec = 3.7 sec

My understanding is that context.Database.SqlQuery(sql) is the same as context.Set().SqlQuery(sql).AsNoTracking(), so there is no difference expected between Test 4 and Test 5.

(De lengte van de resultatensets was niet altijd hetzelfde vanwege mogelijke duplicaten na de willekeurige id-selectie, maar het was altijd tussen 19600 en 19640 elementen.)

Bewerken 2

Test 6

Zelfs 20000 roundtrips naar de database zijn sneller dan het gebruik van Contains :

var result = new List();
foreach (var id in ids)
    result.Add(context.Set().SingleOrDefault(e => e.ID == id));

Result -> msec = 73.6 sec

Merk op dat ik SingleOrDefault heb gebruikt in plaats van Zoeken . Het gebruik van dezelfde code met Zoeken verloopt erg traag (ik heb de test na enkele minuten geannuleerd) omdat Find intern DetectChanges aanroept. Het uitschakelen van de detectie van automatische wijzigingen ( context.Configuration.AutoDetectChangesEnabled = false ) leidt tot ongeveer dezelfde prestaties als SingleOrDefault . Het gebruik van AsNoTracking verkort de tijd met één of twee seconden.

Tests werden uitgevoerd met een databaseclient (console-app) en databaseserver op dezelfde computer. Het laatste resultaat kan aanzienlijk slechter worden met een "externe" database vanwege de vele roundtrips.

122
toegevoegd
Goed antwoord. Waarschijnlijk is het enige dat ik daaraan zou toevoegen ... Als dit soort dingen vermeden kunnen worden door er omheen te ontwerpen, dan zou dat waarschijnlijk zo moeten zijn. Ben je het er mee eens?
toegevoegd de auteur Tom, de bron
Ik begin te denken dat dit type query, idealiter gezien vanuit het oogpunt van prestaties, eenvoudig moet worden vermeden.
toegevoegd de auteur Tom, de bron
Heel erg bedankt.
toegevoegd de auteur Yaron Levi, de bron
Dit antwoord is geweldig. Grote moeite.
toegevoegd de auteur spender, de bron
@ThisGuy: Ja, bedankt, dat is veel beter!
toegevoegd de auteur Slauma, de bron
@Tom: ik heb wat tests gedaan, zie mijn bewerking.
toegevoegd de auteur Slauma, de bron
@Tom: Ik heb een testnummer 6 gedaan, zie mijn bewerking 2. Ja, het vermijden van deze situatie lijkt in de eerste plaats een goede strategie. Maar soms heb je misschien zo'n vraag nodig en kan je deze niet omzeilen. Mijn conclusie is meer: ​​soms is het gebruik van onbewerkte SQL zinvol of zelfs een MUST om prestatieredenen met een ORM zoals Entity Framework.
toegevoegd de auteur Slauma, de bron
@Tom: +1 voor je vraag nu, ik ben het vergeten. Omdat ik veel heb geleerd en het me geholpen heeft om een ​​serieus prestatieknelpunt in een eigen applicatie te detecteren. Bedankt voor de vraag :)
toegevoegd de auteur Slauma, de bron
Geweldig antwoord en onderzoek. Een superkleinaardige opmerking, maar je kunt de lijst met ID's in Test 3 op één regel als volgt bouwen: stringwaarden = string.Join (",", id's);
toegevoegd de auteur ThisGuy, de bron
Een loop draaien was voor mij sneller
toegevoegd de auteur stack, de bron

De tweede optie is zeker beter dan de eerste. De eerste optie resulteert in ids.Length -query's voor de database, terwijl de tweede optie een 'IN' -gebruiker in de SQL-query kan gebruiken. Het zal in principe uw LINQ-query veranderen in iets als de volgende SQL:

SELECT *
FROM ImagesTable
WHERE id IN (value1,value2,...)

waar value1, value2 etc. de waarden zijn van je ids-variabele. Houd er echter rekening mee dat ik denk dat er een bovengrens kan zijn aan het aantal waarden dat op deze manier in een query kan worden geplaatst. Ik zal kijken of ik documentatie kan vinden ...

4
toegevoegd
Bedankt, is dit de manier om te gaan, denk je dan? Of is er een alternatieve benadering?
toegevoegd de auteur Tom, de bron
Zou ik gelijk hebben als ik concludeer dat dit soort vragen een (kleine) code-geur heeft?
toegevoegd de auteur Tom, de bron
Hoeveel tijd en wat was er precies> 4096? Is er een betere algemene benadering van deze klasse van problemen, vraag ik me af?
toegevoegd de auteur Tom, de bron
@Tim: Dit is absoluut de manier om te gaan. Zolang je op EF 4+ zit, kun je dit gebruiken, en krijg je een enkele fetch naar de db ...
toegevoegd de auteur Reed Copsey, de bron
@Tom: Ja, er is een betere algemene benadering - parameters met parameters die gewaardeerd worden. Maar dat vereist het schrijven van opgeslagen procedures en wordt niet ondersteund in LINQ-naar-SQL of EF, plus het heeft zijn eigen kwalen (bijvoorbeeld het cachen van een ongeschikt queryplan).
toegevoegd de auteur Allon Guralnek, de bron
Er is geen limiet in EF omdat het dit converteert naar een niet-geparametriseerde IN-expressie ( kolom IN (val1, val2, val3, ...) ) terwijl LINQ-naar-SQL alle IN-waarden parametreert ( kolom IN (@ p1, @ p2, @ p3, ...) ) en dus raak je de 2100-parameterlimiet vrij snel.
toegevoegd de auteur Allon Guralnek, de bron
Ik denk dat dit de juiste weg is - ik ben me niet bewust van een betere aanpak. Ik zal kijken of ik die maximale grootte ergens kan vinden. Als het bestaat, kunt u ID's gewoon verdelen in blokken van de juiste grootte. Als bekend is dat uw verzameling id's klein is, kunt u het probleem mogelijk zelfs negeren. Ik geloof dat de limiet iets van 512 of 1024 is ...
toegevoegd de auteur Rune, de bron
Hmm, kan die bovengrens nergens vinden. Misschien vergis ik me en was het alleen van toepassing op Linq2SQL. Ik promoot iemand die de bovengrens documenteert (mogelijk het ontbreken van) :-)
toegevoegd de auteur Rune, de bron
Ik heb dit getest met> 4096 inzendingen in de verzameling. Het werkt, hoewel het serialiseren van de inzendingen een niet-triviale hoeveelheid tijd kost.
toegevoegd de auteur Rune, de bron

Ik gebruik Entity Framework 6.1 en ben erachter gekomen met behulp van uw code die beter te gebruiken is:

return db.PERSON.Find(id);

liever dan:

return db.PERSONA.FirstOrDefault(x => x.ID == id);

Performance of Find() vs. FirstOrDefault are some thoughts on this.

1
toegevoegd
zie ook stackoverflow.com/questions/11686225/… vooral de reacties onderaan
toegevoegd de auteur Tom, de bron
uitstekende informatie Tom, het was precies wat ik zocht
toegevoegd de auteur Juanito, de bron

Het transformeren van de lijst naar een array met toArray() verhoogt de prestaties. Je kunt het op deze manier doen:

ids.Select(id => Images.Find(id));     
    return Images.toArray().Where( im => ids.Contains(im.Id));  
1
toegevoegd
Sorry, maar dit klopt niet. Images.toArray() haalt de tabel hele Afbeeldingen in het geheugen. Dat meen je niet echt. Het is ook niet duidelijk wat de eerste regel daar doet.
toegevoegd de auteur Gert Arnold, de bron

Weel, recentelijk heeft een soortgelijk probleem en de beste manier die ik vond was de lijst met bevat in een tijdelijke tabel invoegen en na een join.

private List GetFoos(IEnumerable ids)
{
    var sb = new StringBuilder();
    sb.Append("DECLARE @Temp TABLE (Id bitint PRIMARY KEY)\n");

    foreach (var id in ids)
    {
        sb.Append("INSERT INTO @Temp VALUES ('");
        sb.Append(id);
        sb.Append("')\n");
    }

    sb.Append("SELECT f.* FROM [dbo].[Foo] f inner join @Temp t on f.Id = t.Id");

    return this.context.Database.SqlQuery(sb.ToString()).ToList();
}

Het is geen mooie manier, maar voor grote lijsten is het zeer performant.

0
toegevoegd