Board index » delphi » Removing&recreating indexes (MS-SQL, Delphi 5, ADO)

Removing&recreating indexes (MS-SQL, Delphi 5, ADO)

Before adding records in a table (~600'000 records a time) i have to destroy
all it indexes: primary and 3 secondary. When all records was inserted i
recreate this indexes. All it operations i make using Delphi-application
with ADO-interface. When i recreate indexes i have a trouble - Timeout
expaired exception. What the property i must turning?

Program text:

//Remove indexes
 ADOIndexQuery.SQL.Clear;
 ADOIndexQuery.SQL.Add('DROP INDEX CallList.IX_CallList');
 ADOIndexQuery.SQL.Add('DROP INDEX CallList.IX_CallList_1');
 ADOIndexQuery.SQL.Add('DROP INDEX CallList.IX_CallList_2');
 ADOIndexQuery.SQL.Add('ALTER TABLE CallList DROP CONSTRAINT PK_CallList');
 try
  ADOIndexQuery.ExecSQL;
 except on e:exception do
  begin
   MessageDlg('Unable remove indexes!', mtWarning, [mbOK], 0);
   Exit;
  end;
 end;

.... // adding records

// Recreate indexes
 ADOIndexQuery.SQL.Clear;
 ADOIndexQuery.SQL.Add('ALTER TABLE CallList ADD CONSTRAINT PK_CallList
PRIMARY KEY CLUSTERED (ID)');
 ADOIndexQuery.SQL.Add('CREATE INDEX IX_CallList ON CallList (NTEL)');
 ADOIndexQuery.SQL.Add('CREATE INDEX IX_CallList_1 ON CallList (NTELV)');
 ADOIndexQuery.SQL.Add('CREATE INDEX IX_CallList_2 ON CallList
(DATA_TIME)');
 try
  ADOIndexQuery.ExecSQL;
 except on e:exception do
  begin
   MessageDlg('Unable recreate indexes!', mtWarning, [mbOK], 0);
  end;
 end;
....

I use ADOIndexQuery: TADOQuery, using ADOServer Connection.
ADOServer.CommandTimeOut = 2592000, ADOServer.ConnectionTime = 2592000.

 

Re:Removing&recreating indexes (MS-SQL, Delphi 5, ADO)


Does your code really work ? I thought ADO queries only supported a single
SQL statement (so, you would have to split the code into 4 separate
queries).

"Al" <o...@rostovhotel.ru> schreef in bericht
news:91fn43$svt$1@sa.aaanet.ru...

Quote
> Before adding records in a table (~600'000 records a time) i have to
destroy
> all it indexes: primary and 3 secondary. When all records was inserted i
> recreate this indexes. All it operations i make using Delphi-application
> with ADO-interface. When i recreate indexes i have a trouble - Timeout
> expaired exception. What the property i must turning?

> Program text:

> //Remove indexes
>  ADOIndexQuery.SQL.Clear;
>  ADOIndexQuery.SQL.Add('DROP INDEX CallList.IX_CallList');
>  ADOIndexQuery.SQL.Add('DROP INDEX CallList.IX_CallList_1');
>  ADOIndexQuery.SQL.Add('DROP INDEX CallList.IX_CallList_2');
>  ADOIndexQuery.SQL.Add('ALTER TABLE CallList DROP CONSTRAINT
PK_CallList');
>  try
>   ADOIndexQuery.ExecSQL;
>  except on e:exception do
>   begin
>    MessageDlg('Unable remove indexes!', mtWarning, [mbOK], 0);
>    Exit;
>   end;
>  end;

> .... // adding records

> // Recreate indexes
>  ADOIndexQuery.SQL.Clear;
>  ADOIndexQuery.SQL.Add('ALTER TABLE CallList ADD CONSTRAINT PK_CallList
> PRIMARY KEY CLUSTERED (ID)');
>  ADOIndexQuery.SQL.Add('CREATE INDEX IX_CallList ON CallList (NTEL)');
>  ADOIndexQuery.SQL.Add('CREATE INDEX IX_CallList_1 ON CallList (NTELV)');
>  ADOIndexQuery.SQL.Add('CREATE INDEX IX_CallList_2 ON CallList
> (DATA_TIME)');
>  try
>   ADOIndexQuery.ExecSQL;
>  except on e:exception do
>   begin
>    MessageDlg('Unable recreate indexes!', mtWarning, [mbOK], 0);
>   end;
>  end;
> ....

> I use ADOIndexQuery: TADOQuery, using ADOServer Connection.
> ADOServer.CommandTimeOut = 2592000, ADOServer.ConnectionTime = 2592000.

Re:Removing&recreating indexes (MS-SQL, Delphi 5, ADO)


Have you checked the MS-SQL status to see if you have a locking problem or
something other than your code?

Jim

Quote
"Al" <o...@rostovhotel.ru> wrote in message

news:91fn43$svt$1@sa.aaanet.ru...
Quote
> Before adding records in a table (~600'000 records a time) i have to
destroy
> all it indexes: primary and 3 secondary. When all records was inserted i
> recreate this indexes. All it operations i make using Delphi-application
> with ADO-interface. When i recreate indexes i have a trouble - Timeout
> expaired exception. What the property i must turning?

> Program text:

> //Remove indexes
>  ADOIndexQuery.SQL.Clear;
>  ADOIndexQuery.SQL.Add('DROP INDEX CallList.IX_CallList');
>  ADOIndexQuery.SQL.Add('DROP INDEX CallList.IX_CallList_1');
>  ADOIndexQuery.SQL.Add('DROP INDEX CallList.IX_CallList_2');
>  ADOIndexQuery.SQL.Add('ALTER TABLE CallList DROP CONSTRAINT
PK_CallList');
>  try
>   ADOIndexQuery.ExecSQL;
>  except on e:exception do
>   begin
>    MessageDlg('Unable remove indexes!', mtWarning, [mbOK], 0);
>    Exit;
>   end;
>  end;

> .... // adding records

> // Recreate indexes
>  ADOIndexQuery.SQL.Clear;
>  ADOIndexQuery.SQL.Add('ALTER TABLE CallList ADD CONSTRAINT PK_CallList
> PRIMARY KEY CLUSTERED (ID)');
>  ADOIndexQuery.SQL.Add('CREATE INDEX IX_CallList ON CallList (NTEL)');
>  ADOIndexQuery.SQL.Add('CREATE INDEX IX_CallList_1 ON CallList (NTELV)');
>  ADOIndexQuery.SQL.Add('CREATE INDEX IX_CallList_2 ON CallList
> (DATA_TIME)');
>  try
>   ADOIndexQuery.ExecSQL;
>  except on e:exception do
>   begin
>    MessageDlg('Unable recreate indexes!', mtWarning, [mbOK], 0);
>   end;
>  end;
> ....

> I use ADOIndexQuery: TADOQuery, using ADOServer Connection.
> ADOServer.CommandTimeOut = 2592000, ADOServer.ConnectionTime = 2592000.

Other Threads