Performance

Performance

Postby Zoran » Thu 03 Sep 2009 13:19

Our 2-core web server with 2 GB RAM worked fine. Then I decided to use LINQ entity classes to access database.
After the installation, the web server was immediately overloaded. So I increased number of CPU cores to 8 and RAM to 12 GB. It was no solution because CPU load was constantly between 95 and 100% and 12 GB of RAM wasn't enough.
After a little profiling, I found that LINQ queries work between 2 and 10 times more slowly than plain text SQL. Furthermore, the second line in this code worked 120 times longer:
Table po = dataContext.GetTable();
PurchaseOrder single = po.Single(c => c.Key == key);
After metagenerating the code and changing the 2. line, the performance penalty decreased to 2 times, compared to plain SQL SELECT.
Now I have 8 idle cores, working seldom at more than 10% and 10 GB RAM too much.
Is that normal?
Zoran
 
Posts: 44
Joined: Thu 28 Apr 2005 21:55
Location: Zagreb, Croatia

Postby drichter » Fri 04 Sep 2009 08:58

Perform the data-access like this, and LINQ will generate a select whith the "key" field in the where clause, instead of grabbing the whole table first.

PurchaseOrder single = dataContext.PurchaseOrders.Single(c => c.Key == key));
drichter
 
Posts: 20
Joined: Fri 21 Aug 2009 14:44

Postby Zoran » Sat 05 Sep 2009 07:16

I thought the actual read would be deferred until the second command because the table wasn't used in the first one.
After using the code you proposed, I got the similar results. For a different table 200 LINQ reads took 2.308 sec. and plain SQL spent 0.078 sec., 30 times difference.
How about LINQ queries, do they have to last 2-10 times longer?
Zoran
 
Posts: 44
Joined: Thu 28 Apr 2005 21:55
Location: Zagreb, Croatia

Postby Shalex » Mon 07 Sep 2009 14:52

This code
Code: Select all
Table po = dataContext.GetTable();
PurchaseOrder single = po.Single(c => c.Key == key);
is absolutely identical to the following:
Code: Select all
PurchaseOrder single = dataContext.PurchaseOrders.Single(c => c.Key == key));

Only one row is retrieved in both cases. You can check this with the DataContext.Log property (specifies the destination to write the SQL query or command):
Code: Select all
      StringBuilder sb = new StringBuilder();
      db.Log = new System.IO.StringWriter(sb);

The delay is caused by the compilation of the LINQ query. Our compiler is slow. If you need to call the query 200 times, maybe CompiledQuery will be a better solution for you. Please try the following sample:
Code: Select all
      for (int i = 0; i < 200; i++) {
        TESTDataContext.TESTDataContext db = new TESTDataContext.TESTDataContext(oracleConnection1);
        TESTDataContext.PurchaseOrder order = GetPurchaseOrder(db, 10);
      }
...
    public static Func GetPurchaseOrder =
            Devart.Data.Linq.CompiledQuery.Compile(
                (TESTDataContext.PurchaseOrder ctx, int id) =>
                  (ctx.PurchaseOrder.Single(obj => obj.Key == id))
            );
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Postby Zoran » Thu 10 Sep 2009 22:01

The compiled query is much faster. For my example 0.391 sec. (LINQ) compared to 0.086 sec. (plain SQL). However, that it still 4.5 times more slowly and is awkward to write the delegates. A metagenerated data tier is significantly faster.
200 row fetches for one procedure are seldom needed, that is only for testing purposes. I wanted to say that a 2, 5 or 10 times more slowly data access may not be acceptable. For my application, it isn't.
Zoran
 
Posts: 44
Joined: Thu 28 Apr 2005 21:55
Location: Zagreb, Croatia


Return to dotConnect for PostgreSQL