Anyone help me to know how to perform bulk insert with minimal time. In my scenario, I'm trying to insert 1 lakh+ rows in to SQLite DB from ADODatatable by using for loop.

Even with for loop i have added begin and end transaction like below. but it is taking more than an hour to insert

cmd = New SQLiteCommand("begin", myConn) cmd.ExecuteNonQuery() For i = 0 To myTable.Rows.Count - 1 Step 1 With myTable.Rows(i) dao.Time_Recent_Worktype(.Item("Mandt"), .Item("Zzwbselmts"), .Item("Zzefdate"), .Item("Zzntind"), .Item("Zzwtdatw"), .Item("Zzwtind")) End With Next i cmd = New SQLiteCommand("end", myConn)
cmd.ExecuteNonQuery()

I have used .Item as fetching data from datatable dynamically.

Please help me out on this

changed the cache_size from 2000 to 12000 using pragma cache_size=12000 command in DB browser, but it is not changing still it is 2000 only.

Can you tell what am I missing here?

The cache_size PRAGMA is not persistent and only affects the current connection. ie. if you set it in DB browser it will only apply for the DB browser and only while it holds a connection to the DB open. You have to set the pragma in the connection doing the actual work for it to be effective.

For i = 0 To myTable.Rows.Count - 1 Step 1

Note this is not an efficient way to think about SQL. Sqlite doesn't know in advance how many rows are in a table -- to compute myTable.Rows.Count you are effectively asking it to scan the whole table to tally how many rows are there and you haven't even done any useful work yet.

With myTable.Rows(i)

I'm not familiar with the language in use here, but there's a risk that every call to myTable.Rows(i) is a separate query returning the ith row from the result set, which is almost the least efficient way possible to loop over the table data.

I would recommend looking for an iterator approach which doesn't need to query the row count in advance and just keeps advancing through the table until it reaches the end.

I am utterly clueless about the programming language you are using, but I am going to try and explain the higher concept below as pseudocode in the hope that you can implement it successfully. It seems you are trying to migrate data from your ADODatatable to SQLite. (you might be better off asking on a ADO forum for help with its syntax)

note: 1 lakh = 1e5

// prepare your statement
sql = 'INSERT INTO table ('Mandt', 'Zzwbselmts', 'Zzefdate', 'Zzntind', 'Zzwtind') VALUES (?, ?, ?, ?, ?)';

// get all the records from your ADODatatable
// this will be faster but will require your computer to have enough memory
data = getAllDataFromADODatatable()

// start transaction
BEGIN;

// loop over the data and insert into SQLite table
for (row of data) {
    sql.run(row)
}

// commit the transaction
COMMIT;

as mentioned above, if you would rather iterate over your ADODatatable, you can do that as well, trading speed for memory.

iter = getDataFromADODatatableIteratively()

BEGIN

for (row of iter) {
    sql.run(row)
    next()
}

COMMIT

Good luck