We are using LLBL Resultset caching to prevent round trip to DB.

Our concern is on performance, if we fetch by datatable or DTO, will it reprocess(loop) from cache and create/convert according to the target object type?

LLBLGen Pro version: 5.6 Database type: MSSQL

I'm afraid I didn't understand your question. Could you please rephrase?

I'm quoting the following from the docs, if this is what you are asking about.

The caching of the resultset is done at the lowest level, at the IRetrievalQuery object, and only the object[] arrays obtained from the Data Reader are cached, no entities or other materialized objects.

Also if you are fetching a dataReader, then you need to use an IRetrievalQuery to consume the dataReader in order to use the cache.

Thanks for you reply
Yes I think we are on the same page, I just want to understand how LLBLGen generate DataTable/DTO from cache

For example, let's say we use the following method to fetch data & cache the resultset
If this query return 1k results to data table, how LLBLGen process and return the datatable (from cache) with the same query. By looping thru the cache and create each data row and return the data table?

QueryFactory qf    = new QueryFactory();
DynamicQuery query = qf.Create();


query.Select(ProductFields.Product, ProductFields.Name, ProductFields.Active)
    .Where(ProductFields.Active == 1)
    .CacheResultset(3600, "ProductCache");

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    DataTable dt = adapter.FetchAsDataTable(query);
}

yes, it'll produce a datareader that wraps the cached resultset. Check in the sourcecode (available to you on the main website -> My Account -> Downloads -> version -> Extras section) the class RetrievalQuery in the runtime libraries, 'Query' folder, line 391

In general this should be very fast, check the benchmarks:

37K rows from AdventureWorks. Caching: https://github.com/FransBouma/RawDataAccessBencher/blob/master/Results/20220801_net6.txt#L239 No caching: https://github.com/FransBouma/RawDataAccessBencher/blob/master/Results/20220801_net6.txt#L106

Materializing a resultset in a datatable isn't slow either. 37,000 rows takes 128ms, so 100 a lot less

Otis wrote:

yes, it'll produce a datareader that wraps the cached resultset. Check in the sourcecode (available to you on the main website -> My Account -> Downloads -> version -> Extras section) the class RetrievalQuery in the runtime libraries, 'Query' folder, line 391

In general this should be very fast, check the benchmarks:

37K rows from AdventureWorks. Caching: https://github.com/FransBouma/RawDataAccessBencher/blob/master/Results/20220801_net6.txt#L239 No caching: https://github.com/FransBouma/RawDataAccessBencher/blob/master/Results/20220801_net6.txt#L106

Materializing a resultset in a datatable isn't slow either. 37,000 rows takes 128ms, so 100 a lot less

Thanks! will look into it