Search code examples
subsonicsubsonic3

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

Solution

  • 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