Issue 231 in dblinq2007: ExecuteQuery<T> is very slow.
dblin...@googlecode.com
Owner: jonmpryor
Labels: Type-Enhancement Component-DbLinq Performance Priority-Low
New issue 231 by jonmpryor: ExecuteQuery<T> is very slow.
http://code.google.com/p/dblinq2007/issues/detail?id=231
From: http://groups.google.com/group/dblinq/msg/ee58f93df8ac5cb1
I tested with simple query, returning few rows. DataContext.ExecuteQuery<T>
is extremely slow.
It takes 400 ms while same query executed from pgAdmin takes 2 ms.
I tried to profile dbLinq but havent found cause.
Speed degradation seems to be linear to number of columns returned and
occurs in enumerator which returns data form datareader.
I replaced in my application DataContext ExecuteQuery calls with Marc
Gravell original code which I initially commited as ExecuteQuery code to
DbLinq some years ago (below), converatsion is in
http://www.eggheadcafe.com/software/aspnet/31850600/creating-executequery-met.aspx
In this case ExecuteQuery takes only 2 ms to run, 100 times (!) faster.
This does does not handle decimal-> int and other conversions like DbLinq
method, InvalidCastexception occurs in this case.
Andrus.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Linq.Mapping;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq.Expressions;
using System.Reflection;
using System.Data.Common;
using System.Linq;
/// <summary>
/// Compares arrays of objects using the supplied comparer (or default
is none supplied)
/// </summary>
class ArrayComparer<T> : IEqualityComparer<T[]>
{
private readonly IEqualityComparer<T> comparer;
public ArrayComparer() : this(null) { }
public ArrayComparer(IEqualityComparer<T> comparer)
{
this.comparer = comparer ?? EqualityComparer<T>.Default;
}
public int GetHashCode(T[] values)
{
if (values == null) return 0;
int hashCode = 1;
for (int i = 0; i < values.Length; i++)
{
hashCode = (hashCode * 13) +
comparer.GetHashCode(values[i]);
}
return hashCode;
}
public bool Equals(T[] lhs, T[] rhs)
{
if (ReferenceEquals(lhs, rhs)) return true;
if (lhs == null || rhs == null || lhs.Length != rhs.Length)
return false;
for (int i = 0; i < lhs.Length; i++)
{
if (!comparer.Equals(lhs[i], rhs[i])) return false;
}
return true;
}
}
/// <summary>
/// Represents a single bindable member of a type
/// </summary>
internal class BindingInfo
{
public bool CanBeNull { get; private set; }
public MemberInfo StorageMember { get; private set; }
public MemberInfo BindingMember { get; private set; }
public BindingInfo(bool canBeNull, MemberInfo bindingMember,
MemberInfo storageMember)
{
CanBeNull = canBeNull;
BindingMember = bindingMember;
StorageMember = storageMember;
}
public Type StorageType
{
get
{
switch (StorageMember.MemberType)
{
case MemberTypes.Field:
return ((FieldInfo)StorageMember).FieldType;
case MemberTypes.Property:
return ((PropertyInfo)StorageMember).PropertyType;
default:
throw new
NotSupportedException(string.Format("Unexpected member-type: {0}",
StorageMember.Name));
}
}
}
}
/// <summary>
/// Responsible for creating and caching reader-delegates for compatible
/// column sets; thread safe.
/// </summary>
static class InitializerCache<T>
{
/// <summary>
/// Cache of all readers for this T (by column sets)
/// </summary>
static readonly Dictionary<string[], Func<IDataRecord,
MyDataContext, T>> convertReaders
= new Dictionary<string[], Func<IDataRecord, MyDataContext, T>>(
new
ArrayComparer<string>(StringComparer.InvariantCultureIgnoreCase)),
vanillaReaders = new Dictionary<string[], Func<IDataRecord,
MyDataContext, T>>(
new
ArrayComparer<string>(StringComparer.InvariantCultureIgnoreCase));
/// <summary>
/// Cache of all bindable columns for this T (by source-name)
/// </summary>
private static readonly SortedList<string, BindingInfo> dataMembers
= new SortedList<string,
BindingInfo>(StringComparer.InvariantCultureIgnoreCase);
static bool TryGetBinding(string columnName, out BindingInfo
binding)
{
return dataMembers.TryGetValue(columnName, out binding);
}
const BindingFlags FLAGS = BindingFlags.Instance |
BindingFlags.Public | BindingFlags.NonPublic;
const MemberTypes PROP_FIELD = MemberTypes.Property |
MemberTypes.Field;
private static MemberInfo GetBindingMember(string name)
{
Type type = typeof(T);
return FirstMember(type.GetMember(name, PROP_FIELD, FLAGS))
?? FirstMember(type.GetMember(name, PROP_FIELD, FLAGS |
BindingFlags.IgnoreCase));
}
static InitializerCache()
{
Type type = typeof(T);
foreach (MemberInfo member in type.GetMembers(FLAGS))
{
if ((member.MemberType & PROP_FIELD) == 0) continue; // only
applies to prop/fields
ColumnAttribute col = Attribute.GetCustomAttribute(member,
typeof(ColumnAttribute)) as ColumnAttribute;
if (col == null) continue; // not a column
string name = col.Name;
if (string.IsNullOrEmpty(name))
{ // default to self
name = member.Name;
}
string storage = col.Storage;
MemberInfo storageMember;
if (string.IsNullOrEmpty(storage) || storage == name)
{ // default to self
storageMember = member;
}
else
{
// locate prop/field: case-sensitive first, then
insensitive
storageMember = GetBindingMember(storage);
if (storageMember == null)
{
throw new InvalidOperationException("Storage member
not found: " + storage);
}
}
if (storageMember.MemberType == MemberTypes.Property &&
!((PropertyInfo)storageMember).CanWrite)
{ // write to a r/o prop?
throw new InvalidOperationException("Cannot write to
readonly storage property: " + storage);
}
// log it...
dataMembers.Add(name, new BindingInfo(col.CanBeNull, member,
storageMember));
}
}
private static MemberInfo FirstMember(MemberInfo[] members)
{
return members != null && members.Length > 0 ? members[0] :
null;
}
public static Func<IDataRecord, MyDataContext, T>
GetInitializer(string[] names, bool useConversion)
{
if (names == null) throw new ArgumentNullException();
Func<IDataRecord, MyDataContext, T> initializer;
Dictionary<string[], Func<IDataRecord, MyDataContext, T>> cache
=
useConversion ? convertReaders : vanillaReaders;
lock (cache)
{
if (!cache.TryGetValue(names, out initializer))
{
initializer = CreateInitializer(names, useConversion);
cache.Add((string[])names.Clone(), initializer);
}
}
return initializer;
}
private static Func<IDataRecord, MyDataContext, T>
CreateInitializer(string[] names, bool useConversion)
{
Trace.WriteLine("Creating initializer for: " + typeof(T).Name);
if (names == null) throw new ArgumentNullException("names");
ParameterExpression readerParam =
Expression.Parameter(typeof(IDataRecord), "record"),
ctxParam = Expression.Parameter(typeof(MyDataContext),
"ctx");
Type entityType = typeof(T),
underlyingEntityType =
Nullable.GetUnderlyingType(entityType) ?? entityType,
readerType = typeof(IDataRecord);
List<MemberBinding> bindings = new List<MemberBinding>();
Type[] byOrdinal = { typeof(int) };
MethodInfo defaultMethod = readerType.GetMethod("GetValue",
byOrdinal),
isNullMethod = readerType.GetMethod("IsDBNull", byOrdinal),
convertMethod =
typeof(MyDataContext).GetMethod("OnConvertValue", BindingFlags.Instance |
BindingFlags.NonPublic);
NewExpression ctor = Expression.New(underlyingEntityType); //
try this first...
for (int ordinal = 0; ordinal < names.Length; ordinal++)
{
string name = names[ordinal];
BindingInfo bindingInfo;
if (!TryGetBinding(name, out bindingInfo))
{ // try implicit binding
MemberInfo member = GetBindingMember(name);
if (member == null) continue; // not bound
bindingInfo = new BindingInfo(true, member, member);
}
//Trace.WriteLine(string.Format("Binding {0} to {1} ({2})",
name, bindingInfo.Member.Name, bindingInfo.Member.MemberType));
Type valueType = bindingInfo.StorageType;
Type underlyingType = Nullable.GetUnderlyingType(valueType)
?? valueType;
// get the rhs of a binding
MethodInfo method = readerType.GetMethod("Get" +
underlyingType.Name, byOrdinal);
Expression rhs;
ConstantExpression ordinalExp = Expression.Constant(ordinal,
typeof(int));
if (method != null && method.ReturnType == underlyingType)
{
rhs = Expression.Call(readerParam, method, ordinalExp);
}
else
{
rhs = Expression.Convert(Expression.Call(readerParam,
defaultMethod, ordinalExp), underlyingType);
}
if (underlyingType != valueType)
{ // Nullable<T>; convert underlying T to T?
rhs = Expression.Convert(rhs, valueType);
}
if (bindingInfo.CanBeNull && (underlyingType.IsClass ||
underlyingType != valueType))
{
// reference-type of Nullable<T>; check for null
// (conditional ternary operator)
rhs = Expression.Condition(
Expression.Call(readerParam, isNullMethod,
ordinalExp),
Expression.Constant(null, valueType), rhs);
}
if (useConversion)
{
rhs = Expression.Convert(Expression.Call(ctxParam,
convertMethod, ordinalExp, readerParam,
Expression.Convert(rhs, typeof(object))),
valueType);
}
bindings.Add(Expression.Bind(bindingInfo.StorageMember,
rhs));
}
Expression body = Expression.MemberInit(ctor, bindings);
if (entityType != underlyingEntityType)
{ // entity itself was T? - so convert
body = Expression.Convert(body, entityType);
}
return Expression.Lambda<Func<IDataRecord, MyDataContext,
T>>(body, readerParam, ctxParam).Compile();
}
}
public class ValueConversionEventArgs : EventArgs
{
internal void Init(int ordinal, IDataRecord record, object value)
{
Ordinal = ordinal;
Record = record;
Value = value;
}
internal ValueConversionEventArgs() { }
public ValueConversionEventArgs(int ordinal, IDataRecord record,
object value)
{
Init(ordinal, record, value);
}
public int Ordinal { get; private set; }
public object Value { get; set; }
public IDataRecord Record { get; private set; }
}
public class MyDataContext
{
// re-use args to miniimze GEN0
private readonly ValueConversionEventArgs conversionArgs = new
ValueConversionEventArgs();
public event EventHandler<ValueConversionEventArgs> ConvertValue;
internal object OnConvertValue(int ordinal, IDataRecord record,
object value)
{
if (ConvertValue == null)
{
return value;
}
else
{
conversionArgs.Init(ordinal, record, value);
ConvertValue(this, conversionArgs);
return conversionArgs.Value;
}
}
public IEnumerable<T> ExecuteQuery<T>(string command, params
object[] parameters)
{
if (parameters == null) throw new
ArgumentNullException("parameters");
using (IDbConnection conn = DataAccessBase.CreateConnection())
// new SqlConnection(Program.CS))
using (IDbCommand cmd = conn.CreateCommand())
{
string[] paramNames = new string[parameters.Length];
for (int i = 0; i < parameters.Length; i++)
{
paramNames[i] = "@p" + i.ToString();
IDbDataParameter param = cmd.CreateParameter();
param.ParameterName = paramNames[i];
param.Value = parameters[i] ?? DBNull.Value;
cmd.Parameters.Add(param);
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(command, paramNames);
conn.Open();
using (IDataReader reader =
cmd.ExecuteReader(CommandBehavior.CloseConnection |
CommandBehavior.SingleResult))
{
if (reader.Read())
{
string[] names = new string[reader.FieldCount];
for (int i = 0; i < names.Length; i++)
{
names[i] = reader.GetName(i);
}
Func<IDataRecord, MyDataContext, T> objInit =
InitializerCache<T>.GetInitializer(names, ConvertValue != null);
do
{ // walk the data
yield return objInit(reader, this);
} while (reader.Read());
}
while (reader.NextResult()) { } // ensure any trailing
errors caught
}
}
}
}
}
--
You received this message because you are listed in the owner
or CC fields of this issue, or because you starred this issue.
You may adjust your issue notification preferences at:
http://code.google.com/hosting/settings
dblin...@googlecode.com
Comment #1 on issue 231 by pst2...@gmail.com: ExecuteQuery<T> is very slow.
http://code.google.com/p/dblinq2007/issues/detail?id=231
Hi Andrus, (Hope I don;t call the wrong name!)
DBLinq is a great idea and tool that really save us tones of time and
effort. Unfortunately some critical issues still happening as described
above ... that really draw it backward.
May I know what is the status of this issue? I'm having the same issue when
calling "ExecuteQuery<T>" (from generated DBML). It's extremely slow! I
hope to continue using this method as this give greater flexibility in
Project development.
Appreciate if any EXPERT can give some LIGHTS to this issue?
Because of this issue I turned to use Stored Procedure and that gives me
another issue as shown here
http://social.microsoft.com/Forums/zh/Offtopic/thread/4a7569d1-01f4-4136-a135-cae0f0df7e81?prof=required
I'm going no where now .... please!
Many thanks for any reply,
PS
dblin...@googlecode.com
Comment #2 on issue 231 by pst2...@gmail.com: ExecuteQuery<T> is very slow.
http://code.google.com/p/dblinq2007/issues/detail?id=231
In addition to my comment earlier. Here the link I found to this Slow issue
http://www.primaryobjects.com/CMS/Article99.aspx and again the questions
is .... is this safe to apply? Should project owner review and merge the
fixes for new release version?
Just wondering DBlinq is just STOPPED at version 0.20.1 since 2010-4-16
(http://code.google.com/p/dblinq2007/). How good if we can move forward to
next release with all these critical issue resolved?
Any reply is highly appreciated.
Thanks
PS