The prefetch path functionality looks pretty neat however I note that it requires a separate query and thus a separate db call for each entity type in the prefetch tree. Would it be possible to reduce the number of calls to the database to 1 only by concatenating the queries and then managing the multiple result sets that are returned? Or is the feature of returning multiple result sets in a single call not available to all database providers (it is available in the SQLServer provider)? eg:

 select * from order where EmployeeId = 2;select * from customer where id in (select customerid from order where EmployeeId = 2);

which should return 2 result sets in the one call that you could then process as you do now. (We're always looking at limiting or reducing the number of calls to the database which is often one of the arguments used when proponents espouse that Stored Procedures are the most efficient database access method - this is also a reason why we use TypedLists heavily in our code - why hit the database 4 times when you can hit it once and do a couple of joins which is what relational databases are designed to do and do well). Any thoughts?

takb wrote:

The prefetch path functionality looks pretty neat however I note that it requires a separate query and thus a separate db call for each entity type in the prefetch tree. Would it be possible to reduce the number of calls to the database to 1 only by concatenating the queries and then managing the multiple result sets that are returned? Or is the feature of returning multiple result sets in a single call not available to all database providers (it is available in the SQLServer provider)? eg:

 select * from order where EmployeeId = 2;select * from customer where id in (select customerid from order where EmployeeId = 2);

which should return 2 result sets in the one call that you could then process as you do now.

It's not available on SqlServer At least not with a datareader. It comes with SqlServer 2005 ('Multiple Active ResultSets' or 'MARS'). All entities are read using a datareader, so batching the queries would not be sufficient. You can load them into a dataset using a data-adapter but that would mean extra overhead. Batching sql commands is not available on Oracle 10g, Firebird and Access.

Microsoft experimented in Objectspaces with joins and null values using a union query. However that has problems with duplicate rows which are not filterable because some field clashes with DISTINCT. (the idea is that you first have: order.field1, order.field2, ..., order.fieldn, null, null, null, .... and then: UNION null, null, null, null, (till order.fieldn), customer.field1, customer.field2 etc..

Not only would this be slower on the client, it also suffers from the duplicate issue, which can be pretty harsh.

It's a tradeoff btw. The method used now, pulls the least amount of data to the client. This is also helping making it efficient.

(We're always looking at limiting or reducing the number of calls to the database which is often one of the arguments used when proponents espouse that Stored Procedures are the most efficient database access method - this is also a reason why we use TypedLists heavily in our code - why hit the database 4 times when you can hit it once and do a couple of joins which is what relational databases are designed to do and do well). Any thoughts?

Stored procedures would not win anything for you when it comes to prefetching related data, as the call to the db would have to be made anyway. Joins can be inefficient as well, when a lot of duplicate rows are created but DISTINCT can't be used to filter them out because the resultset contains a field of a type blob/clob/text/image or other big field.

Otis wrote:

It's not available on SqlServer At least not with a datareader. It comes with SqlServer 2005 ('Multiple Active ResultSets' or 'MARS'). All entities are read using a datareader, so batching the queries would not be sufficient.

Can't you use the IDataReader.NextResult method which is implemented by the SQLServer DataReader? This method is designed for batch SQL queries like I described previously.

Otis wrote:

Batching sql commands is not available on Oracle 10g, Firebird and Access.

This may be the case and if there is no common solution then its probably not worth considering. It appears that the Oracle provider supports batch queries, however there seems to be a few issues (you have to use REF CURSORS which may limit this facility to only being available in stored procedures which is no good for llblgen pro).

takb wrote:

Otis wrote:

It's not available on SqlServer At least not with a datareader. It comes with SqlServer 2005 ('Multiple Active ResultSets' or 'MARS'). All entities are read using a datareader, so batching the queries would not be sufficient.

Can't you use the IDataReader.NextResult method which is implemented by the SQLServer DataReader? This method is designed for batch SQL queries like I described previously.

Otis wrote:

Batching sql commands is not available on Oracle 10g, Firebird and Access.

This may be the case and if there is no common solution then its probably not worth considering. It appears that the Oracle provider supports batch queries, however there seems to be a few issues (you have to use REF CURSORS which may limit this facility to only being available in stored procedures which is no good for llblgen pro).

Hmm - a fairly bizzare statement from the nice folks at SD - yes, you can indeed use the NextResule (that's how CSLA does it)...

jcraigue wrote:

takb wrote:

Otis wrote:

It's not available on SqlServer At least not with a datareader. It comes with SqlServer 2005 ('Multiple Active ResultSets' or 'MARS'). All entities are read using a datareader, so batching the queries would not be sufficient.

Can't you use the IDataReader.NextResult method which is implemented by the SQLServer DataReader? This method is designed for batch SQL queries like I described previously.

Otis wrote:

Batching sql commands is not available on Oracle 10g, Firebird and Access.

This may be the case and if there is no common solution then its probably not worth considering. It appears that the Oracle provider supports batch queries, however there seems to be a few issues (you have to use REF CURSORS which may limit this facility to only being available in stored procedures which is no good for llblgen pro).

Hmm - a fairly bizzare statement from the nice folks at SD - yes, you can indeed use the NextResule (that's how CSLA does it)...

Hmmm, my mistake indeed. I was confusing MARS with sequenced results, my mistake (I didn't look closely into IDataReader.NextResult)

Now knowing this, I'm not sure how I can implement this in the runtime core... It would cause a lot of code overhaul and save you 20ms at most, because you need to open another connection (and with adapter, even that's not the case). The query has to be passed to the db, so there is no time gain there, just the amount of connections opened, but that's with pooling a non-issue. Prefetch paths are implemented on top of the regular code of fetching collections btw, they're not in the lower regions. Prefetch paths in fact just create a field compare set query based on the parent query and execute that, merging the results. The time spend fetching prefetch paths is thus mostly spend in merging the data.