Inserting bulk data in a sqlite table

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Inserting bulk data in a sqlite table

Anirban Sarkar
Hi all,

I have a flat file with pipe seperated data which needs to be inserted into
a sqlite table.
I have written a script for the same and it also works.
But my problem is that the flat file has 46000 records due to which the data
insertion process is becoming very slow.
Can anyone suggest me other ways of doing it.

Thanks in advance.

Regards,
Anirban Sarkar
Reply | Threaded
Open this post in threaded view
|

RE: Inserting bulk data in a sqlite table

Hans Bieshaar
I am assuming you are using separate INSERT statements in your script. You
can either try to use the COPY or .IMPORT commands (depending on the version
of SQLite you are using), or combine the INSERT statements into a single
transaction using BEGIN and COMMIT.

        Regards
        Hans

-----Original Message-----
From: Anirban Sarkar [mailto:[hidden email]]
Sent: Tuesday, May 24, 2005 12:09 PM
To: [hidden email]
Subject: [sqlite] Inserting bulk data in a sqlite table
Importance: High

Hi all,

I have a flat file with pipe seperated data which needs to be inserted into
a sqlite table.
I have written a script for the same and it also works.
But my problem is that the flat file has 46000 records due to which the data
insertion process is becoming very slow.
Can anyone suggest me other ways of doing it.

Thanks in advance.

Regards,
Anirban Sarkar

--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.322 / Virus Database: 266.11.15 - Release Date: 5/22/2005
 

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.322 / Virus Database: 266.11.15 - Release Date: 5/22/2005
 

Reply | Threaded
Open this post in threaded view
|

Re: Inserting bulk data in a sqlite table

lawrence.chitty
In reply to this post by Anirban Sarkar

>
> From: "Anirban Sarkar" <[hidden email]>
> Date: 2005/05/24 Tue AM 10:08:45 GMT
> To: <[hidden email]>
> Subject: [sqlite] Inserting bulk data in a sqlite table
>
> Hi all,
>
> I have a flat file with pipe seperated data which needs to be inserted into
> a sqlite table.
> I have written a script for the same and it also works.
> But my problem is that the flat file has 46000 records due to which the data
> insertion process is becoming very slow.
> Can anyone suggest me other ways of doing it.
>
> Thanks in advance.
>

Do you wrap your insertions in a single transaction ?

e.g (assuming you still use Tcl) :-

db1 eval begin
....insert your 46000 lines here
db1 eval commit

This delays the writing to the disk until you have done all your inserts.

Lawrence


> Regards,
> Anirban Sarkar
>
>

-----------------------------------------
Email sent from www.ntlworld.com
virus-checked using mcAfee(R) Software
visit www.ntlworld.com/security for more information
 

Reply | Threaded
Open this post in threaded view
|

Re: Inserting bulk data in a sqlite table

Jim Dodgen
In reply to this post by Anirban Sarkar
bulk loading should be wrapped with begin and commit statements.

begin;
insert into ...
(a bunch more)
commit;



At 03:08 AM 5/24/2005, Anirban Sarkar wrote:

>Hi all,
>
>I have a flat file with pipe seperated data which needs to be inserted into
>a sqlite table.
>I have written a script for the same and it also works.
>But my problem is that the flat file has 46000 records due to which the data
>insertion process is becoming very slow.
>Can anyone suggest me other ways of doing it.
>
>Thanks in advance.
>
>Regards,
>Anirban Sarkar