Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    12

    Unanswered: Storing Daily Cached query results in SQL Server

    I'm working on a reporting tool that could bring back hundreds of thousands of results back at once. I need some way to run the actual query only once a day, and then the reporting tool would just pull back this cached results.

    To be short, I need to figure out how to do this using a minimum amount of resources.

    Would a DataView work with something like this? How would I have it update only once a day?

    I appreciate any advice!

  2. #2
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    If you're pulling hundreds of thousands of results, it's likely that you won't be able to keep them all cached. Your best bet if you're pulling back that much information, you may want to create a staging table and then truncate it prior to reloading it every time. You could then create an index on that (those) tables to make your reporting software run a little faster. If your tempdb is big enough, you could also create a temp table (#tablename) and create indexes on it, that may even be faster.

    You could schedule the query as a job in SQL server, and give it a schedule, that should solve your problems.


    Cheers,
    -Kilka

  3. #3
    Join Date
    Oct 2003
    Posts
    12
    Quote Originally Posted by Kilka101
    If you're pulling hundreds of thousands of results, it's likely that you won't be able to keep them all cached. Your best bet if you're pulling back that much information, you may want to create a staging table and then truncate it prior to reloading it every time. You could then create an index on that (those) tables to make your reporting software run a little faster. If your tempdb is big enough, you could also create a temp table (#tablename) and create indexes on it, that may even be faster.

    You could schedule the query as a job in SQL server, and give it a schedule, that should solve your problems.

    Cheers,
    -Kilka
    Thanks, and after some research I think I'm going to use a stored procedure to create the temp tables that will hold the data, then index that. How do I keep the data in the temp table? I think by default the temp table only lasts as the stored procedure executes correct?

    And once I have the SP working I'll just figure out how to execute it on a schedule.

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •