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
