Problem setting a table in edit mode

[Soulcito]

Hi! First of all, sorry for my english.

I have a function that edit some data from every record in a table.

I'm using a for to make it work. The problem is that in every loop it asks me to set the table in edit mode (

table1.edit

).
That makes the loop a lot slower and in the tables that I'm using, with 150 000+ records its a lot of time wasted. More than one second per record.

So I'm asking if there's a way to set the table in edit mode the first time and be able to use it like that.

Thx in advance!

[finalist]

If you know SQL = Structured Query Language
you may use a Query Component ( instead of Table Component ) ....

a Query would be something like that :

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

You may learn about the UPDATE sql statement at:
http://www.w3schools.com/Sql/sql_update.asp
or
http://www.sql-tutorial.net/SQL-UPDATE.asp

[Soulcito]

Thx for replying!

I don't know SQL actually, but i think that the command tou suggest doesn't work for me.

I'll put some of the code, maybe you will understand better what im trying to do.

begin
modifiedRecords := 0;
table1.First;
for counter := 0 to table1.RecordCount - 1 do
begin
  table1.Edit;
  editingField := Table1.FieldbyName('direccion').AsString;
  editingField := TrimRight(editingField);
  lastSpacePosition := LastDelimiter(' ', editingField);
  numeroCalle := copy (editingField, lastSpacePosition+1);
  if (numeroCalle[1] in ['0'..'9', 'N']) or (AnsiContainsText(numeroCalle, 'S/N')) then
    begin
    editingField := copy(editingField, 1, lastSpacePosition-1);
    Table1.FieldbyName('Calle').AsString := editingField;
    Table1.FieldbyName('Numero').AsString := numeroCalle;
    modifiedRecords :=  modifiedRecords+1;
    end;
  table1.Next;
end;
label7.Caption := inttostr(modifiedRecords);
end;

Thanks!

[finalist]

I understand the reason for the slow work of your programm:
1. Reading the current record from Database into Delphi environment;
2. Manipulating the string data by Delphi variables;
3. Writing back current record from Delphi environment into Database record;
4. Going to the next Record.
So it is impossible to be more fast, sorry !

Structured Query Language = SQL is created exactly for very-fast manipulating of Database records.
But there is a problem, that SQL is equal for different Databases only in common SQL statements .... every Database has a specific SQL statement for manipulating strings ....
So if your Database is ORACLE you will use SQL statements for Oracle ....
if your Database is MS SQL Server you will use SQL statements for SQL Server ....
if your Database is FIREBIRD you will use SQL statements for Firebird .... and so on ....
It is not easy to learn SQL - it may take more time that Delphi will manipulate all the data into your Database, so you have to decide wat to do:
1. Use Delphi manipulating for long time, because it is slow and can not be faster;
2. Take time for learning SQL for your Database and create fast working SQL Query, that will manipulate the data very-fast.
Good luck !

[Soulcito]

Hi! I've been doing some testing and I found out that the problem is only with the table1.edit command.

I did this test:

for counter := 0 to table1.RecordCount - 1 do
begin
table1.Edit;
table1.next;
end;

With this simple code, the delay times are almost the same that with the entire code.

Other thing that I found out was that the more records has the table, more time takes per loop. Something pretty reasonable if you think it.

I've been searching for a way to avoid doing that in every loop, but I had no luck.

Anyway, thank you very much for your advices! It's nice that someone else helps with other people problems.

:D

[finalist]

Hi! I've been doing some testing and I found out that the problem is only with the table1.edit command.

I did this test:

for counter := 0 to table1.RecordCount - 1 do
begin
table1.Edit;
table1.next;
end;

With this simple code, the delay times are almost the same that with the entire code.

Other thing that I found out was that the more records has the table, more time takes per loop. Something pretty reasonable if you think it.

I've been searching for a way to avoid doing that in every loop, but I had no luck.

Anyway, thank you very much for your advices! It's nice that someone else helps with other people problems.

:D

Sorry, but it is impossible to avoid :

Table1.First;
for Counter := 1 to Table1.RecordCount do
begin
  Table1.Edit;
  // Getting Database string field into Delphi string variable
  // Manipulating Delphi string variable
  // Taking Delphi string variable into Database string field
  Table1.Post;
  Table1.Next;
end;

This could not work faster, sorry :!:

SQL works very-fast because works inside Database ( avoids transfering Data both directions from Database to Delphi environment, and back from Delphi environment to Database ).

[cepukom]

Get Table1.Edit out of the loop!

Sorry, but it is impossible to avoid :

Table1.First;
Table1.Edit;  // put here
for Counter := 1 to Table1.RecordCount do
begin
  // Table1.Edit; -> do not put this here
  // Getting Database string field into Delphi string variable
  // Manipulating Delphi string variable
  // Taking Delphi string variable into Database string field
  // Table1.Post; -> get this out of the loop too
  Table1.Next;
end;
Table.Post; // put here

This could not work faster, sorry :!:

SQL works very-fast because works inside Database ( avoids transfering Data both directions from Database to Delphi environment, and back from Delphi environment to Database ).

[Soulcito]

Get Table1.Edit out of the loop!

If I do that, it only modifies the first time, on the second cicle, i get an exception telling me that the DataSet is not in edit or insert mode.

Thats EXACTLY the problem.

[cepukom]

If I do that, it only modifies the first time, on the second cicle, i get an exception telling me that the DataSet is not in edit or insert mode.

Thats EXACTLY the problem.

If this is became your new problem, then u must put table.edit and table.post pair back in the loop.
The real problem is lie on your calculation method then. Usually i avoid using variant cos of variant will slow down performance significantly.