CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Alex Gordon
Alex Gordon

Reputation: 60841

how do i cache a variable every 24 hours?

I have a query with horrible performance:

select COUNT(distinct accession_id) count,MONTH(received_date) month,YEAR(received_date) year
into #tmpCounts
from F_ACCESSION_DAILY
where CLIENT_ID not in (select clientid from SalesDWH..TestPractices)
group by MONTH(received_date),YEAR(received_date)

Instead of waiting for this query, I would like to create a variable or a view or anything else that I can store on the server and have the server automatically calculate it every 24 hours.

I would like to then be able to do a select * from #tmpCounts

How can I achieve this ?

Upvotes: 0

Views: 272

Answers (3)

Sylvia
Sylvia

Reputation: 2616

Instead of creating a cache table, consider creating an indexed view. There are limitations, but you may be able to improve your performance dramatically without much extra processing or code.

Here's some basic information to start with:

http://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/advantage-and-disadvantage-of-using-indexed-view-in-sql-server.aspx

Upvotes: 1

DRapp
DRapp

Reputation: 48169

Aside from your issue of performance and trying an alternative to prebuild each time once per day... How many records are you dealing with for this query to process.

In addition, I would alter the query since IN ( SUBSELECT ) are ALWAYS horrible. I would change to a LEFT-JOIN to the client table and test for NULL or not

select 
      YEAR(FAD.received_date) year,
      MONTH(FAD.received_date) month,
      COUNT(distinct FAD.accession_id) count
   from 
      F_ACCESSION_DAILY FAD
         LEFT JOIN SalesDWH..TestPractices TP
            on FAD.Client_ID = TP.ClientID
   where 
      TP.CLIENT_ID IS NULL
   group by 
      YEAR(FAD.received_date),
      MONTH(FAD.received_date)

I would also ensure having an index on your ACCESSION table on received_date, and your TestPractices table on its ClientID

Upvotes: 1

Rick Petersen
Rick Petersen

Reputation: 744

I don't know if this meets your need, but I would create a table for storing it, use SQL Server Agent to create a job that just truncates the table, runs the query you mention above and inserts the rows. This way from then on you could query the table for those results.

As an aside, likely the easiest way to truncate and load the table would be running a very simple SSIS package.

Upvotes: 1

Related Questions