Our DBA team exported the schema/database and imported it over to an Oracle 19c environment. Since repointing the web app to use the new schema on 19c we've seen a huge hit to performance.
After a ton of debugging I've targeted a table which is small enough to test out some ideas as it drives a view on a page which is performing badly. When I say badly, its render time was on average 2 secs on 11g and now taking up to 25 seconds on 19c.
I isolated a line of code using a LINQ statement to get a filtered dataset from one of the entities (which returns 13 records, so really nothing much).
In Devart there is a feature .ExecuteQuery which I've used as an alternative way to get the data and eliminate the potential of a poor performing LINQ statement.
Code: Select all
_dbContext.ExecuteQuery<T>("sql here");
Code: Select all
SELECT * FROM tableA WHERE condition1='Y' and condition2 = 'Y'
- I've since upgraded Devart LinqConnect to their latest version (as of yesterday).
- I've recreated the model with the assistance from LinqConnect's ORM (this was to eliminate any legacy settings that perhaps wasn't working well on 19c.
In the test app using the option 'Direct=true;' in the connection string actually improved the response times on the table down to something like 3 seconds. However, this setting doesn't have any noticable impact on our main app that uses Devarts own drivers.
I did find however in the Devart ORM a setting to 'delay loading' for fields of the table/model. I gave that a go and enabled delayed loading for both CLOB fields and voila, response times return back to normal. I guess this works because the data from the CLOB field isn't being accessed in realtime, but probably more like lazy loading at the field level maybe?
Unfortunately this doesn't solve the issue as the results are passed back up the chain where some code puts the results into another data model which is enriched with more data from related entity tables.
Code: Select all
public statifc Func<CustomerTable, CustomerViewModel> CustomerViewModelProjection
{
get
{
return cust => new CustomerViewModel
{
Name = cust.Name,
Surname = cust.Surname
Age = (int?) cust.Age
Rating = cust.Billing.Max(x => x.BillingStatic.RatingName)
}
}
}
In summary:
- The issue started by repointing the backend database from Oracle 11g to 19c.
- No code changes were done.
- LINQ to SQL as far as I can tell, isn't the issue.
- Recreating the entity model completely using the latest LinqConnect software from Devart made no tangible impact.
- CLOBs do seem to be a playing a direct role in the performance hit, but why would this happen?