Dropping Columns takes long time?

[111111111]

Here is the code that I am using to drop columns from a table. It takes a very long time if there are a lot of records. But with Query Analyser the same query is fast. Can anyone tell what is the problem? Even if I did not create the recordset & used the client side cursor it gave me the problem.

For Each adofld In adoflds
strsql = ""
strsql = "ALTER TABLE [dbo].[" + adorecTablelist("DBTableName&quot + "] DROP COLUMN "
If Left(adofld.Name, 4) = "Old_" Or Left(adofld.Name, 5) = "Flag_" Then
strsql = strsql + Trim(adofld.Name)

Set adorecDropFields = New ADODB.Recordset
adorecDropFields.CursorLocation = adUseServer
adorecDropFields.CursorType = adOpenKeyset
adocnnTargetSetupDB.CommandTimeout = 0
adocnnTargetSetupDB.CursorLocation = adUseServer
Set adorecDropFields = adocnnTargetSetupDB.Execute(strsql)
End If

Next

 

[tlbroadbent]

Here are the problems that I see.[ol][li]You have an open record set. That record set has opened the table on the server. While the table is open you attempt to Alter the table schema. This will likely lead to blocking with slow performance or outright failure of the Alter Table statement.

[li]For every field name you want to delete, you are opening another recordset based on the Alter table statement. How many record sets will be opened? The code is not very efficient and is a likely contributor to the slowdown you experience.

[li]Moreover, you don't need to create a recordset when executing the Alter table statement. That SQL command doesn't return a result set. Remove the following lines from your code.

Set adorecDropFields = New ADODB.Recordset
adorecDropFields.CursorLocation = adUseServer
adorecDropFields.CursorType = adOpenKeyset
adocnnTargetSetupDB.CommandTimeout = 0
adocnnTargetSetupDB.CursorLocation = adUseServer
Set adorecDropFields = adocnnTargetSetupDB.Execute(strsql)

Use the following form of ADO.Execute method in place of the removed statements.

adocnnTargetSetupDB.Execute strsql

[li]You are dropping one column at a time. SQL Server allows multiple columns to be dropped in one Alter Table statement.

Alter Table tblname Drop Column col1name, col2name, col3name

I suggest that you loop through the ADO field collection, choosing the column names to drop and building the Alter Table statement.

[li]I also suggest that you close the ADO recordset before executing the SQL statement you build.[/ol] Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin