I am testing LLBLGEN Pro and NHibernate. The aim is to fill a standard Windows Datagrid from an Oracle Database:

Here´s the simple LLB Code:

RelationPredicateBucket bucket = new RelationPredicateBucket(); 
FilteredDispos               = new EntityCollection(new DispoEntityFactory());
bucket.PredicateExpression.Add(PredicateFactory.CompareValue(DispoFieldIndex.nr,     ComparisonOperator.LesserThan, 6400);
DataAdapter.FetchEntityCollection(FilteredDispos, bucket);
window_datagrid = FilteredDispos;

Info: Database Oracle 10, Editor: VS Studio 2005 .NET 2

I´ve used the same table, SQL-Statement, etc. Here´s the time that both needed to view the data in this grid ( 240 Columns to view ) Time in second´s.

DataRows LLBL NHIBERNATE 3200 ------- 13-------5 6400 ------- 25------- 8 12800 ----- 61------- 15

I don´t know what to say about this great differences. Maybe you can tell me something, what could be wrong ?

Thanks a lot, Slowhand

It's a known issue. LLBLGen Pro has more overhead behind the scenes as it fills default values for null values and has a more disconnected structure so the entities have to maintain change tracking internally which also eats some overhead. Also, the fetch logic doesn't add duplicate entities to the collection, which means it has to filter out dupes. This can be slower if the fetch can contain dupes and DISTINCT wasn't possible due to CLOB/BLOB types in the resultset, so the duplicate checks have to be done client-side.

We couldn't fix this in 1.0.x.y as it required deep architectural changes, however we've fixed for in v2.0. We're aware of the bottleneck of bulk entity fetches and sadly we couldn't address it properly in v1.0.x.y. We hope to have a beta of v2 within a month.

Normally you wouldn't run into the delay that much, as large bulk fetches are not that common, though we still wanted to address this. As a workaround, map a typedview on the view for fetching.

(edit): the slowdown is mainly located in the EntityField creation code. As you have a very wide entity, you have a lot of fields to create in a bulk fetch. With an entity with much fewer fields, you won't see such a big difference (though still a difference I'm afraid). As said, we're aware of the fact and can't fix it now, as it requires an architectural change, which we've made in v2.

We internally measure agains the datatable fetch logic of .NET 2.0, as that's the fastest data-retrieval mechanism there is (excluding datareaders). We hope to eliminate as much roadblocks for v2 so we come as close as possible to that datatable fetch speed. "As close as possible" sounds like we've already given up on making it faster than the datatable, but it's unlikely an O/R mapper is faster than the datatable fetch logic, because all the dataadapter.Fill method does is passing an object[] array, received from datareader.GetValues() to the datatable as a new datarow (which simply wraps that array).

It hurts to see that something else is faster in a given aspect of data-access, though I'm sure we're faster in other areas, it just depends on how/what you measure I guess. I can assure you, we'll do everything we can to eliminate as much hurdles in performance as possible in v2, to the limit where we have to stop to avoid removing features of course

Hi,

Could you try doing the same test with the cache enable in the PersistenceInfoFactory class that is located inside the DbSpecific project. I would like to see how the cache help.

Also you may try using a TypedViewand/or a TypedList.

<EDIT> You can also try to set the Collection DoNotPerformAddIfPresent property to false. And tell us if there is a difference in the results. I was wrong, Frans has told me that the object fetcher already sets that property to false before fetching.

To show you how much faster v2 will be

reading 15000 entities from a table made for benchmark testing so it has rows like these: (strings with '...' are max 2000 chars long)

49972 44E72890-8522-49CA-8FE4-74B3D6D21AB0 NULL 1684392854 HHHHHHH RRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRR...RRRR 10544 2006-03-30 20:57:53.680 1607360028 598868452 133639732 QQQQQQQQQQQQQQQQ 49973 51EAAB09-B498-4F11-8A37-447F0309715B NULL 1589852454 WWWWWWWWWW WWWWWWWWWWWWWWWWWWWWWWWWWWWWW...W 21398 2006-03-30 20:57:53.680 543772258 1063257734 2049876602 QQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQ 49974 8DC1A2B6-B732-4FFB-84CE-F72CA48DF1EB NULL 1529430701 HHHHHHHHHHHHHHHHHHHHHHHHHH UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU...U 30989 2006-03-30 20:57:53.680 735721376 151288707 296278016 IIIIIIIIIIIIII 49975 7F93E2BF-0EB4-4DEE-AF67-0ECFC97342FF NULL 240992659 EEEEEEEEEEEEEEEEEEEEEE BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB...B 1265 2006-03-30 20:57:53.680 2084314036 892980549 144329559 KKKKKKKKKKKKKK 49976 37E2F041-9C50-4FDE-ADF5-B3701EEA0D8F NULL 197733592 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD 2627 2006-03-30 20:57:53.680 1643525124 471780884 461938614 TTTTTTTTT

Test code:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SD.LLBLGen.Pro.ORMSupportClasses;

using BenchmarkData.Adapter;
using BenchmarkData.Adapter.DatabaseSpecific;
using BenchmarkData.Adapter.EntityClasses;
using BenchmarkData.Adapter.FactoryClasses;

namespace Net20Bencher
{
    class Program
    {
        private const int Amount = 15000;

        static void Main( string[] args )
        {
            DataTableBencher dtBencher = new DataTableBencher();
            dtBencher.Bench(Program.Amount);    // dummy, to get sqlserver fired up.
            dtBencher.Bench( Program.Amount );

            AdapterBencher aBencher = new AdapterBencher();
            aBencher.Bench( Program.Amount );
        }
    }


    public class DataTableBencher
    {
        public void Bench(int amount)
        {
            DataTable toFill = new DataTable();
            SqlConnection con = new SqlConnection("data source=nerd;initial catalog=BenchmarkData;integrated security=SSPI;persist security info=False;packet size=4096");
            SqlCommand cmd = new SqlCommand( string.Format( "SELECT TOP {0} * FROM RandomData", amount ), con );
            SqlDataAdapter adapter = new SqlDataAdapter( cmd );
            DateTime start = DateTime.Now;
            adapter.Fill( toFill );
            DateTime end = DateTime.Now;
            Console.WriteLine("Reading {0} rows into a datatable took {1}", toFill.Rows.Count, (end-start));
        }
    }


    public class AdapterBencher
    {
        public void Bench(int amount)
        {
            EntityCollection<RandomDataEntity> randomData = new EntityCollection<RandomDataEntity>( new RandomDataEntityFactory() );
            DateTime start = DateTime.Now;
            using( DataAccessAdapter adapter = new DataAccessAdapter() )
            {
                adapter.FetchEntityCollection( randomData, null, amount );
            }
            DateTime end = DateTime.Now;
            Console.WriteLine( "Reading {0} entities into an entity collection took {1}", randomData.Count, (end - start) );
        }
    }
}

result: Reading 15000 rows into a datatable took 00:00:01.5959736 Reading 15000 rows into a datatable took 00:00:01.5959736 Reading 15000 entities into an entity collection took 00:00:01.8776160

And that's DEBUG build LLBLGen Pro v2 code .

I've to do more benchmarking on .NET 1.x, with selfservicing and also with 1.0.2005.1 code, but needless to say, I'm pleased we're this close with datatables on this huge amount of data.