v2.6, sql server, adapter, vb.net 2008

I was wondering if there was a way to make my FetchEntity's more effecient.

Example: EquipmentEntity Has EquipmentModelEntity (m:1 - required attribute) Has EquimentDocumentEntity collection (1:n) - association table Has DocumentEntity (m:1) Has DocumentEntity collection (m:n via EquipmentDocument)

1. When I do a fetchentity on Equipment I always want to bring back the associated model entity, so in my manager class I include a prefetchpath of EquipmentModel. This correctly loads the corresponding equipmentmodel entity but the sql that is generated is SELECT fields FROM Equiment followed by SELECT fields FROM EquipmentModel WHERE matchclause. Is there any way to construct the fetch to make the sql do 1 statement with an inner join to equipmentmodel rather than 2 different statements?

2. When the user specifies that they want the documents loaded I do a prefetchpath of EquipmentDocument AND a prefetchpath of Document. This results in both collections being loaded but the equimentdocument entity does not have the associated documententity. If I change it so that the prefetchpath on EquipmentDocument does a subpath add of Document then both collections are loaded completey but the sql generated selects from equipmentdocument inner join on document twice, once for each collection. Is there anyway to build this differently so the sql to get the list is only executed once but both collections are loaded fully?

I can figure out ways around each of these but what I'm looking for is the "best way". We've been using the product now for 2 months and it's very powerful but I'm trying to determine how to write the code to use the product effeciently. We're using this in a web environment and want to cut down on the roundtrips when possible. This is just one example in whats going to end up being a 80+ entity system and decisions we make now will effect how we code the rest of the managers going forward.

1. When I do a fetchentity on Equipment I always want to bring back the associated model entity, so in my manager class I include a prefetchpath of EquipmentModel. This correctly loads the corresponding equipmentmodel entity but the sql that is generated is SELECT fields FROM Equiment followed by SELECT fields FROM EquipmentModel WHERE matchclause. Is there any way to construct the fetch to make the sql do 1 statement with an inner join to equipmentmodel rather than 2 different statements?

I think one joinned query might not be the ideal case here, as this will return too much redundant data for the "one" side of the relation. I strongly believe the current approach is the best one.

1. When the user specifies that they want the documents loaded I do a prefetchpath of EquipmentDocument AND a prefetchpath of Document. This results in both collections being loaded but the equimentdocument entity does not have the associated documententity. If I change it so that the prefetchpath on EquipmentDocument does a subpath add of Document then both collections are loaded completey but the sql generated selects from equipmentdocument inner join on document twice, once for each collection. Is there anyway to build this differently so the sql to get the list is only executed once but both collections are loaded fully?

Would you please post the generate SQL to elaborate on the bold part.

I think one joinned query might not be the ideal case here, as this will return too much redundant data for the "one" side of the relation. I strongly believe the current approach is the best one.

Is there anyway to change the behaviour though? In this case I am ALWAYS pulling all the fields from the associated EquipmentModel every time I pull all the fields from Equipment. I don't want all the fields from equipmentmodel on the EquipmentEntity I just want to make one SQL call and load both the EquimentEntity and its associated EquipmentModel.

Here is all of the executed sql and the prefetch path used to retrieve 1 equipment entity based on id. As you can see at the end the EquipmentDocument table is queried 3 times to fill the EquipmentDocument collection and the corresponding Document collection.

Dim path As IPrefetchPath2 = New PrefetchPath2(CType(EntityType.EquipmentEntity, Integer))
Dim flt As IPrefetchPathElement2 = path.Add(EquipmentEntity.PrefetchPathEquipmentModel)
flt.SubPath.Add(EquipmentModelEntity.PrefetchPathEquipmentType)
flt.SubPath.Add(EquipmentModelEntity.PrefetchPathMake)

If bIncludeDocuments Then
  path.Add(EquipmentEntity.PrefetchPathEquipmentDocumentXref).SubPath.Add(EquipmentDocumentEntity.PrefetchPathDocument)
  path.Add(EquipmentEntity.PrefetchPathDocuments)
end if


Executed Sql Query: 
    Query: SELECT DISTINCT TOP 1 [dbo].[Equipment].[EquipmentID] AS [F1_0], [dbo].[Equipment].[EquipmentName] AS [F1_1], [dbo].[Equipment].[SerialNumber] AS [F1_2], [dbo].[Equipment].[ModelID] AS [F1_3], [dbo].[Equipment].[ModelYear] AS [F1_4], [dbo].[Equipment].[EstimatedDailyReading] AS [F1_5], [dbo].[Equipment].[EstimatedReadingPerUnit] AS [F1_6], [dbo].[Equipment].[FuelTypeID] AS [F1_7], [dbo].[Equipment].[PurchasePrice] AS [F1_8], [dbo].[Equipment].[SellPrice] AS [F1_9], [dbo].[Equipment].[OrganizationID] AS [F1_10], [dbo].[Equipment].[EquipmentGroupID] AS [F1_11], [dbo].[Equipment].[ColorID] AS [F1_12], [dbo].[Equipment].[DateLastUpdated] AS [F1_13], [dbo].[Equipment].[ActiveFlag] AS [F1_14], [dbo].[Vehicle].[VehicleID] AS [F2_15], [dbo].[Vehicle].[LicensePlate] AS [F2_16], [dbo].[Vehicle].[LicensePlateState] AS [F2_17], [dbo].[Vehicle].[BodyTypeID] AS [F2_18], [dbo].[Vehicle].[BrakeTypeID] AS [F2_19], [dbo].[Vehicle].[PersonCapacity] AS [F2_20], [dbo].[Vehicle].[WheelChairCapacity] AS [F2_21] 
FROM ( [dbo].[Equipment]  LEFT JOIN [dbo].[Vehicle]  ON  [dbo].[Equipment].[EquipmentID]=[dbo].[Vehicle].[VehicleID]) 
WHERE ( ( [dbo].[Equipment].[EquipmentID] = @Id1))
    Parameter: @Id1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2.

Executed Sql Query: 
    Query: SELECT [dbo].[EquipmentModel].[ModelID] AS [Id], [dbo].[EquipmentModel].[ModelName], [dbo].[EquipmentModel].[MakeID] AS [MakeId], [dbo].[EquipmentModel].[EquipmentTypeID] AS [EquipmentTypeId] FROM [dbo].[EquipmentModel]  WHERE ( ( ( [dbo].[EquipmentModel].[ModelID] = @Id1)))
    Parameter: @Id1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.

Executed Sql Query: 
    Query: SELECT [dbo].[EquipmentType].[EquipmentTypeID] AS [Id], [dbo].[EquipmentType].[EquipmentTypeName], [dbo].[EquipmentType].[ReadingType] AS [ReadingTypeId], [dbo].[EquipmentType].[HasPartsFlag] AS [CanHaveParts], [dbo].[EquipmentType].[AllowChildrenFlag] AS [CanHaveSubEquipment], [dbo].[EquipmentType].[AllowParentFlag] AS [CanHaveParentEquipment], [dbo].[EquipmentType].[SystemID] AS [SystemId] FROM [dbo].[EquipmentType]  WHERE ( ( ( [dbo].[EquipmentType].[EquipmentTypeID] = @Id1)))
    Parameter: @Id1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.

Executed Sql Query: 
    Query: SELECT [dbo].[EquipmentMake].[MakeID] AS [Id], [dbo].[EquipmentMake].[MakeName] FROM [dbo].[EquipmentMake]  WHERE ( ( ( [dbo].[EquipmentMake].[MakeID] = @Id1)))
    Parameter: @Id1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.

Executed Sql Query: 
    Query: SELECT [dbo].[EquipmentDocument].[EquipmentID] AS [EquipmentId], [dbo].[EquipmentDocument].[DocumentID] AS [DocumentId], [dbo].[EquipmentDocument].[DefaultFlag] AS [IsDefault] 
FROM [dbo].[EquipmentDocument]  WHERE ( ( ( [dbo].[EquipmentDocument].[EquipmentID] = @EquipmentId1)))
    Parameter: @EquipmentId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2.

Executed Sql Query: 
    Query: SELECT [dbo].[Document].[DocumentID] AS [Id], [dbo].[Document].[DocumentSubject], [dbo].[Document].[DateEntered], [dbo].[Document].[PersonID] AS [PersonId], [dbo].[Document].[DocumentObjectID] AS [DocumentObjectId], [dbo].[Document].[NoteText], [dbo].[Document].[DateLastUpdated] 
FROM [dbo].[Document]  WHERE ( [dbo].[Document].[DocumentID] IN (@Id1, @Id2, @Id3, @Id4, @Id5, @Id6, @Id7, @Id8, @Id9, @Id10, @Id11, @Id12))
    Parameter: @Id1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 5.
    Parameter: @Id2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 15.
    Parameter: @Id3 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 16.
    Parameter: @Id4 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 17.
    Parameter: @Id5 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 19.
    Parameter: @Id6 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 18.
    Parameter: @Id7 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 6.
    Parameter: @Id8 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.
    Parameter: @Id9 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 20.
    Parameter: @Id10 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 4.
    Parameter: @Id11 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 8.
    Parameter: @Id12 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2.

Executed Sql Query: 
    Query: SELECT [dbo].[Document].[DocumentID] AS [Id], [dbo].[Document].[DocumentSubject], [dbo].[Document].[DateEntered], [dbo].[Document].[PersonID] AS [PersonId], [dbo].[Document].[DocumentObjectID] AS [DocumentObjectId], [dbo].[Document].[NoteText], [dbo].[Document].[DateLastUpdated] 
FROM (( [dbo].[Equipment] [LPA_E1]  INNER JOIN [dbo].[EquipmentDocument] [LPA_E2]  ON  [LPA_E1].[EquipmentID]=[LPA_E2].[EquipmentID]) INNER JOIN [dbo].[Document]  ON  [dbo].[Document].[DocumentID]=[LPA_E2].[DocumentID]) 
WHERE ( ( ( [LPA_E2].[EquipmentID] = @EquipmentId1))) ORDER BY [dbo].[Document].[DateEntered] DESC
    Parameter: @EquipmentId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2.

Executed Sql Query: 
    Query: SELECT DISTINCT [LPA_E1].[EquipmentID] AS [Id0], [dbo].[Document].[DocumentID] AS [Id1] 
FROM (( [dbo].[Equipment] [LPA_E1]  INNER JOIN [dbo].[EquipmentDocument] [LPA_E2]  ON  [LPA_E1].[EquipmentID]=[LPA_E2].[EquipmentID]) INNER JOIN [dbo].[Document]  ON  [dbo].[Document].[DocumentID]=[LPA_E2].[DocumentID]) 
WHERE ( ( ( ( [LPA_E2].[EquipmentID] = @EquipmentId1))))
    Parameter: @EquipmentId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2.

path.Add(EquipmentEntity.PrefetchPathEquipmentDocumentXref).SubPath.Add(EquipmentDocumentEntity.PrefetchPathDocument) path.Add(EquipmentEntity.PrefetchPathDocuments)

May I ask why do you need to have load documents by 2 different ways, once using the intermediate entity, and once directly.

Just one should be sufficient.

The EquipmentDocument association table has boolean to indicate if the document is the default one for the given piece of equipment. So, if the ui programmer needs to display a list of all documents for a given piece of equipment it seems is more intuitive to iterate through as folllows:

Dim eq as EquipmentEntity = EquipmentManager.GetById(1,true) '2nd param flag to get docs
for each doc as DocumentEntity in eq.Documents
  'load entry into grid via doc.properties
  'if default document show diff icon in grid
next

rather than

Dim eq as EquipmentEntity = EquipmentManager.GetById(1,true) '2nd param flag to get docs
for each eqdoc as EquipmentDocumentEntity in eq.EquipmentDocumentCollection
  'load entry into grid via eqdoc.Document.properties
  'if default document show diff icon in grid
next

In order to determine if its the default document they need the EquipmentDocument (association table) collection loaded. Even if I take the EquipmentDocument.Subpath(Document) off it still results in 3 queries over the EquipmentDocument table (portion of trace below)

Executed Sql Query: 
    Query: SELECT [dbo].[EquipmentDocument].[EquipmentID] AS [EquipmentId], [dbo].[EquipmentDocument].[DocumentID] AS [DocumentId], [dbo].[EquipmentDocument].[DefaultFlag] AS [IsDefault] 
FROM [dbo].[EquipmentDocument]  WHERE ( ( ( [dbo].[EquipmentDocument].[EquipmentID] = @EquipmentId1)))
    Parameter: @EquipmentId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2.

Executed Sql Query: 
    Query: SELECT [dbo].[Document].[DocumentID] AS [Id], [dbo].[Document].[DocumentSubject], [dbo].[Document].[DateEntered], [dbo].[Document].[PersonID] AS [PersonId], [dbo].[Document].[DocumentObjectID] AS [DocumentObjectId], [dbo].[Document].[NoteText], [dbo].[Document].[DateLastUpdated] 
FROM (( [dbo].[Equipment] [LPA_E1]  INNER JOIN [dbo].[EquipmentDocument] [LPA_E2]  ON  [LPA_E1].[EquipmentID]=[LPA_E2].[EquipmentID]) INNER JOIN [dbo].[Document]  ON  [dbo].[Document].[DocumentID]=[LPA_E2].[DocumentID]) WHERE ( ( ( [LPA_E2].[EquipmentID] = @EquipmentId1))) ORDER BY [dbo].[Document].[DateEntered] DESC
    Parameter: @EquipmentId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2.

Executed Sql Query: 
    Query: SELECT DISTINCT [LPA_E1].[EquipmentID] AS [Id0], [dbo].[Document].[DocumentID] AS [Id1] 
FROM (( [dbo].[Equipment] [LPA_E1]  INNER JOIN [dbo].[EquipmentDocument] [LPA_E2]  ON  [LPA_E1].[EquipmentID]=[LPA_E2].[EquipmentID]) INNER JOIN [dbo].[Document]  ON  [dbo].[Document].[DocumentID]=[LPA_E2].[DocumentID]) WHERE ( ( ( ( [LPA_E2].[EquipmentID] = @EquipmentId1))))
    Parameter: @EquipmentId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2.

OK - after doing a search on "efficiency" and reading other posts it seems that there is no way to tell a prefetch path to use as join as opposed to a subquery. Might be a nice request to have an optional parameter when creating a prefectpath that lets the developer using the object indicate that they want it performed as a join instead of a subquery. I understand why it can't be done automatically.

So I guess the root of my question comes down to...in a real enterprise scale web application are my concerns regarding the multiple round trips to work with one entity and some if its related data something that I shouldn't be concerned with? I'm wondering if others have stuck with the default code generated by llblgen or have switched to using stored procs, views etc. when retrieving joined data and then projecting to new entities? For example, to load my collection of EquipmentDocument entities and Document entities I could write a stored proc to issue SELECT fields FROM EquipmentDocument INNER JOIN Document ON fields WHERE EquimentId = n and then manually load both collections. Less SQL but more code.

Figuring out HOW to use a new tool can be easy, figuring out the best way to utilize it definately takes more time, experimenting and input.

You could use excluding fields as well on the prefetch path to limit the field data, or be sure the parameterizedprefetchpaththreshold setting is high enough that subqueries are done more efficiently. Joins with prefetch paths is a true nightmare, and can't be done automatically. So it then effectively comes down to specifying projections on a joined set. This is very cumbersome if the entities are in an inheritance hierarchy.

Otherwise you might consider using a typedlist, or a dynamicList, which grabs all the fields you want in a flat list (dataTable), performing Joins in the database.