Hi,

I need to quickly insert 10k records into my table.

I write 2 functions: for inserting records via Table->Append/Post and via SQL INSERT
statement.

Results very confuse me:

- Inserting 10k records via Table->Append/Post take only 1.2 secods

- Inserting 10k records via Query->ExecSQL take 30 !!! seconds !

Why ?

my first function:

------------------------------------------
void __fastcall Form1::AddAppend()
{
int st,et;
TStringList *Data=new TStringList;
Data->LoadFromFile("c:\\emails.txt");
st=GetTickCount();
for(int i=0;i<Data->Count;i++)
   {
   Table->Append();
   Table->FieldByName("Email")->AsString=Data->Strings[i];
   Table->FieldByName("FullName")->AsString="test";
   Table->Post();
   }
Table->FlushBuffers();
et=GetTickCount();
ShowMessage(et-st); // always 1 - 1.2 seconds
}
-----------------------------------------

my second function:

void __fastcall Form1::AddInsert()
{
int st,et;
TStringList *Data=new TStringList;
TStringList *UpdQuery=new TStringList;
Data->LoadFromFile("c:\\emails.txt");
for(int i=0;i<Data->Count;i++)
   UpdQuery->Add("INSERT INTO \"List0\" (Email,FullName) VALUES ('"+Data->Strings[i]+"',
'test');");
Query->SQL->Assign(UpdQuery);
Query->Prepare();
st=GetTickCount();
Query->ExecSQL();
et=GetTickCount();
ShowMessage(et-st); // 29 - 32 seconds !!!
}
-----------------------------------------

I also try to add Database->StartTransaction() and Database->Commit() before and after
Query->ExecSQL and receive same results

I also try to load only 1000 records (to avoid possible commit problems) - same results -
Table Append up to 15 times faster than SQL INSERT

Session->ForceBufferFlush set to false, DBISAM Version 4.26.1, C++Builder 2007, WinXP SP2,
2 GB Ram, Athlon X2 4600.

How to speed up INSERT statement ?

You are opening the table, parsing the SQL, etc. for each iteration. Use
parameters on the SQL, and do and explicit prepare before you start the
loop, and I bet the timings will be similar.

Robert

"Max Terentiev" <support@bspdev.com> wrote in message
news:0858D3D6-9961-49CE-A90D-897FC3390AFA@news.elevatesoft.com...
> Hi,
>
> I need to quickly insert 10k records into my table.
>
> I write 2 functions: for inserting records via Table->Append/Post and via
> SQL INSERT
> statement.
>
> Results very confuse me:
>
> - Inserting 10k records via Table->Append/Post take only 1.2 secods
>
> - Inserting 10k records via Query->ExecSQL take 30 !!! seconds !
>
> Why ?
>
> my first function:
>
> ------------------------------------------
> void __fastcall Form1::AddAppend()
> {
> int st,et;
> TStringList *Data=new TStringList;
> Data->LoadFromFile("c:\\emails.txt");
> st=GetTickCount();
> for(int i=0;i<Data->Count;i++)
>    {
>    Table->Append();
>    Table->FieldByName("Email")->AsString=Data->Strings[i];
>    Table->FieldByName("FullName")->AsString="test";
>    Table->Post();
>    }
> Table->FlushBuffers();
> et=GetTickCount();
> ShowMessage(et-st); // always 1 - 1.2 seconds
> }
> -----------------------------------------
>
> my second function:
>
> void __fastcall Form1::AddInsert()
> {
> int st,et;
> TStringList *Data=new TStringList;
> TStringList *UpdQuery=new TStringList;
> Data->LoadFromFile("c:\\emails.txt");
> for(int i=0;i<Data->Count;i++)
>    UpdQuery->Add("INSERT INTO \"List0\" (Email,FullName) VALUES
> ('"+Data->Strings[i]+"',
> 'test');");
> Query->SQL->Assign(UpdQuery);
> Query->Prepare();
> st=GetTickCount();
> Query->ExecSQL();
> et=GetTickCount();
> ShowMessage(et-st); // 29 - 32 seconds !!!
> }
> -----------------------------------------
>
> I also try to add Database->StartTransaction() and Database->Commit()
> before and after
> Query->ExecSQL and receive same results
>
> I also try to load only 1000 records (to avoid possible commit problems) -
> same results -
> Table Append up to 15 times faster than SQL INSERT
>
> Session->ForceBufferFlush set to false, DBISAM Version 4.26.1, C++Builder
> 2007, WinXP SP2,
> 2 GB Ram, Athlon X2 4600.
>
> How to speed up INSERT statement ?
>

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote:

>You are opening the table, parsing the SQL, etc. for each iteration. Use
>parameters on the SQL, and do and explicit prepare before you start the
>loop, and I bet the timings will be similar.

No, I build large SQL scripts (with all INSERTs) then execute it in single ExecSQL call:

// build SQL script
for(int i=0;i<Data->Count;i++)
  UpdQuery->Add("INSERT INTO \"List0\" (Email,FullName) VALUES ('"+Data->Strings[i]+"',
'test');");
Query->SQL->Assign(UpdQuery);
// now execute it
Query->ExecSQL();

so, I run about 10 000 inserts like this:

INSERT INTO "List0" (Email,FullName) VALUES ('abareal@spb.cityline.ru', 'test');
INSERT INTO "List0" (Email,FullName) VALUES ('abarenovda@tnk-nv.ru', 'test');
INSERT INTO "List0" (Email,FullName) VALUES ('abarhudarov@mail.ru', 'test');
INSERT INTO "List0" (Email,FullName) VALUES ('abarigen@yandex.ru', 'test');

in single ExecSQL call !

"Max Terentiev" <support@bspdev.com> wrote in message
news:FBB45703-D7AE-4E09-9116-BA909BE2FC0D@news.elevatesoft.com...
> "Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote:
>
>>You are opening the table, parsing the SQL, etc. for each iteration. Use
>>parameters on the SQL, and do and explicit prepare before you start the
>>loop, and I bet the timings will be similar.
>
> No, I build large SQL scripts (with all INSERTs) then execute it in single
> ExecSQL call:
>

Same thing. Each step is a separate SQL operation.

Robert

Max,

<< No, I build large SQL scripts (with all INSERTs) then execute it in
single ExecSQL call: >>

Robert is correct - the SQL compiler still has to parse and compile (what is
essentially the same statement) 10,000 times.  Use a parameterized statement
instead, prepare it once, and then populate the parameters and call ExecSQL
for each execution.  The timings will then be similar to the Append..Post
method.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thank you very much !

With parametrized query 10k records inserted in 0.6 seconds !!!