Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Performance When dropping multiple tables Expand / Collapse
Author
Message
Posted Thursday, October 3, 2013 10:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 3:14 AM
Points: 26, Visits: 208
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
Post #1501273
Posted Thursday, October 3, 2013 11:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 3:14 AM
Points: 26, Visits: 208
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
Post #1501301
Posted Thursday, October 3, 2013 1:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:45 PM
Points: 35,342, Visits: 31,875
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 :)



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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1501330
Posted Thursday, October 3, 2013 1:39 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:57 AM
Points: 989, Visits: 1,327
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 :)



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
Post #1501335
Posted Friday, October 4, 2013 1:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 6, 2014 5:46 AM
Points: 2,840, Visits: 3,969
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
Post #1501450
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse