1

I'm working on a project that uses EntitySpaces as ORM.

Below you see a simplified method of ItemCollection that loads a collection by calling a stored procedure:

public partial class ItemCollection : esItemCollection
{
    public bool LoadItemsUsingSomeStoredProcedure(string aLotOfAttributes, out int totalCount)
    {
        // Set a lot of parameters
        esParameters parameters = new esParameters();
        // ...

        bool result = Load(esQueryType.StoredProcedure, "ItemsStoredProcedure", parameters);

        totalCount = (int) paramTotalCount.Value;

        return result;
    }
}

Using SQL Server Profiler I see that is results in this call to the database:

declare @p5 int
set @p5=485
exec [ItemsStoredProcedure] @Param1=4,@Param2=N'41',@Param3=N'SomeValue',@Param4=0,@TotalCount=@p5 output,@Param5=1,@Param6=25
select @p5

It takes about 200 seconds to complete.

BUT: When I run the same SQL snippet in SQL Management Studio (locally and remotely) it takes about 4~5 seconds to complete.

Any ideas why it tooks about 40x longer for the EntitySpaces call to complete than the one in the SQL client? Any ideas how this can be debugged/improved?

PS: Replacing EntitySpaces is high on my WANT-list, but as always it is really hard to convince a client to spend a month of development on refactoring a "working" program... So that option is out, atm.

Niels R.
  • 7,260
  • 5
  • 32
  • 44
  • The 200 seconds; is that timing the SQL part via ES, or the full stack including materialization etc? If it is the SQL part, then I would suspect different `SET` options are the culprit (this can break, for example, computed+persisted+indexed columns, forcing a table-scan and re-evaluate). However, it *could* also simply be that ES materialization is slow. Or that you have a bloated context (i.e. loading lots and lots into a single context, rather than keeping contexts separate and slim) – Marc Gravell Jul 10 '13 at 09:47
  • The >200 seconds is indeed the timing of the SQL part (as seen in the SQL Server Profiler). Can you elaborate in the different `SET` options? – Niels R. Jul 10 '13 at 10:02
  • Another reason could be that the SQL Management Studio only fetches the first X items. – Daniel Hilgarth Jul 10 '13 at 10:03
  • @DanielHilgarth The stored procedure only returns 25 rows (paged). So I assume both ES en SMS are returning the same amount of rows. – Niels R. Jul 10 '13 at 10:18
  • @NielsR. you could try running something like [this](http://blog.techdreams.org/2008/01/display-current-session-set-options-sql.html) - run it once in SSMS and once in your usual app code - and compare the results. Also - did you look to see if there were any blocks when this happened? via `sp_who` / `sp_who2` etc. – Marc Gravell Jul 10 '13 at 19:18

1 Answers1

0

Don't run it from Visual Studio, that is a known issue with all application, EntitySpaces barely adds any time to SqlClient at all and materializes that fastest of any ORM.

http://www.entityspaces.net/www.entityspaces.net/blog/2010/08/26/The%20EntitySpaces%20ORMBattleNET%20Performance%20Numbers.aspx.html

Mike Griffin
  • 73
  • 4
  • I am not running it from Visual Studio. I did the timing using SQL Server Profiler on the production database. Anyways, it is no longer relevant as we will be moving away from EntitySpaces in favor of EntityFramework. – Niels R. Oct 21 '13 at 09:11
  • That is fine, but just for the record the performance numbers http://www.entityspaces.net/www.entityspaces.net/blog/2010/08/26/The%20EntitySpaces%20ORMBattleNET%20Performance%20Numbers.aspx.html If you like the EntitySpaces dynamic query over the EF/LINQ stuff *(trust me, EF is terribly slow and limited, you must always bring back all columns on a select) you can use this, it's available on NuGet. See http://brewdawg.github.io/Tiraggo.EF/ It installs and generated your Query classes in seconds, and it loads your EF model classes – Mike Griffin Nov 04 '13 at 12:11
  • These numbers seems to be dated more than 3 years ago, so I don't trust them blindly. Also EntitySpaces seems to be dying. The website is incomplete, the forums are not searchable, decent documentation is lacking (try Googling ES problems and you will find almost no relevant results), ... Since EF is a Microsoft product and is it well supported by a lot of third-party frameworks/tools/etc I think it is a safe bet. If it is a little slower, we will upgrade the hardware. Hardware is still a lot cheaper than development (debugging) time. – Niels R. Nov 05 '13 at 07:43