Hi all

I use version 2.5 June 2nd, 2008

I have a table with a lot of foreign keys. To get the description field from the primary key table I use prefetchpath like this

                RelationPredicateBucket filter = new RelationPredicateBucket(flylisteFilter);
                // add showline filter
                filter.PredicateExpression.AddWithAnd(PakkelisteHistorikFields.ShowLine == true);
                IPrefetchPath2 prefetchpath = new PrefetchPath2((int)FlyListe.DAL.EntityType.PakkelisteHistorikEntity);
                prefetchpath.Add(PakkelisteHistorikEntity.PrefetchPathEKliste);
                prefetchpath.Add(PakkelisteHistorikEntity.PrefetchPathSorteringsliste);
                prefetchpath.Add(PakkelisteHistorikEntity.PrefetchPathVarenrliste);
                // 2009-03-11 Add Kolli1 and Stk1
                prefetchpath.Add(PakkelisteHistorikEntity.PrefetchPathKolli1liste);
                prefetchpath.Add(PakkelisteHistorikEntity.PrefetchPathStk1liste);
                // 2009-03-11 Added udv til listen
                prefetchpath.Add(PakkelisteHistorikEntity.PrefetchPathUdvliste);
                PredicateExpression filternotes = new PredicateExpression(UserNotesFields.UserId == _userID);
                PredicateExpression orderstatusfilter = new PredicateExpression(OrdreStatusFields.UsersId == _userID);
                prefetchpath.Add(PakkelisteHistorikEntity.PrefetchPathUserNotes, 0, filternotes);
                prefetchpath.Add(PakkelisteHistorikEntity.PrefetchPathOrdreStatus, 0, orderstatusfilter);
adapter.FetchEntityCollection(_flyListeentityCollection, filter, prefetchpath);

If I have 50000 rows with showline=true it takes 21 seconds on my machine and if I don't use prefetchpath it takes only 8 seconds. I can only run .net framework 2.0. flylisteFilter is a very very complicated filter. So complicated that I was not able to write it in sql. In the example the filter is empty.

How can I make it faster (upgrade to 2.6 is ok if that is the solution?

An upgrade is always a good thing to try, for better performace.

But the thing is that you are fetching 50,000 records, with almost 8 prefetchPaths. Taking into consideration that for each prefetchPath fetch the main query is executed again as a subQuery in the prefetch fetch.

Example: Query to fetch the main entities (PakkelisteHistorik)

SELECT CustomerID, CompanyName, ...
FROM Customers
WHERE Country = @country

A query for a preftech would look like:

SELECT OrderID, CustomerID, OrderDate, ...
FROM Orders
WHERE CustomerID IN
(
    SELECT CustomerID
    FROM Customers
    WHERE Country = @country
)

Imagine this is done 8 more times.

What you can try to do is to fetch the 50,000 in chuncks, each chunck with its prefetchPaths.

Thank you for a fast answer

I have tried with a databaseview with joins on most of the primary tables, but not ordrestatus and usernotes, because these two needs an extra filter. Is there a way where I can fetch data from the tables mention in the view as part of fetchcollection, so that I don't need a prefetch, so making a join.

I have tried to use FetchTypedView, but then I can't use prefetch for ordrestatus and usernotes. And I also need to write to the main table. I use Infragistics grid. 50000 rows is not used in the program. It is just for performance. But the program is run on a thin client where everything is virtual (including the dbserver) and everything travles over wan and not lan.

I don't understand chunk. Do you mean getting data x rows at a time.

SELECT      dbo.Pakkeliste_Historik.ID, dbo.Pakkeliste_Historik.PrintDato, dbo.Pakkeliste_Historik.PakkeID, dbo.Pakkeliste_Historik.Turtid, 
                         dbo.Pakkeliste_Historik.[Selskab nr.] AS SelskabNr, dbo.Pakkeliste_Historik.Selskab, dbo.Pakkeliste_Historik.Adresse, dbo.Pakkeliste_Historik.Kunde, 
                         dbo.Pakkeliste_Historik.[Fakt.] AS Fakt, dbo.Pakkeliste_Historik.Varenrnr, dbo.Varenrliste.Vare, dbo.Pakkeliste_Historik.udvnr, dbo.Udvliste.udv, 
                         dbo.Udvliste.udvtekst, dbo.Pakkeliste_Historik.Snr, dbo.Sorteringsliste.S, dbo.Pakkeliste_Historik.Kol, dbo.Pakkeliste_Historik.E_Knr AS EKnr, 
                         dbo.E_Kliste.E_K AS Ek, dbo.Pakkeliste_Historik.[Embl.komb.] AS EmblKomb, dbo.Pakkeliste_Historik.Antal, dbo.Pakkeliste_Historik.Pris, 
                         dbo.Pakkeliste_Historik.kolli1nr, dbo.Kolli1liste.kolli1, dbo.Kolli1liste.[Opt. Cont. ant.], dbo.Pakkeliste_Historik.LastChanged, dbo.Pakkeliste_Historik.stk1nr, 
                         dbo.Stk1liste.stk1, dbo.Pakkeliste_Historik.Bemærkning, dbo.Pakkeliste_Historik.[Antal Kolli i alt] AS AntalKolliIAlt, 
                         dbo.Pakkeliste_Historik.Banenummer_Min AS BanenummerMin, dbo.Pakkeliste_Historik.Banenummer_Max AS BanenummerMax, 
                         dbo.Pakkeliste_Historik.Stregkodetype, dbo.Pakkeliste_Historik.UdprintLokation, dbo.Pakkeliste_Historik.SortOrder, dbo.Pakkeliste_Historik.Varenr, 
                         dbo.Pakkeliste_Historik.[Cont. ant.] AS ContAnt, dbo.Pakkeliste_Historik.[Ens cont.] AS EnsCont, dbo.Pakkeliste_Historik.ContainerOK, 
                         dbo.Pakkeliste_Historik.ContainerNummer, dbo.Pakkeliste_Historik.[Fakt. org.] AS FaktOrg, dbo.Pakkeliste_Historik.MaxContainerNummer, 
                         dbo.Pakkeliste_Historik.[Afd. nr.] AS AfdNr, dbo.Pakkeliste_Historik.Ugedagsturtid, dbo.Pakkeliste_Historik.Indleveringssted, 
                         dbo.Pakkeliste_Historik.[Lager afvig] AS LagerAfvig, dbo.Pakkeliste_Historik.[Delt pak.] AS DeltPak, dbo.Pakkeliste_Historik.[Pottestør.] AS Pottestør, 
                         dbo.Pakkeliste_Historik.[Egne Notater] AS EgneNotater, dbo.Pakkeliste_Historik.Kolli2, dbo.Pakkeliste_Historik.Pak, dbo.Pakkeliste_Historik.Land, 
                         dbo.Pakkeliste_Historik.Turdato, dbo.Pakkeliste_Historik.Autolager, dbo.Pakkeliste_Historik.[Delt tur] AS DeltTur, dbo.Pakkeliste_Historik.Vognmand, 
                         dbo.Pakkeliste_Historik.[Indlev tid] AS IndlevTid, dbo.Pakkeliste_Historik.[DANPOT ordre] AS DanpotOrdre, dbo.Pakkeliste_Historik.E, 
                         dbo.Pakkeliste_Historik.[Indlev dato] AS IndlevDato, dbo.Pakkeliste_Historik.[Stk. 2] AS Stk2, dbo.Pakkeliste_Historik.[€] AS IllegalName, 
                         dbo.Pakkeliste_Historik.U, dbo.Pakkeliste_Historik.Adrkode, dbo.Pakkeliste_Historik.[Fælles nota] AS FællesNota, 
                         dbo.Pakkeliste_Historik.[Indlev datotid] AS IndlevDatotid, dbo.Pakkeliste_Historik.Turdatotid, dbo.Pakkeliste_Historik.[Fælles nota2] AS FællesNota2, 
                         dbo.Pakkeliste_Historik.[Fælles nota3] AS FællesNota3, dbo.Pakkeliste_Historik.AntalCC, dbo.Pakkeliste_Historik.ShowLine
FROM            dbo.Pakkeliste_Historik LEFT OUTER JOIN
                         dbo.E_Kliste ON dbo.Pakkeliste_Historik.E_Knr = dbo.E_Kliste.E_Knr LEFT OUTER JOIN
                         dbo.Kolli1liste ON dbo.Pakkeliste_Historik.kolli1nr = dbo.Kolli1liste.kolli1nr LEFT OUTER JOIN
                         dbo.Udvliste ON dbo.Pakkeliste_Historik.udvnr = dbo.Udvliste.udvnr LEFT OUTER JOIN
                         dbo.Sorteringsliste ON dbo.Pakkeliste_Historik.Snr = dbo.Sorteringsliste.Snr LEFT OUTER JOIN
                         dbo.Stk1liste ON dbo.Pakkeliste_Historik.stk1nr = dbo.Stk1liste.stk1nr LEFT OUTER JOIN
                         dbo.Varenrliste ON dbo.Pakkeliste_Historik.Varenrnr = dbo.Varenrliste.Varenrnr
WHERE       (dbo.Pakkeliste_Historik.ShowLine = 1)

I have found info about Multi-entity filters, where I can make joins. I will now try that

Example from doc

EntityCollection customers = new EntityCollection(new CustomerEntityFactory()); RelationPredicateBucket bucket = new RelationPredicateBucket(); bucket.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID); bucket.Relations.Add(OrderEntity.Relations.OrderDetailsEntityUsingOrderID); bucket.Relations.Add(OrderDetailsEntity.Relations.ProductEntityUsingProductID); bucket.Relations.Add(ProductEntity.Relations.SupplierEntityUsingSupplierID); bucket.PredicateExpression.Add(SupplierFields.Country == "France"); DataAccessAdapter adapter = new DataAccessAdapter();