SubSonic & LinqTemplate: Why is read so slow here?

Not sure where to get this info out to the subsonic crew as they took down their forums, and said use stackoverflow, so here we go.

I've been struggling to achieve maximum performance with an ORM. I love subsonic, and its bulk insert, update, delete obliterates Entity Framework in this context, however for straight simple reads, EF blows subsonic out of the water with 5x faster performance.

I've stripped both down, removed change tracking, foreign properties, navigational properties, everything down to poco.

EF executes a simple select of 1M rows in about 4.8, SubSonic Linq i开发者_如何学编程s taking 5x that...

looking at SQL profiler the call is slightly different: Entity Framework: SELECT TOP (1000000) [c].[Id] AS [Id], [c].[ProjectIDL] AS [ProjectIDL], [c].[DescriptorIDL] AS [DescriptorIDL], [c].[FieldIDL] AS [FieldIDL], [c].[Data] AS [Data], [c].[OptionId] AS [OptionId] FROM [dbo].[DescriptorFieldValues] AS [c]

SubSonic:SELECT TOP (1000000) [t0].[Data], [t0].[DescriptorIDL], [t0].[FieldIDL], [t0].[Id], [t0].[OptionId], [t0].[ProjectIDL] FROM [dbo].[DescriptorFieldValues] AS t0

SQL profiler is running a trace and showing a massive duration difference here.

I checked the audit login before both queries and they are identical...

If I run the same query in sql management studio it takes 11 seconds for both queries

Looking into the subsonic 3.04 source, I cannot figure out where I would make the adjustment to make it match EF, but is the query syntax here really making that big of a difference, or is there some magic going on I'm not aware of?

Thanks for your help!

Aside from the fact that your SubSonic query doesn't seem to be selecting the ID field, the two SQL statements are for all intents and purposes identical. Finding that the two queries take the same amount of time to execute in SQL Management Studio seems to support this.

That would seem to indicate that SubSonic does in fact take longer to query than EF. SubSonic is known to have some issues with performance that could most certainly explain this difference.

Really, though, we'd need far more detail on your exact usage to really pin down an answer to why this query is slower for you.

Also, if you are looking for the current activity and help with SubSonic, you should try their Google Group.

The creator of SubSonic, Rob Conery, stopped working on it quite a while ago, and the "SubSonic crew" haven't released any substantial updates (if any at all, I don't think they have but I'm not completely certain). It's a project that you may as well consider "finished" in that work on the project has ceased (for all practical purposes) but in a way more work isn't really needed exactly (it works).

By the way, that page that says the SubSonic forums are shut down and come to StackOverflow - well, those pages haven't been updated at all in over a year, maybe 2.

After getting berated by Rob Corney for wanting to learn why SubSonic is slow in comparison to EF, and told we are stupid for wanting to learn why SubSonic is broke, we believe our team has identified a couple places for these performance issues:

1. In Extensions/Database.cs Load uses per row, per property reflection to serialize the datarow to a concrete object.
2. In Extensions/Database.cs ToEnumerable all DataReader conversion are done in 1 sequential while loop.
3. in ExecutionBuilder - this querycompiler is a straight copy paste of an alpha for educational purposes only querycompiler and is woefully out of date using DynamicInvoke.

Our team plans to make the following modifications, in the following order:

1. In Extensions/Database.cs ToEnumerable pull the property infos once and pass them into load, this is believed to have mininmal performance impact, but likely a decent memory utilization impact.

2. Modify ToEnumerable to multithread the materialization for large datasets.

3. Add a method into the object factories to allow for construction of an object from a datarow without using reflection, and instead using the t4 template code generation ahead of time.
4. Inside load, check the object activator for this interface method, if so use this, if not, default back to reflective based serialization.
5. Update the ExceutionBuilder to avoid the use of DynamicInvoke.

Hopefully this should bring our performance needs to fruition.

Thank you to Jeff V, Ken I, and QES for their help. It's sad to see the creator of SubSonic Rob Corney so defensive about SubSonic's performance, when it appears to be fairly easy to solve. ~ JT

Subsonic 3 is so slow because its need to compile again and again and again the same thinks before its go to sql server and ask for results.

And this compile is happens on the linq part.

For example a simple static command like "Select * FROM Products WHERE ProductID > 100" that typed in subsonic 3 as Products.Find(x => (x.ProductID > 100) needs first a lot of time to converted to string command, and I mean a very long time.

This is the main reason why subsonic is so slow, and thats why subsonic 3 is worthless.

In linq the trick is the CompiledQuery.Compile function that compile it one time and remember it in memory. How ever for me and my standards is slow too.

Now back to subsonic 2, this is a really nice idea, but still need a lot of optimization inside. Apparently they not think too much the speed. Anyway with some optimization subsonic 2 can be super fast, near the simple ado commands.

My tests a year ago: Benchmark Linq2SQL, Subsonic2, Subsonic3 - Any other ideas to make them faster?

My idea is to go back to subsonic 2 and make it better and speedy, and drop the subsonic 3. I thank them for both subsonic 2 and 3, but in the version 3 they fail, its ok, I have made too many programs all that years, and not all of them used. Is not big deal.