Perf issue with Linq2Sql

I'm trying to improve perf in an app that uses Linq2Sql heavily. I have found a particular method that is really slow. It is a na开发者_C百科sty, nested,.Sum() statement. When executed it is taking just over 30 seconds to return data. The total rows of data are maybe 3000. If I take the sql that LinqPad generates and run it, I get data back in less than a second.

I'm at a loss as to what the framework will return here. It should be filtered down by the where clause, right? I also imagine it is going to work from the inside out. starting with the where, then sum all the Scores, then sum each in T2, then sum each in T1.

ParentTable.Table1.Sum
(
 t1=>
  t1.Table2.Sum
  (
   t2=>
     t2.Table3.Where(t3=>t3.Table4.Id==275).Sum(t3=>t3.Score)
  )
)

To complicate things even further, LinqPad can execute the same statement in under half a second.

I guess my question is, why is there such a difference in Linq2Sql and TSql speeds? Is Linq bringing back all rows and filtering on the app box?

Now, the webapp reuses the same DataContext for the lifetime of the users session. I was always under the impression you should dispose of it after each operation. Could this be the issue?

Let me add that when I profile SQL (when executed from the app) I see nothing to worry about. Reads are <15, CPU is <5, Writes are nothing, and duration is at most 20. So I am pretty sure it isn't the execution of the statements, but some processing LINQ2Sql is doing.

---

I think I figured out what is going on.

The data relations are not as simple as LINQ makes it out to be. Table1 has a reference to Table2 Table2 does reference Table3 However Table4 isn't tied directly to T3, there are 2 others in the way.

That shouldn't affect anything, but shimmed in a partial class of the Table1 object is a custom global "caching" dictionary. Any time you request ANYTHING from Table1 it makes sure every record from that table is loaded into memory. The same pattern exists for all the objects.

So basically, by doing that quick and simple sum, it is loading EVERY record in all 6 tables (t1,t2,t3,t3b,t3c,t4) into memory and THEN doing the sum.

On subsequent requests, it would look at every item in the cache to see if it was stale. This also explains the other problem of data inconsistency.

Wow...just wow!