I've been using DbLinq in my application in the wild now for a couple
of months and functionally it's working very well. However I've
encountered a performance issue and from observations of CPU usage a
crude estimate is that using DbLinq has increased CPU utilistation by
a thousand fold for some of my application functions. All my database
operations are very straight forward CRUD with queries kept as simple
as possible even to the point that joins are avoided.
I'm now delving into where the performance issue is occurring and the
first thing I've tried is to turn on the query cache. I previously had
to turn it off because some of my select queries weren't behaving as
expected. I've now been able to track that down a bit further and
suspect there is a bug in the query cache. Below is a block of code
that produces unexpected results for me.
class Program {
static void Main(string[] args) {
try {
Console.WriteLine("Starting DbLinqQueryCaching Test");
string connStr = "Database=sipsorcery;Data
Source=localhost;User Id=root;Password=password";
for (int index = 0; index < 2; index++) {
using (DataContext dataContext = GetDataContext
(connStr)) {
Table<Test> table = dataContext.GetTable<Test>
();
DateTime insertedAt = DateTime.Now;
IQueryable<Test> getList = from asset in
table.Where(t => t.Inserted < insertedAt) select asset;
getList.FirstOrDefault();
}
Thread.Sleep(3000);
}
}
catch (Exception excp) {
Console.WriteLine("Exception Main. " + excp.Message);
}
finally {
Console.WriteLine("finished, press any key to
exit...");
Console.ReadLine();
}
}
private static DataContext GetDataContext(string connStr) {
MySqlConnection dbConn = new MySqlConnection(connStr);
DataContext dataContext = new DataContext(dbConn, new
MySqlVendor());
dataContext.Log = Console.Out;
dataContext.QueryCacheEnabled = true;
return dataContext;
}
}
[Table(Name = "test")]
public class Test {
[Column(Storage = "_id", Name = "id", DbType = "character
varying(36)", IsPrimaryKey = true, CanBeNull = false)]
public string ID { get; set; }
[Column(Storage = "_inserted", Name = "inserted", DbType =
"timestamp", CanBeNull = false)]
public DateTime Inserted { get; set; }
public Test() { }
}
The truncated results of executing are:
Call (MethodCallExpression)
..Object: (null)
..Method: FirstOrDefault
..#00000 Constant (ConstantExpression)
....Value: DbLinq.Data.Linq.Implementation.QueryProvider`1
[DbLinqQueryCaching.Test]
SELECT id, inserted
FROM test
WHERE (inserted < ?insertedAt) LIMIT 1
-- ?insertedAt: Input DateTime (Size = 0; Prec = 0; Scale = 0)
[9/09/2009 12:17:48 PM]
-- Context: MySQL Model: AttributedMetaModel Build: 0.19.0.0
SELECT id, inserted
FROM test
WHERE (inserted < ?insertedAt) LIMIT 1
-- ?insertedAt: Input DateTime (Size = 0; Prec = 0; Scale = 0)
[9/09/2009 12:17:48 PM]
-- Context: MySQL Model: AttributedMetaModel Build: 0.19.0.0
finished, press any key to exit...
The problem is the insertedAt parameter for both queries is [9/09/2009
12:17:48 PM]. For the second query it should have been [9/09/2009
12:17:51 PM].
To get it to work correctly the following small change is required.
DateTime insertedAt = DateTime.Now;
for (int index = 0; index < 2; index++) {
using (DataContext dataContext = GetDataContext
(connStr)) {
Table<Test> table = dataContext.GetTable<Test>
();
insertedAt = DateTime.Now;
IQueryable<Test> getList = from asset in
table.Where(t => t.Inserted < insertedAt) select asset;
getList.FirstOrDefault();
}
Which produces correct results of:
Call (MethodCallExpression)
..Object: (null)
..Method: FirstOrDefault
..#00000 Constant (ConstantExpression)
....Value: DbLinq.Data.Linq.Implementation.QueryProvider`1
[DbLinqQueryCaching.Test]
SELECT id, inserted
FROM test
WHERE (inserted < ?insertedAt) LIMIT 1
-- ?insertedAt: Input DateTime (Size = 0; Prec = 0; Scale = 0)
[9/09/2009 12:20:30 PM]
-- Context: MySQL Model: AttributedMetaModel Build: 0.19.0.0
SELECT id, inserted
FROM test
WHERE (inserted < ?insertedAt) LIMIT 1
-- ?insertedAt: Input DateTime (Size = 0; Prec = 0; Scale = 0)
[9/09/2009 12:20:34 PM]
-- Context: MySQL Model: AttributedMetaModel Build: 0.19.0.0
Regards,
Greyman.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"DbLinq" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/dblinq?hl=en
-~----------~----~----~----~------~----~------~--~---
