Adaptation "calculate intermediate results in temp table" SQL template for LINQ?

Question

My team is building a C # web application that can generate custom reports that recombine data from the same basic set of SQL Server 2008 R2 tables in different ways. For example, a single dashboard page can combine a list of today's sales in each region, a list of the least-selling products and their trends over the past week, a list of top sales managers and 20 other indicators and charts. Under covers, a typical dashboard page will require at least 30 queries in 20+ different tables.

Unfortunately, it is impractical to “freeze” this data and pre-calculate it - we need to receive data in real time on the fly.

To make these pages fast, our trick was to identify different queries that retrieve the same underlying data. Then we compute the intermediate results from those base tables, cache these results in temporary tables, and then attach this temporary table to other tables to calculate the final results. Using this approach, we can reduce the I / O time and the time required for a particular panel by 10 times.

Our team would like to apply the same template to a similar page that uses LINQ-to-SQL to access data. We like LINQ for easy use in programming, for unit testing, etc. But performance stinks for the application described above, where we execute multiple queries, which may partially depend on the same underlying data.

Of course, I can call AsEnumerable() to materialize the intermediate results of the query, but if the intermediate results are large, then getting the results to and from SQL denies the performance gain and creates inefficient parameterized queries using hundreds of elements of length IN (@p1, ... ) .

In an ideal world, LINQ-to-SQL will offer the AsServerEnumerable() method, which will create a temporary table of intermediate results that I could reuse downstream without leaving the database.

Is there something similar?

If not, have you received any suggestions on how to make our server side “intermediate materialization” template work well on LINQ?

PS - I say "temporary table" and not "table variable" above, because temporary tables tend to work better with more expensive queries (parallel query plans, non-clustered indexes, etc.). But otherwise, all of the above applies to table variables.

Answer 1

No, this does not exist in raw LINQ and is not pre-prepared in any LINQ API I know of.

It can exist if you ignore the LINQ part in LINQ-to-SQL and just use the db.ExecuteQuery<T>(sql, args) approach, but if you do, you have to make sure that you pass explicit and open to data context (if you use the approach using the connection string, the connection control is processed automatically and you are not guaranteed to get the same connection between operations - it could be taken from the pool and such , even if it is the same basic connection , it will reset discarding any temporary tabs Itza).

Answer 2

Well, if you have a lot of readings, you can consider creating a VIEW instead of a temporary table and add a clustered index to this view. This will provide a view in the database.

Indexed views can be used by SQL Server in two different ways. First, a view can be called directly from a query, because regular views are currently used. But instead of launching the views underlying the SELECT statement and creating the results set on the fly, it uses a unique clustered index to immediately display the results of the view. Second, any query that runs on SQL Server 2000/2005 is automatically evaluated to see if there are existing indexed views that would satisfy the query. If so, the query optimizer uses an indexed query, even if it was not specified in the query, which greatly speeds up the query.

Additional Information: Sql Server Performance