I came across this interesting problem at work and thought I’d share the problem and the eventual resolution (workaround).
I was asked to look into the performance of a screen in one of our applications which seemed OK in our Dev environment but slowed down drastically in Test and Prod.
The application uses Entity Framework 4.0 and the Oracle Data Provider (ODP) Release 4 for data access and .Net 4.0 with MVC3 Razor for the application.
So the first thing I did was fire up Hibernating Rhinos – Entity Framework Profiler. I can’t praise this tool enough as it has saved us so much time when working with LINQ and the Entity Framework. I think it paid for itself in the first week we got it.
The first thing I noticed was that a few of the queries seemed to be taking longer to execute than I would have expected so I copied the SQL Statement that EF had generated out of EFP and pasted it into SQL Developer. To my surprise, the query executed much faster in SQL Developer and the explain plan looked good.
So if the SQL was correct and the database was capable, it looked like the middle man was to blame. Oracle ODP. I started the application and asked one of the DBAs to run a trace on the database as I was performing the actions that triggered the SQL I was examining.
The SQL looked good on the way out but the DBA informed me that one of my bind variables which was a GUID was actually doing a conversion from the database type of VARCHAR2 to NVARCHAR. It was the conversion that was causing the overhead.
Looking further into the problem it would appear that ODP has an issue with string bind variables in .Net. Strings in .Net are Unicode by default and as the EF is providing Unicode strings to the ODP it causes the conversion of VARCHAR2 columns to NVARCHAR2 causing a considerable performance hit. What makes it worse is that the column that was being checked against was indexed and due to the conversion, the index was being ignored and the Oracle optimiser was performing full table scans. This explained the performance difference in the different environments as Test and Prod both contained considerably more data than Dev.
My first thought was to have a look at the EDMX and see if I’d missed something. To my pleasant surprise I noticed on the model properties there was a field called Unicode which had not been set! “Easy!” I thought, I set the value correctly and started everything backup excitedly waiting for the application to start performing correctly.
No Dice.
It would seem that the EF completely ignores that property so I was back to square one.
So after a bit of researching I came across this workaround which although I’m not happy with, it’ll do for now. Essentially on any string bind variables, wrap them with the following code
EntityFunctions.AsNonUnicode(variable);
so
var jobMeterReads = from i in this.context.JobItems
join m in this.context.JobMeterReads on i.JobItemID equals m.JobItemID
where i.ParentItemID == itemID
select m;
would become
var jobMeterReads = from i in this.context.JobItems
join m in this.context.JobMeterReads on i.JobItemID equals m.JobItemID
where i.ParentItemID == EntityFunctions.AsNonUnicode(itemID)
select m;
I’m going to keep looking for a better solution but for now that’s solved my issue.
