I have DbLinq successfully bedded down in my application and now need
to reduce its CPU usage. To that end I've run some very quick and
dirty performance profiling which points fairly blatanly to the DbLinq
query cache as being a good way to do that. I've put the code used and
the results below in case anyone else is interested. I'm now going to
have a crack at the bug in the DbLinq query cache mechanism which
causes query parameters to be cached when they shouldn't be.
Test Methodology:
TimeSpan startCPUTime = Process.GetCurrentProcess
().TotalProcessorTime;
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
Run the test
stopWatch.Stop();
TimeSpan endCPUTime = Process.GetCurrentProcess
().TotalProcessorTime;
Console.WriteLine("cpu total=" + endCPUTime.Subtract
(startCPUTime).TotalSeconds + "s.");
Console.WriteLine("test took " + stopWatch.ElapsedMilliseconds +
"ms.");
The database schema and mapping is an empty test table.
[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() { }
}
Test 1 - MySQL direct:
for (int index = 0; index < 1000; index++) {
using (MySqlConnection dbConn = new MySqlConnection(connStr)) {
dbConn.Open();
IDbCommand command = new MySqlCommand("select count(*) from test
where inserted > ?1", dbConn);
command.Parameters.Add(new MySqlParameter("1", DateTime.Now));
int count = Convert.ToInt32(command.ExecuteScalar());
Console.WriteLine("count=" + count + ".");
}
}
Test 2 - DbLinq:
MySqlConnection dbConn = new MySqlConnection(connStr);
MySqlVendor vendor = new MySqlVendor();
for (int index = 0; index < 1000; index++) {
DataContext dataContext = new DataContext(dbConn, vendor);
Table<Test> testTable = (Table<Test>)dataContext.GetTable(typeof
(Test));
DateTime insertedAt = DateTime.Now;
int count = testTable.Count(t => t.Inserted > insertedAt);
Console.WriteLine("count=" + count + ".");
}
Test 3 - DbLinq with query cache enabled:
Same as Test 2 but with the line below to turn the query cache on.
dataContext.QueryCacheEnabled = true;
Results:
Test 1 - MySQL direct:
1. CPU Time=0.640s CPU & Test Time=1176ms,
2. CPU Time=0.593s CPU & Test Time=1073ms,
3. CPU Time=0.562s CPU & Test Time=993ms,
Test 2 - DbLinq:
1. CPU Time=4.493s CPU & Test Time=5244ms,
2. CPU Time=4.680s CPU & Test Time=5143ms,
3. CPU Time=4.680s CPU & Test Time=5268ms,
Test 3 - DbLinq with query cache enabled:
1. CPU Time=1.430s CPU & Test Time=2076ms,
2. CPU Time=1.590s CPU & Test Time=2181ms,
3. CPU Time=1.470s CPU & Test Time=2014ms,
Conclusion:
- Using DbLinq without the query cache on a simplistic count query
results in a 7 to 8x increase in CPU compared to using hand crafted
SQL.
- Using DbLinq with the query cache on a simplisitic count query
results in a 2 to 3x increase in CPU compared to using hand crafted
SQL.
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
-~----------~----~----~----~------~----~------~--~---
