SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance When dropping multiple tables


Performance When dropping multiple tables

Author
Message
Steve Smith-163358
Steve Smith-163358
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 287
Hi Kurt,

Thank you very much for the input and ideas.

There are no hard and fast goals that I have to meet regarding the performance.
This process will still run out of hours and as such is not going to have any impact on live processing.
As such, the performance / efficiency improvement is solely to make me happy.

At the moment, my test case that I'm using takes around 3 minutes to drop all of the tables.
If I found a way to drop all 10,000 tables in under 2 minutes (for example) I would feel like i'd achieved something.


I suppose that the bigger issue here, rather than my specific situation was for me to understand if it was at all possible for me to efficiently drop multiple tables.
If I could, there is potential for me to use a similar process with other tasks.


Given that the overall priority of this process is functionality, rather than performance, I'm not planning to spend considerable time attempting to make it more efficient.





I have just realised that in a previous post I had reached the size limit of the parameter.
After reviewing it, I don't believe I have.... the parameter is easily big enough to handle my table names.
I don't know why I thought i'd be hitting a varchar(max) limit.



Thanks for the help
Steve
Steve Smith-163358
Steve Smith-163358
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 287
I don't know if I should be starting a new post for this or not.

When I've been testing the performance of my drop table statements, I've been restarting the SQL Instance before running.
My feeling here was that I didn't want any cached data to be affecting my calculations.


Interestingly, when dropping all 10,000 tables after an Instance restart, the whole process takes about 3 minutes
If I don't restart the instance, and just run the process immediately after creating the tables, they delete in about 15 seconds.



Is this discrepancy in time to be expected?
Is the process faster if the instance isn't restarted due to cached data?
Or is the process slow after the instance has been restarted due to SQL also running other startup processes?


Thanks again for your help
Steve
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87420 Visits: 41113
Steve Smith-163358 (10/3/2013)
Thank you all very much for your replies, and thank you Jeff for the excellent article that Alan linked to earlier.
I had often read about the merits of set based v's cursors before, but never really grasped the concepts.
More to the point, I didn't realise that half of the code I wrote was set based and half wasn't Smile



Alan, thank you for the code sample.
I've tried using it, but unfortunately I start hitting limits with the size of the data type.
In my test example that I'm using, I have 10,000 tables that I want to delete.

It appears, that due to the number of tables, I can't populate them all into the parameter, even using varchar(max)


With this many tables, I may be stuck with cursors or similar procedures


I don't believe that you're hitting the limits of the datatype (VARCHAR(MAX)). What you are hitting the limit of is how to display the code... normally 8K bytes (or less depending on your column width settings) even if the variable contains much more.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Kurt W. Zimmerman
Kurt W. Zimmerman
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1408 Visits: 1398
Jeff Moden (10/3/2013)
Steve Smith-163358 (10/3/2013)
Thank you all very much for your replies, and thank you Jeff for the excellent article that Alan linked to earlier.
I had often read about the merits of set based v's cursors before, but never really grasped the concepts.
More to the point, I didn't realise that half of the code I wrote was set based and half wasn't Smile



Alan, thank you for the code sample.
I've tried using it, but unfortunately I start hitting limits with the size of the data type.
In my test example that I'm using, I have 10,000 tables that I want to delete.

It appears, that due to the number of tables, I can't populate them all into the parameter, even using varchar(max)


With this many tables, I may be stuck with cursors or similar procedures


I don't believe that you're hitting the limits of the datatype (VARCHAR(MAX)). What you are hitting the limit of is how to display the code... normally 8K bytes (or less depending on your column width settings) even if the variable contains much more.


Good point Jeff....
Steve... can you post the original error that you were receiving when you thought you hit the limit of the varchar(max)? Thanks.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5330 Visits: 4076
Steve Smith-163358 (10/3/2013)
Is this discrepancy in time to be expected?
NOt neccessarily ..depends


Steve Smith-163358 (10/3/2013)
Is the process faster if the instance isn't restarted due to cached data?
Yes ..this is the main reason and probable answer of all these 3 questions

Steve Smith-163358 (10/3/2013)
Or is the process slow after the instance has been restarted due to SQL also running other startup processes?
No

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search