Hi -

Please help me jump-start the conversion of the approach that I have been using to retrieve Child and Grandchild collections in my Parent Business Objects.

I've reviewed the Pre-fetch info in the documentation, but I'm not clear about the best practice for implementing:

One Round trip to the database to retrieve 1 parent and all it's childeren ...

It is currently implemented with a stored procedure or set of SQL statements that return the hierarchy ...generating multiple result sets ti a data reader

The pattern is to:

Capture the single Parent row and populate the properties of the Root object ( I'm currently processing the root BusinessObject using a TypedView).

After executing a datareader.NextResult() command another method in the Root constructor is called LoadChild(datareader) ... those child rows are processed in the Load Child(datareader) function.

If GrandChildren are contained in the Root object the Root constructor then does another datareader.NextResult() and calls LoadGrandChild(datareader).

I did review the Pre-fetch documentation, but it seems that the topic that I reviewed started with pulling the Root object after retrieving a collection of children ( get the Customer after Retrieving invoices).

Let me know if you need more details. Hopefully this is a common design issue with a documented best practice.

Thank you,

Steve

I'm not sure what exactly is your question, but I'll give it a shot.

• LLBLGen Pro executes one query to fetch all entities in the first level (root entity/entities)
• Then executes one query for each prefetchPathed level of entities. Either with an IN () predicate: WHERE FK_Field IN(list of PK values returned from the previous query) Or with a SubQuery, as explained in the docs. (ref: Optimizing Prefetch Paths).

So if you are fetching Customers, their Orders and their Order Details. Then 3 Query are to be executed.

so there is no way to retrieve more than one result set with one trip to the database ?

You are telling me that prefetch works that way ... but is there another method ???

Thx,

Steve

SDM wrote:

so there is no way to retrieve more than one result set with one trip to the database ?

You are telling me that prefetch works that way ... but is there another method ???

Thx,

Steve

It's not done that way because it in general isn't flexible enough and also not all databases support MARS. The prefetch path logic is in the generic code so it doesn't use MARS.

It's also not a big deal, because it can now optimize the subsequent queries you need per node based on the fetched set or sets. If you fetch everything in one go, you can't do that. Also, joins between graph nodes is only possible with m:1 and some 1:1 edges, and also only on a single path (so multi-branch graphs need multiple queries no matter what).

You can fetch multiple resultsets from a proc if you want to, and project them onto multipe entity collections, there's an example of it in the manual section describing fetching datareaders and projections.

Though for prefetch paths: they're build to optimize the query per node, based on the set fetched. This is sometimes an IN query with values, but also sometimes a subquery. In your proc you can't make that decision without a count query and even then you can't optimize it with an IN (val1, val2 ... ) query without string manipulation in the proc (not what you want).

I've drilled into Prefetch ... the purpose of that seems to be most suited for retrieving multiple children / grandchildren of multiple roots

Is there a better approach for retrieving all the children of a single root: IE: all Orders for ONLY ONE Customer ??

And from what I get from Typed Lists that will allow me to retrieve all of the orders for a customer, but it does not retrieve the hieracrchy ... all I need is:

-- 1 row containing the attributes of the Root: IE: Customer info

and

-- 0 to many rows containing the atttributes of the Order: Ie Order ID / Order Date etc:

At some point I'll also need to drill down and get

Customer Order1 Line Item1.1 Line Item1.2 Line Item1.3 Order2 Line Item2.1 Line Item2.2 Line Item2.3

... but again ... the domain is always limited to one customer ... Is Prefetch Overkill ???

I'm having trouble finding an example of the basic use case 1 and only 1 root 0 to many children

and BTW I have chosen to use DataAccessAdapter only ... that's correct, right ???

Thx,

Steve

I'm having trouble finding an example of the basic use case 1 and only 1 root 0 to many children

The following is copied from the docs:

Single entity fetches and Prefetch Paths Prefetch Paths can also be used when you fetch a single entity, either by fetching the entity using a primary key or via a unique constraint fetch. Below are two examples, one using the primary key and one using a unique constraint. Both fetch the m:n related Employees for the particular Customer entity instantiated.

Primary key fetch

IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.CustomerEntity); prefetchPath.Add(CustomerEntity.PrefetchPathEmployees); CustomerEntity customer = new CustomerEntity("BLONP"); DataAccessAdapter adapter = new DataAccessAdapter(); adapter.FetchEntity(customer, prefetchPath);

Unique constraint fetch

CustomerEntity customer = new CustomerEntity(); customer.CompanyName = "Blauer See Delikatessen"; IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.CustomerEntity); prefetchPath.Add(CustomerEntity.PrefetchPathEmployees); DataAccessAdapter adapter = new DataAccessAdapter(); adapter.FetchEntityUsingUniqueConstraint(customer, customer.ConstructFilterForUCCompanyName(), prefetchPath);

Is Prefetch Overkill ???

Absolutly not.