We use LLBLGen Pro 3.5 in MS VS 2010 and as database SQL Server 2008 R2.

Code snap from our project:

Dim filter AS RelationPredicateBucket = Nothing Dim entityCollection As IEntityCollection2 = Nothing Dim sorter As New SortExpression Dim prefetchPath As New PrefetchPath2(CInt(EntityType)) entityCollection = New EntityCollection(Of ControlModulePinsEntity) prefetchPath.Add(ControlModulePinsEntity.PrefetchPathControlModuleTypePins) prefetchPath.Add(ControlModulePinsEntity.PrefetchPathControlModules) Using adapter As New DataAccessAdapter adapter.FetchEntityCollection(entityCollection, Filter, 0, sorter, prefetchPath) End Using

I'm wondering why: - LLBLGen generates SQL statments with subselect - The SQL statment is executed twice (see below SQL snaps from profiler)

SELECT DISTINCT [Tables].[ControlModulePins].[ID], [Tables].[ControlModulePins].[CMID], [Tables].[ControlModulePins].[CMTypePinID] FROM (( [Tables].[ControlModules] [LPA_C1] INNER JOIN [Tables].[vw_AllControlModulePins] [LPA_A2] ON [LPA_C1].[ID]=[LPA_A2].[CMID]) INNER JOIN [Tables].[ControlModulePins] ON [Tables].[ControlModulePins].[ID]=[LPA_A2].[PinID]) WHERE ( [LPA_A2].[CMID] IN (SELECT [Tables].[ControlModules].[ID] FROM [Tables].[ControlModules] ))

SELECT DISTINCT [LPA_C1].[ID] AS [ID0], [Tables].[ControlModulePins].[ID] AS [ID1] FROM (( [Tables].[ControlModules] [LPA_C1] INNER JOIN [Tables].[vw_AllControlModulePins] [LPA_A2] ON [LPA_C1].[ID]=[LPA_A2].[CMID]) INNER JOIN [Tables].[ControlModulePins] ON [Tables].[ControlModulePins].[ID]=[LPA_A2].[PinID]) WHERE ( ( [LPA_A2].[CMID] IN (SELECT [Tables].[ControlModules].[ID] FROM [Tables].[ControlModules] )))

The second query is for the M:N merge. It fetches the PK's from both sides to merge the parent and the child set over an m:n relationship. it has to do this, as it otherwise doesn't know which element to join with the other side as there's no FK value in one side pointing to the other in an m:n relationship (as that information is in the intermediate entity).

The subselect is done because the # of parent entities is larger than the threshold for a parametrized query. See prefetch paths in the docs about tweaking prefetch paths to make them use a parametrized subquery to filter child entities based on parent entities.

I provided you with the wrong code snip. The correct one is:

  Dim prefetchPath As New PrefetchPath2(CInt(EntityType.ControlModulesEntity))
  prefetchPath.Add(ControlModulesEntity.PrefetchPathParentControlModule)
  prefetchPath.Add(ControlModulesEntity.PrefetchPathAllControlModulePins)
  If ControlModules Is Nothing Then
     ControlModules = New EntityCollection(Of ControlModulesEntity)
  End If
  Using adapter As New DataAccessAdapter
     adapter.FetchEntityCollection(ControlModules, Nothing, prefetchPath)
  End Using

Remarks: - After investigating what happens it seems we have a view, called "AllControlModulePins" (110.000 recods) that is rather performance expensive. - A profiler on the client side shows that LLBLGen needs a lot of time to fit all data together also.

Do you need all 110,000 rows on the client? Prefetch paths work per node. If you fetch a tremendous amount of entities first and a lot of related data, then those entities have to be merged together using hash-loops (which is rather efficient, but it still needs to process all the entities' PK and FK's). M:N related entities need 1 extra pk-pk query as I explained above.

So the question is: do you need all entities with a prefetch path fetch on the client, or could you filter them perhaps? Is paging an option? Please elaborate a bit about what you're doing with all the data fetched.

Sorry for the delay:

What we do is perhaps a bit complex to explain, but i try say something, perhaps you understand (see also attachment). We have objects, called Control Modules (CM), that have Pins (example: Motor with connectors for power supply). This CM can be a complex type that hase child CM 's with also pins. We need all the pins belonging to a particular CM (so also the child pins). For this reason we use a view, this view is not optimalised for a particular CM.

Previously we opened a datagrid, called Cables, and that takes 48 seconds, because of the majour fetch of all pins. We need this information not on the main grid, but in detailed grid. At the moment i fetch the data when we need it, but the program feels still slow. It takes 5 seconds to get information of 12 Pins. (instead of 110.000 Pins 45 seconds)

What whould be the sugestion to handle this?

Using such a view in a prefetchPath is not a good idea, as far as I see it. So instead of fetching CM and prefetching the view, I suggest de-normalizing the view to include the CM info, and only fetch the View, filtering by the CM.

We made a stored procedure that returns what we want. Thanks for your help.