Strange Performance Results

[asaake]

In my application I see slow grid fill behavior and could not locate the reason.

So I started a comparison of data access strategies and used a production table in a test schema for it. It contains 16054 rows.

At first I wanted to read them all, but the LinqToSql-Run didn't end, so I reduced the selection to 1000 rows.

The tests and results (2nd runs) are:

1. LinqToSQL read entities ca. 8000 ms
2. Fill DataTable from LinqToSQL query ca. 50 ms
3. LinqTOSQL read anonym. classes ca. 50 ms
4. Entity Framework Select Entities ca. 50 ms

The times for 2-4 are probably to small for deciding an order, but significantly smaller than 1 ( x 160!)

This are the tests:

----------------------------------------------------------------------------------
1. LinqToSQL
----------------------------------------------------------------------------------
private void btnLoadEntities_Click(object sender, RoutedEventArgs e) {
Stopwatch watch = new Stopwatch();

using (ASAAKEDataContext.ASAAKEDataContext ctx = new ASAAKEDataContext.ASAAKEDataContext()) {
//ctx.Log = Console.Out;
//ctx.ObjectTrackingEnabled = false;

watch.Start();

var q = (from c in ctx.bsj_asa
select c).Take(1000);

var l = q.ToList();

watch.Stop();

Console.WriteLine("Load Entities ms: " + watch.ElapsedMilliseconds);

tabEntities.IsSelected = true;
dgEntities.DataContext = l;
}
}

----------------------------------------------------------------------------------
2. DataTable
----------------------------------------------------------------------------------
private void btnLoadDataTable_Click(object sender, RoutedEventArgs e) {
DataTable dt = new DataTable();
Stopwatch watch = new Stopwatch();

using (ASAAKEDataContext.ASAAKEDataContext ctx = new ASAAKEDataContext.ASAAKEDataContext()) {
ctx.Log = Console.Out;
ctx.ObjectTrackingEnabled = false;

watch.Start();

var q = (from c in ctx.bsj_asa
select c).Take(1000);

using (IDbCommand cmd = ctx.GetCommand(q)) {
cmd.Connection = ctx.Connection;
cmd.Connection.Open();

using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
dt.BeginLoadData();
dt.Load(reader);
dt.EndLoadData();
}
}
watch.Stop();

Console.WriteLine("Load DataTable ms: " + watch.ElapsedMilliseconds);

tabDataTable.IsSelected = true;
dgDataTable.DataContext = dt;
}
}


----------------------------------------------------------------------------------
3. Anonymous Class
----------------------------------------------------------------------------------
private void btnLoadAnClass_Click(object sender, RoutedEventArgs e) {
Stopwatch watch = new Stopwatch();

using (ASAAKEDataContext.ASAAKEDataContext ctx = new ASAAKEDataContext.ASAAKEDataContext()) {
ctx.Log = Console.Out;
ctx.ObjectTrackingEnabled = false;

watch.Start();

var q = (from c in ctx.bsj_asa
select new {
aend = c.bsjo_aend,
ag = c.bsjo_ag,
art = c.bsjo_art,
at = c.bsjo_at,
berdat = c.bsjo_berdat,
bestdat = c.bsjo_bestdat,
bestf = c.bsjo_bestf,
dat = c.bsjo_dat,
kz = c.bsjo_kz,
mg = c.bsjo_mg,
num = c.bsjo_num,
pos = c.bsjo_pos,
se = c.bsjo_se,
user = c.bsjo_user,
wg = c.bsjo_wg,
zen = c.bsjo_zen
}).Take(1000);

var l = q.ToList();


watch.Stop();

Console.WriteLine("Load Entities ms: " + watch.ElapsedMilliseconds);

tabEntities.IsSelected = true;
dgEntities.DataContext = l;
}
}

----------------------------------------------------------------------------------
4. Entity Framework
----------------------------------------------------------------------------------
private void btnLoadEFEntities_Click(object sender, RoutedEventArgs e) {
Stopwatch watch = new Stopwatch();

using (DataSourceModel1.DataSourceModel1Entities ctx = new DataSourceModel1.DataSourceModel1Entities()) {
watch.Start();

var q = (from c in ctx.BsjAsas
select c).Take(1000);

var l = q.ToList();

watch.Stop();

Console.WriteLine("Load Entities ms: " + watch.ElapsedMilliseconds);

tabEntities.IsSelected = true;
dgEntities.DataContext = l;
}
}


----------------------------------------------------------------------------------
5. Table Definition
----------------------------------------------------------------------------------
-- Create table
create table BSJ_ASA
(
BSJO_WG NUMBER(2) not null,
BSJO_AG NUMBER(2) not null,
BSJO_SE NUMBER(2) not null,
BSJO_AT NUMBER(4) not null,
BSJO_ZEN NUMBER(5) not null,
BSJO_ART VARCHAR2(4) not null,
BSJO_NUM NUMBER(7) not null,
BSJO_POS NUMBER(5) not null,
BSJO_BESTF VARCHAR2(1) default ' ' not null,
BSJO_MG NUMBER default 0,
BSJO_DAT DATE,
BSJO_KZ VARCHAR2(1) default ' ',
BSJO_USER NUMBER(3) default 0,
BSJO_AEND DATE default SYSDATE not null,
BSJO_BERDAT DATE,
BSJO_BESTDAT DATE,
VID NUMBER(9)
)

The first 9 columns make the primary key.

The Models are created by dragging the table to the Modeler and generating code.

The Connection uses ODP.NET.

Can you imagine a reason for these results or have I missed something very important?

[AndreyR]

Thnak you for the report, we are investigating the LINQ to Oracle performance.
I will let you know about the results of our investigation.

[asaake]

Are you able to reproduce what I described?

I would like to know, if its a generally feature or problem of LinqToSQL.

Thanks!

[webx]

I have converted a LINQ to SQL project developed using Microsoft LINQ to SQL for SQL Server 2008 to use DevArt LINQ to Oracle with Oracle 11g and found that in many places the performance slows down by around 500%. There seems to be a serious problem with performance with LINQ to Oracle somewhere, I have yet to identify exactly where. I am using version 5.70.140.0

[asaake]

Hi,

coming back to my problem / question.

We have no general performance problems with LinqConnect. It is quite fast as you can see by reading anonymous classes.

But it seems to me there is an exponential magnitude of time correlating to the number of enities read into the context. So it isn't visible until you load a significant number of them. And it depends not on ObjectTrackingEnabled true or false.

We would like to know if you identified this as a problem worth a change in near future or if you cannot reproduce my findings.

The answer is important for us in deciding to stay with LinqConnect or to change to Entity Framework.

Thanks in advance.

Andreas

[AndreyR]

We have increased the select performance in the upcoming build.
Follow the announcements on the forum.

[asaake]

Running my test case again with Release 5.70.152.0 now I see similar results for all queries (all < 50ms) and so it's ok!

[AndreyR]

Glad to hear about these positive results.
Feel free to contact us if there are any other problems in our components.