Dapper/EF - why is there a performance increase when variable is outside of using

c# dapper entity-framework scope using

Question

I ran both the below queries using the same params and stored proc. Example A takes over a minute, whereas example B takes under 20 seconds. If I call this same proc using EF, then I'm down to about 10 seconds (there is just over 50000 records returned). So it's also puzzling as to why EF is quicker.

Example A:

            List<resultObj> result;

            using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["string"].ConnectionString))
            {
                    result = conn.Query<resultObj>("spProc", param: new { /*params here*/ }, 
                    commandType: CommandType.StoredProcedure, commandTimeout: 300).ToList();
            }

Example B:

            using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["string"].ConnectionString))
            {
                var result = conn.Query<resultObj>("spProc", param: new { /*params here*/ }, 
                    commandType: CommandType.StoredProcedure, commandTimeout: 300).ToList();
            }

Why does moving the result variable out of the using's scope lead to a such a large increase in performance?

Accepted Answer

There is no reason for that to be the case. In this case, I suspect any difference was due to database server bottlenecks - perhaps you effectively primed the data into memory the first time (the slow one, where it had to hit disk) - or perhaps server contention or network throughput issues. What you describe in the two examples can only be caused by the underlying data source - not the difference between where the variable is declared.

As a minor note: AsList() would be preferable to ToList(), but this too: will only save you a tiny sliver of time (probably less than a millisecond).

When profiling, you should:

  • work in release mode without an IDE attached
  • perform JIT and prime all external sources first, usually by executing things at least once before you start timing
  • measure everything multiple times (in the case of fast operations, doing it thousands of times to obtain an average is common - not so applicable if it takes 30s per iteration, obviously)
  • force GC etc ahead of time so that any additional GC is solely the fault of the thing being tested
  • try to avoid competing acrivity on the test machine / server / network

Popular Answer

The two pieces of code you have posted will produce virtually identical IL code when compiled. The only difference will be which registers are used to represent which variable, and that won't have any impact on how the program runs.

The results you're seeing are a result of factors external to this code. There are a lot of factors that can affect performance, and it's impossible to say for sure what's causing the problem in your case. Perhaps the database re-thought its indexing plan since the first time you ran the code. Perhaps you changed other code that had an unexpected influence on your database connection. Maybe the remote system was under heavier load for a while there. But I feel confident saying that the difference is not from the code change you've provided here.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why