Hello,
we are using OpenAccess ORM 2010.2.714.1
In our solution we have a Web Application and a Class Library (DAL) that we use to read the data stored in a SQL Server 2005 DB installed in another server. The OpenAccess features are enabled only for the DAL Class Library.
Everything works, but we have a “lookup” procedure that is very slow because there are many “select” on the same tables in the SQL DB to load the required objects.
So we have activated the L2 cache to load the entire table (objects) data in the cache and avoid the execution of many select in the DB.
But after we have activated the L2 cache, the select statements are continuing to be executed in the DB and the data are not read from the L2 cache.
We have added the L2 cache enablement in the App.config file (using the Telerik OpenAccess BackEnd Configuration settings menu) :
<backendconfigurations> <backendconfiguration id="mssqlConfiguration" backend="mssql"> <mappingname>mssqlMapping</mappingname> <l2CacheEnabled>True</l2CacheEnabled> <l2QueryCacheEnabled>True</l2QueryCacheEnabled> <l2QueryCacheMaxQueries>10000</l2QueryCacheMaxQueries> <l2CacheMaxObjects>1000000</l2CacheMaxObjects> <pmCacheRefType>STRONG</pmCacheRefType> </backendconfiguration> </backendconfigurations> We load the connection data dynamically, so also we have added in the sub AdjustForDynamicLoad the following parameters:
Dim assumedInitialConfiguration As String = "<openaccess>" _ & "<references>" _ & "<reference assemblyname='PLACEHOLDER' configrequired='True'/>" _ & "</references>" _ & "<connections>" _ & "<connection id=""DatabaseConnection1"">" _ & "<databasename>" + lstrDatabaseName + "</databasename>" _ & "<servername>" + lstrServerName + "</servername>" _ & "<user>" + lstrUsername + "</user>" _ & "<password>" + lstrPassword + "</password>" _ & "<integratedSecurity>False</integratedSecurity>" _ & "<backendconfigurationname>mssqlConfiguration</backendconfigurationname>" _ & "</connection>" _ & "</connections>" _ & "<backendconfigurations>" _ & "<backendconfiguration id=""mssqlConfiguration"" backend=""mssql"">" _ & "<mappingname>mssqlMapping</mappingname>" _ & "<l2CacheEnabled>True</l2CacheEnabled>" _ & "<l2QueryCacheEnabled>True</l2QueryCacheEnabled>" _ & "<l2QueryCacheMaxQueries>10000</l2QueryCacheMaxQueries>" _ & "<l2CacheMaxObjects>1000000</l2CacheMaxObjects>" _ & "<pmCacheRefType>STRONG</pmCacheRefType>" _ & "</backendconfiguration>" _ & "</backendconfigurations>" _ & "</openaccess>" Dim dll As System.Reflection.Assembly = theObjectScopeProvider1.GetType().Assembly assumedInitialConfiguration = assumedInitialConfiguration.Replace("PLACEHOLDER", dll.GetName().Name) Dim xmlDoc As New System.Xml.XmlDocument() xmlDoc.LoadXml(assumedInitialConfiguration) Dim db As Database = Telerik.OpenAccess.Database.Get("DatabaseConnection1", xmlDoc.DocumentElement, New System.Reflection.Assembly() {dll}) theObjectScopeProvider1._myDatabase = db We have tried with “pmCacheRefType” = AUTO and =STRONG, but the result is the same.
For every class that we need to store in the L2 cache we have added the cache strategy parameter in the App.config file:
<extension key="cache-strategy" value="all" /> But every time in the same thread (web request) we read these objects the data are retrieved from the DB instead from the L2 cache.
This is the code used to read the objects:
Protected Function GetObjectList(Of T)(ByVal pstrQuery As String, _ Optional ByVal pblnForwardsOnly As Boolean = True, _ Optional ByVal parrParams As Object() = Nothing) As IList(Of T) Dim llist As List(Of T) = Nothing Dim lobjQuery As Telerik.OpenAccess.IQuery = mobjObjectScope.GetOqlQuery(pstrQuery) lobjQuery.ForwardsOnly = pblnForwardsOnly Dim lobjQueryResult As Telerik.OpenAccess.IQueryResult = lobjQuery.Execute(parrParams) If ((lobjQueryResult IsNot Nothing) AndAlso (lobjQueryResult.Count > 0)) Then llist = New List(Of T) For Each p As T In lobjQueryResult llist.Add(p) Next End If Return llist End FunctionThis is an example of pstrQuery:
SELECT office FROM clsDmlOfficeVOExtent AS office WHERE office.OfficeCode = $1In the “for each” loop if we test if the “p” object is cached we get:
mobjObjectScope.Database.Cache.IsCached(mobjObjectScope.GetObjectId(p)) = TRUE
The queries are executed in the same loop calling the GetObjectList function, so we have:
1) "SELECT office FROM clsDmlOfficeVOExtent AS office WHERE office.OfficeCode = ‘XX’"
2) "SELECT office FROM clsDmlOfficeVOExtent AS office WHERE office.OfficeCode = ‘YY’"
…
N) "SELECT office FROM clsDmlOfficeVOExtent AS office WHERE office.OfficeCode = ‘NN’"
We have enabled the log and every time we execute the same query with different parameter, the select is executed in the DB and the data are not retrieved from the L2 cache, so the operations are very slow.
Please can you help us?
Best Regards.
Joseph
6 Answers, 1 is accepted
Rank 1
Your configuration seems to be OK from what you have posted here.
We had some issues regarding this, too, but they went away when we moved the L2 cache configuration, that is the those in the backend configuration tag to the web.config file instead.
Please try it, and let me know if you have success..
UPDATE:
This is the L2 cache configuration we're using (from the web.config):
<performanceCounter>false</performanceCounter>
<lockTimeout>5000</lockTimeout>
<l2CacheEnabled>True</l2CacheEnabled>
<l2CacheMaxObjects>10000000</l2CacheMaxObjects>
<l2QueryCacheEnabled>True</l2QueryCacheEnabled>
<l2QueryCacheMaxQueries>10000</l2QueryCacheMaxQueries>
<ext.cache-strategy>yes</ext.cache-strategy>
<logging.logEvents>none</logging.logEvents>
We experienced a massive performance boost using the L2 cache - so go ahead - it really pays back
Regards
Henrik
Rank 1
thank you for your answer!
I have added your L2 cache configuration in the web.config, in the App.config and in the sub AdjustForDynamicLoad, but unfortunately the result is always the same: the performances are not changed.
The execution time of the read loop is not changed, the queries are always executed in the SQL DB and it's very slow.
Please, is there anything we can do?
Regards.
Joseph
How big is the query result? We are not caching query results that contain more than 500 objects. If that is the case, can you use paging for the result?
Kind regards,
Jan Blessenohl
the Telerik team
Rank 1
Thanks for joining in. I need some elaboration on your statement.
Maybe I am misunderstanding something, but as per my understanding of the L2 cache, the retrieved objects (from query and looped in foreach statement) will still be put in the L2 (object) cache, even if you are traversing more than 500 objects (in the query results), right?
Thanks for your time.
Regards
Henrik
Rank 1
Hi Jan,
thank you for your reply.
Every query returns only one record.
For every class that we need to store in the L2 cache we have added the cache strategy parameter in the App.config file:
<extension key="cache-strategy" value="all" />So we think that when the first query is executed the complete list of objects for these classes is loaded in the L2 cache.
The maximum number of objects contained in the SQL DB tables (complete lists) is 270.
We have tried to preload the complete lists before the loop that reads the single objects, but the result is the same: the queries are always executed in the SQL DB.
Thanks.
Kind Regards.
Joseph
By writing the example code I found out that I was also not thinking in the right direction.
There is a difference between a cached query result and a cached object. The cache strategy all setting caches objects, but we cannot cache all query results. If you use scope.GetObjectById() the object cache is used, if you use scope.Extent<TestClass>().Where(x=>x.id == 12) this is a query and we look into the query result cache, if it has been excuted already, what is maybe not the case in your use case.
The cache strategy all is more meant for navigation, you have a zip-code table and all address objects are referencing the same zip code objects. When you navigate from the address to the zip-code and the zip-codes are in the cache there are no extra queries.
In your case you should produce at startup an IObjectId list of the table you want to cache, now you can make a loop over the list in each scope and call GetObjectByID. This works only if no new objects are stored in that table.
Does that makes sense for you?
I have attached my test project. It uses the old mapping, If you use the new way with the graphical designer I can make another example for you.
Kind regards,
Jan Blessenohl
the Telerik team