Post reply

Temp Table Strategy

  • January 12, 2021 at 1:53 pm

    #3831440

    I have a set of reports - with user defined criteria.  And an optional set of statistics which are produced on the report.  These are not db table stats, but things like aggregates, trends and statistical analysis of the data.  The happy user then downloads their data and moves on to something else.

    The reports can be expensive - so I save the main report data in a ##Temp table and pass this to other procedures in order to then generate the statistics.

    Later users may want to add another set of statistics. The App keeps track of what reports they already created (lets call it a virtual report) - so they don't have to re-enter all the criteria, and they can get back the original data, so the user thinks the report already exist: But of because the way the App to Database connection works, by this time the session has closed and the ##Temp table has gone.  The user has to wait again - and some of them get impatient and send the request several times - further exacerbating the issue.

    To cover this situation I am proposing the following.

    Create a report tracking table in tempdb (this is a permanent table, but obviously has to be recreated with db resets). Then every time a report is created add a reference to the virtual report to the report tracking table. The ##Temp tables then become proper tables in tempdb, and get referenced in the report tracking table against a specific virtual report.

    So when statistics are requested I can first check the report tracking table for existence of the data - and re-use the tempdb table if its there.  There would have to be a cleanup task to run periodically.

    So far I've come across 2 problems.

    1. How to automatically re-create a table after server restart empties out tempdb  (without putting the create code in every call)

    2. The App User does not have permission to create tables in tempdb - this is easily fixed but maybe its a warning that I'm doing something I shouldn't.

    Does anyone have any advice or comments about this: Good idea?, not a good idea?, hidden pitfalls?, Alternative strategies?

    I'm not looking for an enterprise level reporting solution - it has to be something fairly simple to develop.

    Thanks for any advice.

     

  • January 12, 2021 at 4:09 pm

    #3831510

    1. Use a SQL start up proc to create the table.  You could consider a regular table name in tempdb to make sure the table doesn't get dropped even if no connection is currently using/referencing it.
    2. I thought all users had permission to create tables in tempdb.  But maybe not.  Or maybe it's just global temps they can't create?  At any rate, I allow users to freely use tempdb (if they abuse it they may be restricted).  You can add the desired permissions to tempdb for specific users in the SQL start up proc as well.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • January 12, 2021 at 4:49 pm

    #3831524

    Is there a reason for you thinking that tempdb is the best place for your new table? Why not put it into an existing User database instead? Or create a new Reporting (or whatever) database and use that. It then becomes part of your usual development/source control process and you do not have any problem with recreating the table after a restart.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • January 12, 2021 at 5:17 pm

    #3831562

    Are you using SSRS for the reports - or something else?  If SSRS - then you have the option of using shared datasets and can control when and how often the shared dataset is updated/refreshed.

    If not - and you are going the route of 'permanent' tables in tempdb, wouldn't it be cleaner to just build permanent tables and cleanup processes in a user database?  Since you have to have cleanup processes in this approach then it shouldn't matter whether you are using a permanent table in tempdb or a permanent table in a user database (I would think).

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply