How to Improve - Multiple inserts with different Ids using multiple OledbConnections

Question

currently the system is creating multiples OledbConnections and the process is taking a long time to finish, it sums up to this:

C# code:

Select all person from table A.
FOR EACH person: Do a SELECT to see if ID 'x' from table A exists in table B.
FOR EACH person: INSERT with the ID from table A or a new ID (if that ID already exists in table B)

All these INSERTS are creating a new OledbConnection for each person, and for example 3k persons, it is taking too long.

It would be easier if I didn't have to deal with the IDs, but I'm not finding a good way to do this with VFP.

is that a way to Bulk INSERT this, or improve the performance?

Are you using foxpro or SQL-Server? – GarethD Jan 17 '20 at 09:54 — GarethD, Jan 17 '20 at 09:54
@GarethD, sorry, I am using FoxPro, I edited the question. – Luciano Gaube Jan 17 '20 at 10:11 — Luciano Gaube, Jan 17 '20 at 10:11

Ibra · Answer 1 · 2020-01-17T09:44:37.377

0

if id in table2 is autonumber:

if exists(select * from table2 where id = idtable1)

    insert into table2(field1,field2...

else

    insert into table2(ID,field1,field2...

edited Jan 17 '20 at 09:44

answered Jan 17 '20 at 09:39

Ibra

192
11

unfortunately, the table is a Foxpro table, so I does not have autoInc – Luciano Gaube Feb 05 '20 at 11:37

score 0 · Answer 2 · answered Feb 06 '20 at 20:33

I don't understand why you need to use multiple OleDbConnections for this. A single connection would do:

string sql = @"insert into tableB (id, f1, f2, f3)
select getNextId('tableB'), f1, f2, f3
from tableA
where exists (select * from tableA where tableA.id = tableB.id)";

using (OleDbConnection cn = new OleDbConnection(@"Provider=VFPOLEDB;Data Source=c:\MyDataFolder"))
using (OleDbCommand cmd = new OleDbCommand(sql, cn))
{
  cn.Open();
  cmd.ExecuteNonQuery();
  cmd.CommandText = @"insert into tableB (id, f1, f2, f3)
select id, f1, f2, f3
from tableA
where not exists (select * from tableA where tableA.id = tableB.id)";
  cmd.ExecuteNonQuery();
  cn.Close();
}

The SQLs used may not be right, because from your definition, it is not clear what you are really trying to do.

And BTW, you are not telling about the version, if Foxpro tables mean VFP tables then there is autoinc int field (but not necessary you can always have your own getNextId function as in the sample).

How to Improve - Multiple inserts with different Ids using multiple OledbConnections

2 Answers2