insert on windows server 2003 very slow

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

insert on windows server 2003 very slow

Angela Kramer
Hi,

I've written a java programm which reads data from a file and inserts
them into a sqlite database. In order to speed up this process I use
PreparedStatement.

On a computer running Windows XP inserting one line into the database
takes about 16 milliseconds. On a machine with Windows Server 2003
inserting the same line takes 150 to 200 milliseconds.

Where might this big difference come from?

Thank you for your answers in advance!
Best regards
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: insert on windows server 2003 very slow

Eugene Wee
Hi Angela,

Did you wrap the inserts in a transaction?

Regards,
Eugene Wee

Angela Kramer wrote:

> Hi,
>
> I've written a java programm which reads data from a file and inserts
> them into a sqlite database. In order to speed up this process I use
> PreparedStatement.
>
> On a computer running Windows XP inserting one line into the database
> takes about 16 milliseconds. On a machine with Windows Server 2003
> inserting the same line takes 150 to 200 milliseconds.
>
> Where might this big difference come from?
>
> Thank you for your answers in advance!
> Best regards
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: insert on windows server 2003 very slow

Angela Kramer
Hi Eugene,

thank you for your reply.

Eugene Wee wrote:
> Hi Angela,
>
> Did you wrap the inserts in a transaction?

I do not start a transaction manually. However a transaction is probably
started automatically. But: "Automatically started transactions are
committed at the conclusion of the command."
(http://www.sqlite.org/lang_transaction.html)

My program executes only three queries:
1. create table contacts ...
2. "insert into contacts values (?, ?, ?, ?, ?, ?, ?, ?, ?);"
3. "create index idx_last_name on contacts (lastName ASC)"

The second query is run in a while loop (about 200.000 times).

> Regards,
> Eugene Wee
>
> Angela Kramer wrote:
>> Hi,
>>
>> I've written a java programm which reads data from a file and inserts
>> them into a sqlite database. In order to speed up this process I use
>> PreparedStatement.
>>
>> On a computer running Windows XP inserting one line into the database
>> takes about 16 milliseconds. On a machine with Windows Server 2003
>> inserting the same line takes 150 to 200 milliseconds.
I execute the same jar-file and use exactly the same data on both machines.
>>
>> Where might this big difference come from?
>>
>> Thank you for your answers in advance!
>> Best regards

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: insert on windows server 2003 very slow

Michael Ruck
Hi Angela,

for every iteration of your while loop a new transaction is opened and
commited. You need to wrap the loop in manual transactions using BEGIN and
COMMIT statements. Creating transaction logs is an expensive operation, when
compared to the insert itself.

Regards,
Michael

-----Urspr√ľngliche Nachricht-----
Von: [hidden email]
[mailto:[hidden email]] Im Auftrag von Angela Kramer
Gesendet: Dienstag, 26. Februar 2008 10:29
An: Eugene Wee
Cc: General Discussion of SQLite Database
Betreff: Re: [sqlite] insert on windows server 2003 very slow

Hi Eugene,

thank you for your reply.

Eugene Wee wrote:
> Hi Angela,
>
> Did you wrap the inserts in a transaction?

I do not start a transaction manually. However a transaction is probably
started automatically. But: "Automatically started transactions are
committed at the conclusion of the command."
(http://www.sqlite.org/lang_transaction.html)

My program executes only three queries:
1. create table contacts ...
2. "insert into contacts values (?, ?, ?, ?, ?, ?, ?, ?, ?);"
3. "create index idx_last_name on contacts (lastName ASC)"

The second query is run in a while loop (about 200.000 times).

> Regards,
> Eugene Wee
>
> Angela Kramer wrote:
>> Hi,
>>
>> I've written a java programm which reads data from a file and inserts
>> them into a sqlite database. In order to speed up this process I use
>> PreparedStatement.
>>
>> On a computer running Windows XP inserting one line into the database
>> takes about 16 milliseconds. On a machine with Windows Server 2003
>> inserting the same line takes 150 to 200 milliseconds.
I execute the same jar-file and use exactly the same data on both machines.
>>
>> Where might this big difference come from?
>>
>> Thank you for your answers in advance!
>> Best regards

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: insert on windows server 2003 very slow

Angela Kramer
Hi,

ok, my program used to run for hours, but when using begin and commit it
takes only a few seconds. :-)

Thank you so much for your help!

Best regards,
Angela


Michael Ruck wrote:

> Hi Angela,
>
> for every iteration of your while loop a new transaction is opened and
> commited. You need to wrap the loop in manual transactions using BEGIN and
> COMMIT statements. Creating transaction logs is an expensive operation, when
> compared to the insert itself.
>
> Regards,
> Michael
>
> -----Urspr√ľngliche Nachricht-----
> Von: [hidden email]
> [mailto:[hidden email]] Im Auftrag von Angela Kramer
> Gesendet: Dienstag, 26. Februar 2008 10:29
> An: Eugene Wee
> Cc: General Discussion of SQLite Database
> Betreff: Re: [sqlite] insert on windows server 2003 very slow
>
> Hi Eugene,
>
> thank you for your reply.
>
> Eugene Wee wrote:
>> Hi Angela,
>>
>> Did you wrap the inserts in a transaction?
>
> I do not start a transaction manually. However a transaction is probably
> started automatically. But: "Automatically started transactions are
> committed at the conclusion of the command."
> (http://www.sqlite.org/lang_transaction.html)
>
> My program executes only three queries:
> 1. create table contacts ...
> 2. "insert into contacts values (?, ?, ?, ?, ?, ?, ?, ?, ?);"
> 3. "create index idx_last_name on contacts (lastName ASC)"
>
> The second query is run in a while loop (about 200.000 times).
>
>> Regards,
>> Eugene Wee
>>
>> Angela Kramer wrote:
>>> Hi,
>>>
>>> I've written a java programm which reads data from a file and inserts
>>> them into a sqlite database. In order to speed up this process I use
>>> PreparedStatement.
>>>
>>> On a computer running Windows XP inserting one line into the database
>>> takes about 16 milliseconds. On a machine with Windows Server 2003
>>> inserting the same line takes 150 to 200 milliseconds.
> I execute the same jar-file and use exactly the same data on both machines.
>>> Where might this big difference come from?
>>>
>>> Thank you for your answers in advance!
>>> Best regards
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users