Result of caching a SELECT statement for reuse in multiple queries

Question

I have a rather complicated query to retrieve the Id field of the results that interest me, based on the parameters entered by the user.

After retrieving the corresponding identifiers, I use the result set of identifiers several times in separate queries to retrieve the actual sets of output records that I want (by joining other tables using aggregate functions, etc.).

I would like to avoid running the initial query separately for each set of results that I want to return. I believe that my situation is a general model, so I am interested in what is the best approach.

The database is in MS SQL Server and I am using .NET 3.5.

Answer 1

This will definitely help if the question contains some dimensions of a non-optimized solution (data sizes, timings). There are many methods that can be considered here, some of which are listed in other answers. I assume that the reason you don't want to repeat the same query is performance.

If all applications of the cached identifier set consist of joining the entire set into additional tables, the solution should certainly not include caching the identifier set outside the database. Data should not be moved back and forth if you can avoid this.

In some cases (when cursors or extremely complex SQL are not involved) it may be better (even if it is not intuitive) to not perform caching and simply join repetitive SQL to all desired queries. In the end, each query must be passed on the basis of one of the combined tables, and then the performance largely depends on the availability of indexes needed to join and quickly evaluate all the remaining information.

The most intuitive approach to "caching" a set of identifiers in a database is a temporary table (if it is named #something , it is private for connection and therefore can be used by parallel independent clients or it can be called ##something and be global). If there are many records in the table, indexes are needed. For optimal performance, the index should be a clustered index (only one per valid table) or created only after creating this set, where creating the index is a little faster.

Indexed views are preferred for temporary tables, unless the baseline data is read only during the entire process or when you can and want to ignore such updates to keep the entire set of reports consistent with the set. However, the ability of indexed views to always accurately predict baseline data occurs by slowing down these updates.

Another answer to this question refers to stored procedures. This is pretty much a way to organize your code. However, if you go this route, it is preferable to avoid using temporary tables, since such references to the temporary table prevent the compilation of the stored procedure; go to view or indexed views if you can.

Whatever approach you take, do not guess the performance characteristics and query optimizer behavior. Learn how to display query execution plans (in SQL Server Management Studio) and make sure that you see index calls, as opposed to nested loops that combine several large data sets; add only indexes that explicitly and dramatically change the performance of your queries. A well-chosen index can often change the performance of a query 1000 times, so it’s pretty hard to find out, but it’s important for success.

Last but not least, make sure you use UPDATE STATISTICS when re-building the database (and nightly during production), or your query optimizer will not be able to put the indexes you created into their best use.

Answer 2

If you plan to cache the result set in the application code, then ASP.NET has a cache, your Winform will have an object containing data with it, with which you can reuse the data.

If you plan to do the same in SQL Server, you can use indexed views to find identifiers. The view will be implemented and therefore you will be able to get results faster. You might even consider using a staging table to temporarily store the identifier.

Answer 3

With SQL Server 2008, you can pass table variables as SQL parameters. Just cache the identifiers, and then pass them as a table variable to queries that retrieve data. The only caveat to this approach is that you must predefine the table type as UDT.

http://msdn.microsoft.com/en-us/library/bb510489.aspx

Answer 4

For SQL Server, Microsoft generally recommends using stored procedures whenever possible.

Here are a few benefits:

http://blog.sqlauthority.com/2007/04/13/sql-server-stored-procedures-advantages-and-best-advantage/

 * Execution plan retention and reuse * Query auto-parameterization * Encapsulation of business rules and policies * Application modularization * Sharing of application logic between applications * Access to database objects that is both secure and uniform * Consistent, safe data modification * Network bandwidth conservation * Support for automatic execution at system start-up * Enhanced hardware and software capabilities * Improved security * Reduced development cost and increased reliability * Centralized security, administration, and maintenance for common routines 

It is also worth noting that, unlike other RDBMS providers (for example, Oracle), MSSQL automatically caches all execution plans:

http://msdn.microsoft.com/en-us/library/ms973918.aspx

However, for the last two versions of SQL Server, execution plans are cached for all T-SQL batches, regardless of whether they are in a stored procedure.

Answer 5

The best approach depends on how often the identifier changes, or how often you want to find it again.

One method is to simply save the result in the cache of ASP.NET objects using a Cache object (also available from HttpRuntime.Cache ). For example (from the page):

 this.Cache["key"] = "value"; 

There are many possible options for this topic.

Answer 6

You can use memcached to cache values ​​in memory. As I can see, there are some .net ports.

Answer 7

How often do the data you request change? For me, this sounds like the perfect scenario for data warehouses, where you set up data for faster data retrieval and create tables just like your DTO wants to see data. This method differs from the indexed view in that it simply represents a table that will have quick search operators and can especially be improved if you correctly configure the indexes in the columns that you plan to query

Answer 8

You can create a Global temporary Table . Create a table on the fly. Now insert records as per your request. Access this table in the following query on your connections ... for reuse