data retrieval - Sporadically Slow Calls From .NET Application To SQL Server -
i have table in sql server inherited legacy system thats still in production structured according code below. created sp query table described in code below table create statement. issue that, sporadically, calls .net sp both through enterprise library 4 , through datareader object slow. sp called through loop structure in data layer specifies params go sp purpose of populating user objects. it's important mention slow call not take place on every pass loop structure. fine of day or more, , start presenting makes extremely hard debug. the table in question contains 5 million rows. calls slow, instance, take long 10 seconds, while calls fast take 0 10 milliseconds on average. checked locking/blocking transactions during slow calls, none found. created custom performance counters in data layer monitor call times. essentially, when performance bad, it's bad 1 call. when it's good, it's good. i've been able recreate issue on few different developer mach...