0

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 is 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!

JTtheGeek
  • 1,707
  • 14
  • 17
  • "EF executes a simple select of 1M rows in about 4.8, SubSonic Linq is taking 5x that..." At the very least, we should see the code that this assertion comes from, as well as what conditions it was observed under. I recall reading (but cannot find source) that SubSonic utilizes some dynamic code generation and therefore may have a high time cost for compilation initially. Also, question, how do you suppose EF can return a query in "4.8" (4.8 what, btw?) if it takes 11 seconds to run in the management studio? – quentin-starin May 09 '11 at 20:21
  • I was referring to the time from SQL monitor - looking at the time from the point SQL Server received it and processed it to the time it returned results, and least that's what I believe... – JTtheGeek May 09 '11 at 21:20
  • You still haven't established any real basis for comparison, and I'm still quite confused about where you are getting your numbers from. Are you saying the 4.8 and the "5x that" are from what SQL Monitor reports? If you're asking about performance it helps to be exceptionally explicit and detailed. I cannot help anymore without much more detail. – quentin-starin May 09 '11 at 21:36
  • BTW - We built some custom templates and edited SubSonic just slightly and were able to get blistering performance out of it. Feel free to contact me if you want assistance. – JTtheGeek Jul 24 '12 at 21:34

3 Answers3

1

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.

quentin-starin
  • 26,121
  • 7
  • 68
  • 86
  • while I appreciate your answer qes, it does not address my actual questions of how is one of those queries faster than the other in this case, nor how I might be able to make this enhancement in subsonic other than talk to the dead sub-sonic team. It is however a good step in one direction, but was hoping to get more info on the query performance issue. – JTtheGeek May 09 '11 at 19:21
  • well, you don't really give anywhere near enough specific information to determine why you are seeing a difference in performance, so the best anyone can do is provide general information. That said, I realized I had a link wrong and updated that. – quentin-starin May 09 '11 at 20:19
1

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

JTtheGeek
  • 1,707
  • 14
  • 17
  • Glad I could help. If you end up making it quicker, please post a patch or a GitHub pull request. -JeffV (rally25rs) – CodingWithSpike May 23 '11 at 11:39
  • Jeff, thanks again. To do some testing we auto generated a factory that can construct any one of the main types from a datareader. We are doing a check in the materialization to see if its a base type, if so, sent it to the factory and bypass all reflection. We are seeing performance exceed anything else we've seen, and memory utilization at a minimum as well. We were able to easily read 1M in just over a second. In our free time we are going to try to take this generation to the next level and use reflection.emit. I'll be happy to post up our changes soon. – JTtheGeek May 23 '11 at 18:53
1

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.

Community
  • 1
  • 1
Aristos
  • 66,005
  • 16
  • 114
  • 150
  • definitely a valid point regarding subsonic and the dynamic query compilation. In my particular use case though all my reads are very bulky, and then mostly i do small light insert/update/delete calls. I found by far the biggest performance enhancement for me was extending the advanced templates to generate a constructor for each class that takes a DataReader. Then in the deserialization buried in subsonic, if the object is simple I call that datareader constructor instead of using reflection to populate the object. This gave me easily a 20x read speed boost with almost no mem usage. – JTtheGeek Jun 28 '11 at 22:38