Caching joined tables in SQL Server

Question

My website has a search procedure that runs very slowly. One thing that slows it down is the 8 table join it has to do (It also has a WHERE clause on ~6 search parameters). I've tried to make the query faster using various methods such as adding indexes, but these have not helped.

One Idea I have is to cache the result of the 8 table join. I could create a temporary table of the join, and make the search procedure query this table. I could update the table every 10 minutes or so.

Using pseudo code, I would change my procedure to look like this:

IF CachedTable is NULL or CachedTable is older than 10 minutes
    DROP TABLE CachedTable
    CREATE TABLE CachedTable as (select * from .....)
ENDIF

Select * from CachedTable Where Name = @SearchName
                            AND EmailAddress = @SearchEmailAddress

Is this a working strategy? I don't really know what syntax I would need to pull this off, or what I would need to lock to stop things from breaking if two queries happen at the same time.

Also, it might take quite a long time to make a new CachedTable each time, so I thought of trying something like double buffering in computer graphics:

IF CachedTabled is NULL
    CREATE TABLE CachedTable as (select * from ...)
ELSE IF CachedTable is older than 10 minutes
    -- Somehow do this asynchronously, so that the next time a search comes
    -- through the new table is used?
    ASYNCHRONOUS (
        CREATE TABLE BufferedCachedTable as (select * from ...)
        DROP TABLE CachedTable
        RENAME TABLE BufferedCachedTable as CachedTable
    )

Select * from CachedTable Where Name = @SearchName
                            AND EmailAddress = @SearchEmailAddress

Does this make any sense? If so, how would I achieve it? If not, what should I do instead? I tried using indexed views, but this resulted in weird errors, so I want something like this that I can have more control over (Also, something I can potentially spin off onto a different server in the future.)

Also, what about indexes and so on for tables created like this?

This may be obvious from the question, but I don't know that much about SQL or the options I have available.

Answer 1

You can use multiple schemas (you should always specify schema!) and play switch-a-roo as I demonstrated in this question. Basically you need two additional schemas (one to hold a copy of the table temporarily, and one to hold the cached copy).

CREATE SCHEMA cache AUTHORIZATION dbo;
CREATE SCHEMA hold  AUTHORIZATION dbo;

Now, create a mimic of the table in the cache schema:

SELECT * INTO cache.CachedTable FROM dbo.CachedTable WHERE 1 = 0;
-- then create any indexes etc.

Now when it comes time to refresh the data:

-- step 1:
TRUNCATE TABLE cache.CachedTable;
-- (if you need to maintain FKs you may need to delete)
INSERT INTO cache.CachedTable SELECT ...

-- step 2:
-- this transaction will be almost instantaneous, 
-- since it is a metadata operation only: 

BEGIN TRANSACTION;
  ALTER SCHEMA hold  TRANSFER dbo.Cachedtable;
  ALTER SCHEMA dbo   TRANSFER cache.CachedTable;
  ALTER SCHEMA cache TRANSFER hold.CachedTable;
COMMIT TRANSACTION;