[C#/LINQ/MSSQL] orderby query ontzettend traag

[PdeBie]

Hoi allemaal,

ik zit met de volgende situatie. Ik heb een gridview van orders (met paging) die per pagina 15 orders laat zien.
Dit gridview wordt gevuld met data (via LINQ) uit een MSSQL database.

Omwille van functionele eisen moet er een bepaalde sortering aan orders hangen op basis van status, bezorgdatum en ID.

De orders worden nu als volgt opgehaald:

C#:

IQueryable<OrderHead> orders = from oh in dc.orders
where (oh.Receiver == memberID || oh.Sender == memberID)
orderby 
  (oh.Status == 1 && oh.Receiver == memberID ? 1 :
  oh.Status == 3 && oh.Receiver == memberID ? 1 :
  oh.Status == 5 && oh.Sender == memberID ? 0 :
  oh.Status == 6 && oh.Sender == memberID ? 0 :
  oh.Status == 7 && oh.Sender == memberID ? 0 :
  oh.Status == 8 && oh.Sender == memberID ? 0 :
  oh.Status == 20 ? 99 : 90), 
  oh.DeliveryDate ascending, oh.ID ascending
select oh;

return orders.Skip(startRowIndex).Take(maximumRows)

Het duurt nu echter +/- 33 seconden om 15 orders (=maximumRows) op te halen.
Haal ik de sortering op basis van status weg en laat ik de bezorgdatum en id sortering staan, duurt het nog 'maar' 6 seconden. (Nog te lang naar mijn mening, maar dat is van latere zorg).

De query die wordt opgebouwd ziet er als volgt uit:

SQL:

SELECT ...kolommen...
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY 
        (CASE 
            WHEN ([t1].[Status] = 1) AND ([t1].[Receiver] = 8888) THEN 1
            WHEN ([t1].[Status] = 3) AND ([t1].[Receiver] = 8888) THEN 1
            WHEN ([t1].[Status] = 5) AND ([t1].[Sender] = 8888) THEN 0
            WHEN ([t1].[Status] = 6) AND ([t1].[Sender] = 8888) THEN 0
            WHEN ([t1].[Status] = 7) AND ([t1].[Sender] = 8888) THEN 0
            WHEN ([t1].[Status] = 8) AND ([t1].[Sender] = 8888) THEN 0
            WHEN [t1].[Status] = 20 THEN 99
            ELSE 90
         END), [t1].DeliveryDate, [t1].ID, [t1.ID]) AS [ROW_NUMBER], ...kolommen...
    FROM (
        SELECT DISTINCT ...kolommen...
        FROM [dbo].[Orders] AS [t0]
        WHERE (([t0].[Receiver] = 8888) OR ([t0].[Sender] = 8888))
        ) AS [t1]
    ) AS [t2]
WHERE [t2].[ROW_NUMBER] BETWEEN 1 AND 15
ORDER BY [t2].[ROW_NUMBER]

Ik heb een index op ID staan (primary key) en op deliverydate. Een index op status lijkt de boel alleen maar te vertragen.

Iemand een idee hoe ik hier een flinke performance winst uit kan halen? Want 33 seconden is simpelweg niet acceptabel.
En moet ik de DBML elke keer vernieuwen op het moment dat ik een index toevoeg?

[HMS]

Probeer eens een index of 'Status' & 'Sender' en een andere index op 'Status' & 'Receiver'.

Maar ik denk toch dat je misschien beter de uiteindelijke ordening kunt berekenen in je code en die dan toepassen op je LINQ query.

[PdeBie]

HMS schreef op dinsdag 10 april 2012 @ 12:29:
Probeer eens een index of 'Status' & 'Sender' en een andere index op 'Status' & 'Receiver'.

Heb ik geprobeerd, maar mocht niet baten. Blijft circa 33 seconden.

[TallManNL]

Wat zegt een execution plan in management studio als je die verworven query draain gooit? Waar ligt de tijd, worden er table scans uitgevoerd? Of heb je zoveel records voor die sender/receiver dat er gewoon veel door die case heen gerekend moet worden?

De initiele beperking van het aantal records waarop wordt gewekt ligt puur op Orders.Receiver en Order.Sender dus ik zou die twee (zonder status) in 2 indexes verwachten.

[Woy]

Ik zou inderdaad eerst eens naar de execution plan kijken. Eventueel kijken of je de query niet meerdere malen uitvoert wordt of dat er meerdere query's uitgevoerd worden ( Met SQL Server Profiler kun je makkelijk even snel kijken wat er daadwerkelijk uitgevoerd wordt )

[PdeBie]

De query op zich lijkt niet het probleem te zijn merk ik nu op. Als ik hem direct op de db uitvoer in de query analyser, verschijnen de records in enkele milliseconden op beeld.

Heb vervolgens (ter test) deferredLoading uitgeschakeld. Dit lijkt ook iets te helpen. Echter mis ik nu wel bepaalde data. Dus dat wordt even puzzelen met de dataloadoptions.

En ik denk ook dat het grootste deel van het probleem hier in zit. Op elk record worden weer meerdere andere zaken (bijvoorbeeld info over de receiver) opgehaald. Ik zit er over na te denken om dit allemaal weg te halen en de extra data middels AJAX op te halen op het moment dat het ook daadwerkelijk nodig is (bijvoorbeeld een mouseover op een label). Allemaal overhead om op te halen als het niet gebruikt word.

[HMS]

Dit is met LINQ2SQL?

In dat geval raad ik je aan om eens wat logging aan te zetten (Tip: Log property op je DataContext), en eens te gaan kijken wat er allemaal opgehaald wordt.

[PdeBie]

ok, zal daar eens naar kijken.

[PdeBie]

Zojuist de tip van HMS geprobeerd en logging toegevoegd. Doe dit door te loggen naar de debugwindow. Ideale oplossing! Had ik eerder moeten weten haha.

Maar in ieder geval: de queries die worden uitgevoerd zijn precies wat ik verwachte. Blijft vreemd dan dat de query in SQL manager vele malen sneller werkt dan in de applicatie zelf.
Ben vervolgens de query eens gaan strippen en heb de Skip() er uit gehaald. Dus in principe haalt hij dan altijd dezelfde orders op, maar hij genereert daadwerkelijk een andere query. En wat blijkt: hij is vele malen sneller.
Het lijkt er dus op dat met de .Skip(x).Take(y) methode de orders dusdanig vaak doorlopen worden, wat dus het performanceverlies verklaart. De vraag is nu dus.... waar en waarom?

C#:

IQueryable<OrderHead> orders = from oh in dc.orders 
where (oh.Receiver == memberID || oh.Sender == memberID) 
orderby  
  (oh.Status == 1 && oh.Receiver == memberID ? 1 : 
  oh.Status == 3 && oh.Receiver == memberID ? 1 : 
  oh.Status == 5 && oh.Sender == memberID ? 0 : 
  oh.Status == 6 && oh.Sender == memberID ? 0 : 
  oh.Status == 7 && oh.Sender == memberID ? 0 : 
  oh.Status == 8 && oh.Sender == memberID ? 0 : 
  oh.Status == 20 ? 99 : 90),  
  oh.DeliveryDate ascending, oh.ID ascending 
select oh; 

//return orders.Skip(startRowIndex).Take(maximumRows)

return orders.Take(maximumRows)

[Paul]

Crap, ik heb niet goed gelezen, sorry

Edit: Welke query krijg je nu? SELECT TOP 15 in plaats van de versie met ROW_NUMBER ?

[ Voor 138% gewijzigd door Paul op 11-04-2012 09:58 ]

[PdeBie]

ja, nu krijg ik inderdaad gewoon een select top (x).

Ik ben nu bezig met de query verder uit te pluizen.

[PdeBie]

wat ik nu geprobeerd heb is om met de originele query (incl. skip en take) de records op te halen. Daarbij haal ik niet elke kolom op, maar een selectie ervan.

C#:

var query = from oh in dc.Orders
                 where oh.Status != 99 //dropped orders
                 select new Order
                 {
                            ID = oh.ID,
                            Sender = oh.Sender,
                            Receiver = oh.Receiver,
                            OrderID = oh.OrderID,
                            Status = oh.Status,
                            DeliveryDate = oh.DeliveryDate,
                            InsertDate = oh.InsertDate
                 };

Echter, als ik dit uitvoer krijg ik de volgende exception:
Explicit construction of entity type 'dc.Order' in query is not allowed.

Wat erop neer komt dat je geen gedeeltes van objecten op kan halen uit de database en vervolgens kan casten als Order en dat de rest van de velden null of iets dergelijks zijn.
De reden die MS opgeeft:

This check was added because it was supposed to be there from the beginning and was missing.
Constructing entity instances manually as a projection pollutes the cache with potentially malformed objects, leading to confused programmers and lots of bug reports for us.

In addition, it is ambiguous whether projected entities should be in the cache or changed tracked at all. The usage pattern for entities is that they are created outside of queries and inserted into tables via the DataContext and then later retrieved via queries, never created by queries.

bron: http://social.msdn.microsoft.com/forums/en-US/linqprojectgeneral/thread/1ce25da3-44c6-407d-8395-4c146930004b/

Kortom, ze denken dat developers niet de slimste zijn

--edit--
Dit is te verhelpen met:

C#:

Func<Order, Order> make =
o => new Order { OrderID = o.OrderID };

from o in dc.Orders
select make(o);

[ Voor 9% gewijzigd door PdeBie op 11-04-2012 11:10 . Reden: toevoeging oplossing ]

[PdeBie]

Het lijkt erop dat het geheel simpelweg traag werd door het feit dat er heel veel kolommen opgehaald werden waar eigenlijk niks mee gedaan werd.
Tel daar nog wat overhead bij op uit andere functies in het gridview en zo tikken de seconden stiekem aardig aan.

Wat mij betreft probleem duidelijk.
Ik ga hier eens overleggen wat we met sommige zaken gaan doen om de performance nog verder te verbeteren.

[RobIII]

Gebruik de edit knop ( ) als je iets toe te voegen hebt; je topic herhaaldelijk omhoogschoppen is niet nodig en die melding staat er niet voor niets: