Entity Framework performance

[wkchen]

Is there any reason why Entity Framework takes much longer time to execute a query vs the same query against oracle directly in TOAD?

For example, in our code, when we execute ToList() on an entity, it would take 1 minute to finish the execution. If we trace the query entity framework is executing by calling the ToTraceString() and use that query to execute in TOAD, it takes just 2 sec to finish.

Am I missing anything? How can I improve the Entity framework? Need some suggestion urgently. thanks..

[AndreyR]

Take a look at these links:
1. Large Models.
2. Performance Considerations for Entity Framework Applications.
Hope this information is helpful.

[Shalex]

Please pay attention to view-pregeneration and compiled query in the mentioned articles.

[HCRoman]

This extension of CompiledQuery brings all together...
- use of compiled query,
- missed MergeOption for compiled query
- result function for directly evaluating the query

You can easy extend/shrink the signature, if you need no or more than one parameter (TArg1), TArg2, TArg3...

Code: Select all

		/// 
		/// Creates the compiled query from query if function is null.
		/// Sets the MergeOption to the source before compile and back after first invoke.
		/// Be sure, that the source of the query and this source are identical.
		/// At the end, invokes the compiled query and the resultFunction and return the result.
		/// The resultFunction is necessary to force the execution of the query
		/// 
		/// The entity model
		/// The type of the arg0.
		/// The type of the arg1.
		/// The type of the result query.
		/// The type of the result.
		/// The source.
		/// The arg0.
		/// The arg1.
		/// The merge option.
		/// The function.
		/// The query.
		/// The result function.
		/// The result expression.
		/// 
		///	this.AerzteAktuellMitBescheid = 
		///		this.RlvModel.ArztAktuellMitBescheidEntities.CreateAndInvokeCompiledQuery(
		///			this.RlvModel, 
		///			this, 
		///			MergeOption.OverwriteChanges,
		///			ref query_CalculateAerzteAktuellMitBescheid, 
		///			(ctx, ba) =>
		///					(from aamb in ctx.ArztAktuellMitBescheidEntities
		///					 where aamb.Betriebsstaetteid == ba.Betriebsstaetteid
		///					 select aamb),
		///			ref resultQuery_CalculateAerzteAktuellMitBescheid, 
		///			(qresult) => qresult.ToList());
		/// 
		/// 
		public static TResult CreateAndInvokeCompiledQuery(
			this ObjectQuery source,
			TArg0 arg0,
			TArg1 arg1,
			MergeOption mergeOption,
			ref Func function,
			Expression> query,
			ref Func resultFunction,
			Expression> resultExpression)
			where TArg0 : ObjectContext
		{
			TResult result = default(TResult);
			MergeOption? oldMergeOption = null;
			try
			{
				if (function == null)
				{
					if (source.MergeOption != mergeOption)
					{
						oldMergeOption = source.MergeOption;
						source.MergeOption = mergeOption;
					}
					function = CompiledQuery.Compile(query);
				}
				TResultQuery qresult = function.Invoke(arg0, arg1);
				if (resultFunction == null)
				{
					resultFunction = resultExpression.Compile();
				}
				result = resultFunction.Invoke(qresult);
			}
			finally
			{
				if (oldMergeOption.HasValue)
				{
					source.MergeOption = oldMergeOption.Value;
				}
			}
			return result;
		}

good luck


Roman

[AndreyR]

HCRoman, thank you for sharing your knowledge.