Generate Persistance code straight
from your database.
FrameworkGen generates
simple, clean, performant code that can
easily be understood and extended.

Performance

FrameworkGen uses SqlDataReaders for all of it's data retrieval. SqlDataReaders provide the fastest method of data retrieval in ADO.NET as they retrieve data using a read-only forward only mechanism.

Below are some simple benchmark tests comparing the code generated by FrameworkGen to other .NET data retrieval mechanisms to give you an example of the performance benefits the code generated by FrameworkGen can offer.

The database that was used for testing was the Microsoft's AdventureWorksDW2008R2 database. The test I was performing was to select all records from the DimCustomer table. This table has 18,484 rows and the following structure:

DimCustomer table structure

Test Batch 1

  • The code was executed in Release build to benefit from .NET code optimisation.
  • The following results were based on both Linq to SQL and the ADO.Net Entity Framework not using stored procedures
  • The first execution was ignored in the results as there is a delay when creating the first connection to the database.
  • The tests were executed 50 times and the time taken in milliseconds was recorded.

Test Batch 1 - Results


Run Number FrameworkGen Entities Linq to SQL ADO.NET Entity Framework FrameworkGen DataTable FrameworkGen Typed DataSet
1 252 504 429 437 901
2 348 435 372 406 635
3 282 522 337 418 648
4 300 502 424 378 705
5 341 434 354 427 640
6 303 514 440 390 691
7 295 421 352 415 636
8 299 503 427 391 698
9 307 438 357 425 637
10 301 505 435 382 714
11 309 439 355 426 639
12 300 503 425 390 706
13 305 438 357 420 632
14 299 507 431 394 700
15 310 438 364 423 637
16 301 505 435 390 717
17 309 440 352 424 640
18 300 506 439 391 705
19 311 443 370 433 651
20 303 508 429 386 703
21 303 432 352 424 637
22 304 509 433 396 708
23 300 436 357 422 641
24 304 514 432 394 705
25 312 439 357 426 638
26 302 505 429 404 600
27 310 542 370 511 624
28 301 491 418 409 653
29 260 484 423 395 673
30 270 505 418 401 667
31 287 508 417 403 678
32 267 507 420 393 668
33 275 509 418 391 666
34 274 495 418 391 667
35 271 505 421 395 677
36 267 504 419 391 664
37 271 505 418 408 672
38 270 502 427 386 674
39 267 508 417 397 673
40 273 507 418 400 679
41 270 504 422 392 677
42 289 501 419 397 674
43 272 502 417 411 670
44 281 503 423 393 677
45 268 507 424 390 680
46 267 506 430 401 671
47 274 502 421 396 673
48 267 500 421 394 679
49 272 505 422 394 673
50 270 504 419 403 697
Average 290 489 406 405 673

Test Batch 1 - Results Summary


  • You can see that FrameworkGen Entities had the best performance on average taking 290 milliseconds to retrieve the 18,484 records.
  • Both FrameworkGen DataTables and ADO.NET Entitry Framework were 39.65% slower.
  • Linq to SQL was 68.6% slower.
  • FrameworkGen Typed DataSets was 132% slower.


Test Batch 2

  • Linq to SQL and ADO.NET Entity Framework are both using stored procedures for data retrieval.

Test Batch 2 - Results


Run Number FrameworkGen Entities Linq to SQL ADO.NET Entity Framework FrameworkGen DataTable FrameworkGen Typed DataSet
1 305 513 371 503 590
2 350 442 419 370 668
3 310 406 398 447 596
4 353 396 388 404 636
5 280 491 362 479 598
6 377 439 422 370 657
7 294 433 406 469 602
8 360 401 381 418 648
9 293 483 348 425 580
10 371 425 389 370 614
11 353 412 374 412 651
12 281 487 359 424 638
13 253 450 389 381 611
14 363 421 387 368 647
15 293 410 369 414 649
16 281 484 364 475 595
17 347 438 404 471 619
18 254 456 390 372 610
19 358 415 395 469 624
20 253 446 391 377 626
21 362 419 388 470 626
22 258 446 386 376 627
23 365 413 392 464 629
24 257 448 393 369 598
25 351 417 398 461 624
26 254 447 392 381 607
27 356 416 401 478 623
28 256 445 389 373 599
29 350 414 396 466 620
30 256 450 386 373 595
31 353 419 393 476 627
32 264 451 387 372 603
33 382 415 397 465 622
34 256 448 391 370 607
35 352 415 392 472 621
36 257 444 389 372 615
37 357 417 398 469 631
38 257 452 398 376 606
39 357 419 402 469 627
40 256 454 390 377 640
41 374 414 436 474 652
42 301 486 429 410 673
43 306 526 357 427 675
44 268 482 428 382 706
45 310 415 361 427 700
46 268 479 420 391 709
47 311 412 361 408 672
48 271 483 420 389 716
49 315 418 359 422 681
50 272 477 432 385 712
Average 310 442 391 419 634

Test Batch 2 - Results Summary


  • FrameworkGen Entities has the best performance and on average took 310 milliseconds to retrieve 18,484 records.
  • ADO.NET Entity Framework was 26% slower.
  • FrameworkGen DataTables were 35% slower.
  • Linq to SQL was 42.5% slower.
  • FrameworkGen Typed DataSets were 104.5% slower.


Code for benchmark testing

class Program
    {
        private static Dictionary<string, List<long>> _dictionary = new Dictionary<string, List<long>>();
        private static Stopwatch _stopWatch = new Stopwatch();
        private static bool _skipFirst = false;
        private static int _index = -1;
        private static bool _storedProcedures = false;


        static void Main(string[] args)
        {
            int loopUntil = 51;
            _skipFirst = true;

            for (int j = 0; j < 2; j++)
            {
                SetupDictionary();
                _storedProcedures = (j == 0 ? false : true);

                for (int i = 0; i < loopUntil; i++)
                {
                    _index = i;

                    FrameworkGenEntities();
                    LinqToSql();
                    ADONetEntities();
                    FrameworkGenDataTable();
                    FrameworkGenTypedDataSet();
                }

                WriteOutResults(loopUntil);
            }

            Console.ReadLine();
        }


        private static void SetupDictionary()
        {
            _dictionary = new Dictionary<string, List<long>>();
            _dictionary.Add("FrameworkGen", new List<long>());
            _dictionary.Add("Linq", new List<long>());
            _dictionary.Add("Entity", new List<long>());
            _dictionary.Add("DataTable", new List<long>());
            _dictionary.Add("TypedDataSet", new List<long>());
        }

        static void FrameworkGenEntities()
        {
            Console.Write(".");
            DimCustomerBusiness biz = new DimCustomerBusiness();
            _stopWatch.Reset();
            _stopWatch.Start();
            DimCustomers entities = biz.GetAll();
            _stopWatch.Stop();
            entities = null;
            if (_index != 0 || (_index == 0 && !_skipFirst))
                _dictionary["FrameworkGen"].Add(_stopWatch.ElapsedMilliseconds);
            System.Threading.Thread.Sleep(10);
        }

        static void LinqToSql()
        {
            ConsoleApplication1.LinqToSql.LinqToSqlDataContext linqContext = new ConsoleApplication1.LinqToSql.LinqToSqlDataContext();
            Console.Write(".");
            _stopWatch.Reset();
            _stopWatch.Start();
            if (_storedProcedures)
            {
                List<ConsoleApplication1.LinqToSql.gsp_DimCustomerGetAllResult> result = linqContext.gsp_DimCustomerGetAll().ToList();
                _stopWatch.Stop();
                result = null;
            }
            else
            {
                var query = from cust in linqContext.DimCustomers
                            orderby cust.CustomerKey ascending
                            select cust;
                List<ConsoleApplication1.LinqToSql.DimCustomer> books = query.ToList<ConsoleApplication1.LinqToSql.DimCustomer>();
                _stopWatch.Stop();
                books = null;
            }
            if (_index != 0 || (_index == 0 && !_skipFirst))
                _dictionary["Linq"].Add(_stopWatch.ElapsedMilliseconds);
            System.Threading.Thread.Sleep(10);
        }

        static void ADONetEntities()
        {
            Console.Write(".");
            // Recreate the model as it caches the result sets
            ConsoleApplication1.AdventureWorksDW2008R2Entities model = new ConsoleApplication1.AdventureWorksDW2008R2Entities();
            _stopWatch.Reset();
            _stopWatch.Start();
            if (_storedProcedures)
            {
                List<ConsoleApplication1.DimCustomer> result2 = model.DimCustomerGetAll().ToList<ConsoleApplication1.DimCustomer>();
                _stopWatch.Stop();
                result2 = null;
            }
            else
            {
                var query = from cust in model.DimCustomer
                            orderby cust.CustomerKey ascending
                            select cust;
                List<ConsoleApplication1.DimCustomer> result2 = query.ToList<ConsoleApplication1.DimCustomer>();
                _stopWatch.Stop();
                result2 = null;

            }
            if (_index != 0 || (_index == 0 && !_skipFirst))
                _dictionary["Entity"].Add(_stopWatch.ElapsedMilliseconds);
            System.Threading.Thread.Sleep(10);
        }

        static void FrameworkGenDataTable()
        {
            Console.Write(".");
            DataTable table = new DataTable();
            DimCustomerBusiness biz = new DimCustomerBusiness();
            _stopWatch.Reset();
            _stopWatch.Start();
            biz.GetAll(table);
            _stopWatch.Stop();
            table = null;
            if (_index != 0 || (_index == 0 && !_skipFirst))
                _dictionary["DataTable"].Add(_stopWatch.ElapsedMilliseconds);
            System.Threading.Thread.Sleep(10);
        }

        static void FrameworkGenTypedDataSet()
        {
            Console.Write(".");
            DimCustomerBusiness biz = new DimCustomerBusiness();
            DimCustomerDataSet dataSet = new DimCustomerDataSet();
            _stopWatch.Reset();
            _stopWatch.Start();
            biz.GetAll(dataSet);
            _stopWatch.Stop();
            dataSet = null;
            if (_index != 0 || (_index == 0 && !_skipFirst))
                _dictionary["TypedDataSet"].Add(_stopWatch.ElapsedMilliseconds);
            System.Threading.Thread.Sleep(10);
        }

        static void WriteOutResults(int loopUntil)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("Run Number,FrameworkGen Entities,Linq to SQL,ADO.Net Entity Framework,FrameworkGen DataTable,FrameworkGen TypedDataSet");
            
            for (int i = 0; i < (loopUntil - (_skipFirst ? 1 : 0)); i++)
            {
                sb.Append((i + 1).ToString() + "," + _dictionary["FrameworkGen"][i].ToString() + ",");
                sb.Append(_dictionary["Linq"][i].ToString() + ",");
                sb.Append(_dictionary["Entity"][i].ToString() + ",");
                sb.Append(_dictionary["DataTable"][i].ToString() + ",");
                sb.Append(_dictionary["TypedDataSet"][i].ToString());
                sb.AppendLine();
            }

            sb.Append("\"Average\",");
            sb.Append(Math.Round(_dictionary["FrameworkGen"].Average()).ToString() + ",");
            sb.Append(Math.Round(_dictionary["Linq"].Average()).ToString() + ",");
            sb.Append(Math.Round(_dictionary["Entity"].Average()).ToString() + ",");
            sb.Append(Math.Round(_dictionary["DataTable"].Average()).ToString() + ",");
            sb.Append(Math.Round(_dictionary["TypedDataSet"].Average()).ToString());
            sb.AppendLine();

            if (_storedProcedures)
                File.WriteAllText(@"c:\temp\PerformanceSP.csv", sb.ToString());
            else
                File.WriteAllText(@"c:\temp\PerformanceNonSP.csv", sb.ToString());
        }
    }