January 12, 2021 at 1:53 pm
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
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 12, 2021 at 4:49 pm
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 12, 2021 at 5:17 pm
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