improve transfer from dataset to a table

[Bobito]

This is part of my program where I need to save in a table called ventas_n two columns: cve_art (product_code) and cve_alterna (alternative_code)
Later on I have to use this table in another query.
 
//MSSQLConnection1 is a TMSSQLConnection; connection to ms sql server
//SQLQuery1 is a TSQLQuery
//SQLQuery2 is a TSQLQuery

//SQLQuery1 has selected lots of records (about 5,000) from three (3) diffrent tables (facturas, remisiones y devoluciones -invoices, delivery notes and returns)


 ...

 // Delete any previous records in ventas_n,,.
 SQLQuery2.Close;
 SQLQuery2.DataBase := MSSQLConnection1;
 SQLQuery2.SQL.Text := 'delete from ventas_n;';
 SQLQuery2.ExecSQL;

 SQLQuery1.First;
 SQLQuery2.SQL.Text := 'insert into ventas_n (cve_art, cve_alterna) values (:value1,:value2)';
 While Not(SQLQuery1.EOF) do
  begin
   c_art   := SQLQuery1.FieldByName('cve_art').AsString;
   c_alter := SQLQuery1.FieldByName('alterna').AsString;
   SQLQuery2.Params.ParamByName('value1').AsString := c_art;
   SQLQuery2.Params.ParamByName('value2').AsString := c_alter;
   try
    SQLQuery2.Close;
    SQLQuery2.ExecSQL;
   finally
    // No additional action to be taken
   end;
   cant_reg := cant_reg + 1;
   SQLQuery1.Next;
  end;
 ...



You can imagine that going through the whole SQLQuery1 by: While Not(SQLQuery1.EOF) do begin ... end takes some time, and I would like to try another approach.

I have read that a) select ... into table_b from table_a is desirable comapred to b) insert into table_b (...) SELECT ... from table_a ...
I know in my case I do not have two tables, as a source I have a dataset. But I was wondering if there is another possible way to do it. Any suggestions? Thanks a lot.

[devEric69]

You tested if you could code everything at once,  with a single SQL statement such as...

Code: MySQL  [Select][+][-]

1. "INSERT INTO table_destination (`destination fields`)
2. SELECT
3. `source fields`  
4. FROM
5. table_source
6. WHERE
7. `filter to get the right values`"

...?

[Bobito]

Thanks devEric69, I have considered your hint. The thing is I don´t have two tables. The source comes from a dataset and the destinations certainly is a table.
Of course I wish I could do it by means of a single SQL statement, but I don't know how to do that if my source is not a table but a dataset. I´ll keep investigating ...

[mangakissa]

I don't see your transaction on SQLQuery2. But ik looks like this:

Code: Pascal  [Select][+][-]

1. SQLQuery1.First;
2.  SQLQuery2.SQL.Text := 'insert into ventas_n (cve_art, cve_alterna) values (:value1,:value2)';
3.  While Not SQLQuery1.EOF do
4.   begin
5.    SQLQuery2.Params.ParamByName('value1').AsString := SQLQuery1.FieldByName('cve_art').AsString;
6.    SQLQuery2.Params.ParamByName('value2').AsString := SQLQuery1.FieldByName('alterna').AsString;
7.    SQLQuery2.ExecSQL;
8.    <transaction>.applyupdates;
9.    SQLQuery2.commitretaining;
10.    cant_reg := cant_reg + 1;
11.    SQLQuery1.Next;
12.   end;
13.  

[guest48180]

I don't see your transaction on SQLQuery2. But ik looks like this:

Code: Pascal  [Select][+][-]

1. SQLQuery1.First;
2.  SQLQuery2.SQL.Text := 'insert into ventas_n (cve_art, cve_alterna) values (:value1,:value2)';
3.  While Not SQLQuery1.EOF do
4.   begin
5.    SQLQuery2.Params.ParamByName('value1').AsString := SQLQuery1.FieldByName('cve_art').AsString;
6.    SQLQuery2.Params.ParamByName('value2').AsString := SQLQuery1.FieldByName('alterna').AsString;
7.    SQLQuery2.ExecSQL;
8.    <transaction>.applyupdates;
9.    SQLQuery2.commitretaining;
10.    cant_reg := cant_reg + 1;
11.    SQLQuery1.Next;
12.   end;
13.  

Actually, that would be

Code: Pascal  [Select][+][-]

1. SQLQuery1.First;
2.  SQLQuery2.SQL.Text := 'insert into ventas_n (cve_art, cve_alterna) values (:value1,:value2)';
3.  While Not SQLQuery1.EOF do
4.   begin
5.    SQLQuery2.Params.ParamByName('value1').AsString := SQLQuery1.FieldByName('cve_art').AsString;
6.    SQLQuery2.Params.ParamByName('value2').AsString := SQLQuery1.FieldByName('alterna').AsString;
7.    SQLQuery2.ExecSQL;
8.    SQLQuery2.applyupdates;        // You had these
9.    Transaction1.commitretaining;   // two reversed, mangakissa  
10.    cant_reg := cant_reg + 1;
11.    SQLQuery1.Next;
12.   end;

[LemonParty]

Make a single query, like this:

Code: SQL  [Select][+][-]

1. INSERT INTO tb(fld1,fld2) VALUES
2. (1,2),
3. (3,5),
4. ...
5. (14,100)

[kpeters58]

In no particular order of importance:

1) dis- & re-enable controls on query/dataset/table component before cycling through (if required)

2) don't use FieldByName(<xxx>).AsXXXX - that requires a lookup into the field defs and a typecast as well; use <field def object>.Value if possible;
    this has repeatedly made dramatic speed differences for us in Delphi - assume it would do so here as well

3) do only one commit (and not one after every record)! Or, if it makes you feel better, do one for every 100 or 1.000 records