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?
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: