Print Page | Close Window

Query Performance

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=2870
Printed Date: 13-May-2026 at 9:39pm


Topic: Query Performance
Posted By: Vonzkie
Subject: Query Performance
Date Posted: 03-Aug-2011 at 6:36pm
Hi,

Using the NorthwindIB database, I inserted 50k records in Order Table.

When I used the native SQL Adapter, it only took me a second to fetch the data.

SqlDataAdapter
adap = new SqlDataAdapter("SELECT TOP 50000 * FROM dbo.[Order]", @"Data Source=IVON\SQL2008R2;Initial Catalog=NorthwindIB;Integrated Security=SSPI;");

DataSet
dt = new DataSet();

adap.Fill(dt);

But when I execute this query using Devforce:
 
          NorthwindIBEntities _context = new NorthwindIBEntities();
  var orderquery = _context.Orders.Select(p => p).OrderBy(p => p.OrderID).Skip(0).Take(50000);
           _context.ExecuteQueryAsync(orderquery, p =>
            {
                if (!p.HasError)
                {
                    // My code here..
                }
            }, null);

It took me 30 seconds to finish the async query..

Why is this so? Am I missing some configuration or performance tips?
Another, when I use the SQL Profiler to get the SQL query provided by Entity Framework and execute it in the Management Studio, it also took me a second to finish it.

Your help is highly appreciated. :)

Thanks,
Von 


Replies:
Posted By: Vonzkie
Date Posted: 03-Aug-2011 at 7:16pm
Hi,

Another thing is that, when I used the native Entity Framework it only took me a second to load
50k records.

            NorthwindIBEntities context = new NorthwindIBEntities();

            var query = context.Orders.Take(50000);

            List<Order> orders = query.ToList<Order>();

I rewrite my Devforce query to this one and it took me 7 seconds to load it, faster than my first approach but still far behind the performance of native EF4 and this query is not async:
            
            NorthwindIBEntities _context = new NorthwindIBEntities();

    IEntityQuery<Order> orderquery = _context.Orders.Take(50000);

            List<Order> orders = orderquery.ToList<Order>();
    

Kindly give me some guide on how to accomplish same performance as the native EF4 because we are very particular with the performance because we are not only dealing with thousands of records but millions.

Thanks,
Von

Posted By: sbelini
Date Posted: 05-Aug-2011 at 3:36pm
Hi Von,
 
When you tested using DevForce, were you doing in 2-tier or n-tier?
 
Silvio.

Posted By: Vonzkie
Date Posted: 06-Aug-2011 at 8:17am
Hi,

I tested it in a 2-tier environment by setting the isDistributed property to false.

Thanks,
Von

Posted By: sbelini
Date Posted: 09-Aug-2011 at 3:19pm
Hi Vonzkie,
Entity Framework is slightly faster than DevForce considering DevForce has some overhead over EF and is optmized for n-tier.
 
A few suggestions:
 
1) Try turning Query Inversion off:
 
orderquery.QueryStrategy = new QueryStrategy(context.DefaultQueryStrategy.FetchStrategy, context.DefaultQueryStrategy.MergeStrategy, QueryInversionMode.Off);
You can find more information about query inversion in the http://drc.ideablade.com/xwiki/bin/view/Documentation/query-inversion - DevForce Resource Center .
 
2) You could also try querying a reasonable number the entities, i.e. 50, after async querying for everything
3) As a last resource, you could always user SqlDataReader to populate a POCO. You can store POCOs in the EntityManager's cache and query them. You can find additional information about POCOs in our http://drc.ideablade.com/xwiki/bin/view/Documentation/POCOs -


Print Page | Close Window