Benchmark Linq2SQL, Subsonic2, Subsonic3 - Any other ideas to make them faster ?
- by Aristos
I am working with Subsonic 2 more than 3 years now...
After Linq appears and then Subsonic 3, I start thinking about moving to the new Linq futures that are connected to sql.
I must say that I start move and port my subsonic 2 with SubSonic 3, and very soon I discover that the speed was so slow thats I didn't believe it - and starts all that tests.
Then I test Linq2Sql and see also a delay - compare it with Subsonic 2.
My question here is, especial for the linq2sql, and the up-coming dotnet version 4, what else can I do to speed it up ? What else on linq2sql settings, or classes, not on this code that I have used for my messures
I place here the project that I make the tests, also the screen shots of the results.
How I make the tests - and the accurate of my measures.
I use only for my question Google chrome, because its difficult for me to show here a lot of other measures that I have done with more complex programs. This is the most simple one, I just measure the Data Read. How can I prove that. I make a simple Thread.Sleep(10 seconds) and see if I see that 10 seconds on Google Chrome Measure, and yes I see it.
here are more test with this Sleep thead to see whats actually Chrome gives.
10 seconds delay
100 ms delay
Zero delay
There is only a small 15ms thats get on messure, is so small compare it with the rest of my tests that I do not care about.
So what I measure
I measure just the data read via each method - did not count the data or database delay, or any disk read or anything like that. Later on the image with the result I show that no disk activity exist on the measures
See this image to see what really I measure and if this is correct
Why I chose this kind of test
Its simple, it's real, and it's near my real problem that I found the delay of subsonic 3 in real program with real data.
Now lets tests the dals
Start by see this image
I have 4-5 calls on every method, the one after the other.
The results are.
For a loop of 100 times, ask for 5 Rows, one not exist, approximatively..
Simple adonet:81ms
SubSonic 2 :210ms
linq2sql :1.70sec
linq2sql using CompiledQuery.Compile :239ms
Subsonic 3 :15.00sec (wow - extreme slow)
The project
http://www.planethost.gr/DalSpeedTests.rar
Can any one confirm this benchmark, or make any optimizations to help me out ?
Other tests
Some one publish here this link http://ormbattle.net/ (and then remove it - don not know why) In this page you can find a really useful advanced tests for all, except subsonic 2 and subsonic 3 that I have here !
Optimizing
What I really ask here is if some one can now any trick how to optimize the DALs, not by changing the test code, but by changing the code and the settings on each dal.
For example...
Optimizing Linq2SQL
I start search how to optimize Linq2sql and found
this article, and maybe more exist.
Finally I make the tricks from that page to run, and optimize the code using them all. The speed was near 1.50sec from 1.70.... big improvement, but still slow.
Then I found a different way - same idea article, and wow ! the speed is blow up.
Using this trick with CompiledQuery.Compile, the time from 1.5sec is now 239ms.
Here is the code for the precompiled...
Func<DataClassesDataContext, int, IQueryable<Product>> compiledQuery =
CompiledQuery.Compile((DataClassesDataContext meta, int IdToFind) =>
(from myData in meta.Products
where myData.ProductID.Equals(IdToFind)
select myData));
StringBuilder Test = new StringBuilder();
int[] MiaSeira = { 5, 6, 10, 100, 7 };
using (DataClassesDataContext context = new DataClassesDataContext())
{
context.ObjectTrackingEnabled = false;
for (int i = 0; i < 100; i++)
{
foreach (int EnaID in MiaSeira)
{
var oFindThat2P = compiledQuery(context, EnaID);
foreach (Product One in oFindThat2P)
{
Test.Append("<br />");
Test.Append(One.ProductName);
}
}
}
}
Optimizing SubSonic 3 and problems
I make many performance profiling, and start change the one after the other and the speed is better but still too slow. I post them on subsonic group but they ignore the problem, they say that everything is fast...
Here is some capture of my profiling and delay points inside subsonic source code
I have end up that subsonic3 make more call on the structure of the database rather than on data itself. Needs to reconsider the hole way of asking for data, and follow the subsonic2 idea if this is possible.
Try to make precompile to subsonic 3 like I did in linq2Sql but fail for the moment...
Optimizing SubSonic 2
After I discover that subsonic 3 is extreme slow, I start my checks on subsonic 2 - that I have never done before believing that is fast. (and it is)
So its come up with some points that can be faster. For example there are many loops like this ones that actually is slow because of string manipulation and compares inside the loop. I must say to you that this code called million of times ! on a period of few minutes ! of data asking from the program.
On small amount of tables and small fields maybe this is not a big think for some people,
but on large amount of tables, the delay is even more. So I decide and optimize the subsonic 2 by my self, by replacing the string compares, with number compares! Simple. I do that almost on every point that profiler say that is slow. I change also all small points that can be even a little faster, and disable some not so used thinks.
The results, 5% faster on NorthWind database, near 20% faster on my database with 250 tables. That is count with 500ms less in 10 seconds process on northwind, 100ms faster on my database on 500ms process time. I do not have captures to show you for that because I have made them with different code, different time, and track them down on paper.
Anyway this is my story and my question on all that, what else do you know to make them even faster...
For this measures I have use Subsonic 2.2 optimized by me, Subsonic 3.0.0.3 a little optimized by me, and Dot.Net 3.5
