SQL Server Performance Forum – Threads Archive

Daily Drop and Create Table Performance Hit

Greetings Everybody! I currently have data that has to be pivoted in order to have the user report on it in the way they want. I’m doing this from VB.NET by creating a dataset and pivoting the data there, exporting to an XML file and using Reporting Services XML datasource. All works beautifully! Now for my "opportunity"… My users are starting to use Report Builder to run their own reports which is great, except I cannot build a Report Model based on an XML file. Therefore, I’ve written a stored procedure that will use SQL2005’s new PIVOT to perform my cross table functionallity. Problem is, to make the data available to build a report model, I need to have a static table defined, and the table definition can change based on user selection. Therefore, within the stored procedure, I’m dropping the table, and creating the table based on the latest layout from the user, which gives me a static table. My concerns are with the constant daily dropping and recreating of tables. Obviously, I’m gonna take a little performance hit, but I’m more concerned about any other negative consequences down the road. Any thoughts out there from someone much wiser than I?

Welcome to the SSP forums! Have you observed any sort of performance issues in this case? As you are dropping and recreating the table any associated plans will be flushed too, if that SP has complex calculations then you might need to re-think to collate the data as per the requirement. Using PERFMON will give you better idea on how system is coping up the pressure, make it during busy times and free times on the server for a better assessment.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Thank you! I haven’t observed any issues yet…I haven’t put this into production, only testing. The SP doesn’t really do a whole lot of calculating so that’s not a concern. I’ll check out PERFMON. I have looked at the execution plan and it isn’t too bad. Speed wise the SP runs pretty quick. I’m more concerned about the integrity of the database and the effects that dropping and re-creating will have on it, say two years from now. (ie: fragmentation, stability issues) Randy

If you are able to perform weekly optimization tasks on the database then no worries and you might need to consider about the database growth, say within next 6 months if the data is more as compared to your initial capacity plannng then you might need to address this now itself byusing archiving data method to reduce the impact on the live server. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

I do have a weekly optimization plan that runs for all databases on this instance. It runs everything available with the default options set. Size isn’t a concern. The entire database is archived at years end and shelved as another database for read-only purposes. Then the live database is initialized for the upcoming year. Sounds like I’m OK to promote this to my production server with a little more testing. Thanks for the feedback, Satya! Randy

Not a problem, glad there aren’t much blocks ahead for this database and capacity is not a concern at all. FYI article & KBA on stress testing on application that will help when PERFMON is scheduled:
http://www.sql-server-performance.com/gv_stress_test_lessons_3.asp
http://support.microsoft.com/kb/231619
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Just a thought, are there a lot of indexes on this table that will need to create each time? also if it is dropping and creating a static table, what if more than one person runs it? wouldnt this crash? I would use dynamic sql to create a table with a timestamp in the name and drop it at the end of the process. thoughts? Michael
MCDBA "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends — if they’re okay, then it’s you!"

Michael Thanks for your thoughts and concerns! There aren’t any indexes on these tables…no need as they are only for users to build reports via Report Builder. I’m using dynamic SQL to build the table as the columns can change based on the definition provided by the user (maintained in a table that defines the display of fields on the data entry screen) So within my SP that does the PIVOT, I’m looping through all available fields creating basically a column listing for the PIVOT statement. The tables require a static name for the Report Models I’ll need to build and maintain for the users. So a timestamp on each table would not work. ( although I wish it were that simple ) What I think I’m gonna do is schedule the SP to run once a month. After discussing this with some of the users, the fields won’t change daily as previously anticipated. They don’t even think they’ll change weekly, so I’ll schedule it once a month during off peak hours. Then, I’ll just need an option for the users to update the data within the table, which basically means running a variation of the SP that runs the PIVOT statement, but it wouldn’t need to drop and create the table, just delete what’s there and insert in the latest set of records. It sounds like a bunch, but really the server isn’t doing that much with this scenario. And the benefits of the user being able to create their own reports are amazing! Sounds like I’m trying to rationalize this now, doesn’t it? Whaddya think?

As a whole I believe this will go as expected without any issues as the data load is not a problem at all. You need to anticipate such issues if there is no alternative to archive the data or having the application on age-old hardware causing more issues. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

I think the timestamped name would work if you can capture that name and send it to another proc that runs the report. That proc name would never change… but the parm you pass to it may. Just a thought. Michael
MCDBA "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends — if they’re okay, then it’s you!"

Michael, I’m confused…Can you base a Report Model off of a stored procedure? That’s how I process all my reports that I design in Reporting Services, but to build Report Models for Report Builder, all I have available as a datasource are tables and views…no SP’s. Is there a way to make a Report Model use a Stored Procedure? Randy

sorry.. I was mistaken. Not that I know of … Michael
MCDBA "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends — if they’re okay, then it’s you!"

Report Model can only use data source and not in any other way, fyi with a design of Report Builder report, your local computer culture is used to format the sample data in the design area, except in instances where the DataCulture property has been set for an attribute within the data source. When you run a Report Builder report, the data source culture is used to format data for the entire report, except in instances where the DataCulture property has been set for an attribute within the data source. http://www.amazon.com/Professional-Server-2005-Reporting-Services/dp/0764584979 book is a good resource to get more out of it. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

]]>