MySQLdb

[Daniel Bowett]

I have just started playing around with MySQLdb for a project I am planning.

As a test I have written a script that executes 3000 insert statements
on a table. The table contains 10 fields with a mix of text and numbers
- its a product table for a website eg UPC, ProductName, Price etc.

The problem I have is that it takes just over two minuted to execute the
3000 insert statements which seems really slow! I am running it on a
machine with a 1.5 Ghz Pentium M Processor and Gig Of Ram. I dont think
the machine is to blame for the speed because during execution the
processor sits at about 10% and there is loads of free RAM.

Does anyone know if this sort of speed sounds right?

Cheers,

Dan.

[nobody]

Re: MySQLdb

> The problem I have is that it takes just over two minuted to execute the[color=blue]
> 3000 insert statements which seems really slow![/color]

Are you creating a new DB connection for every insert?

I just did a test on my system (Athlon 2500+), 3000 rows with an
auto_increment field and a randomly generated 128 character field. 1.9
seconds.

[Dennis Lee Bieber]

Re: MySQLdb

On Tue, 25 Jan 2005 20:43:54 +0000, Daniel Bowett
<daniel@bowetts olutions.com> declaimed the following in
comp.lang.pytho n:
[color=blue]
> As a test I have written a script that executes 3000 insert statements
> on a table. The table contains 10 fields with a mix of text and numbers
> - its a product table for a website eg UPC, ProductName, Price etc.
>[/color]
How many indices?
[color=blue]
> The problem I have is that it takes just over two minuted to execute the
> 3000 insert statements which seems really slow! I am running it on a[/color]

I recall reading that, for some RDBMs, when doing such batch
inserts, they recommend turning off the indices at the start, do the
inserts, then reactivate the indices -- apparently it is faster to
rebuild an index after the data has been inserted, then to continually
update the index.

--[color=blue]
> =============== =============== =============== =============== == <
> wlfraed@ix.netc om.com | Wulfraed Dennis Lee Bieber KD6MOG <
> wulfraed@dm.net | Bestiaria Support Staff <
> =============== =============== =============== =============== == <
> Home Page: <http://www.dm.net/~wulfraed/> <
> Overflow Page: <http://wlfraed.home.ne tcom.com/> <[/color]

[nobody]

Re: MySQLdb

> How many indices?

Just the primary key (id).

[Daniel Bowett]

Re: MySQLdb

Dennis Lee Bieber wrote:[color=blue]
> On Tue, 25 Jan 2005 20:43:54 +0000, Daniel Bowett
> <daniel@bowetts olutions.com> declaimed the following in
> comp.lang.pytho n:
>
>[color=green]
>>As a test I have written a script that executes 3000 insert statements
>>on a table. The table contains 10 fields with a mix of text and numbers
>>- its a product table for a website eg UPC, ProductName, Price etc.
>>[/color]
>
> How many indices?
>
>[color=green]
>>The problem I have is that it takes just over two minuted to execute the
>>3000 insert statements which seems really slow! I am running it on a[/color]
>
>
> I recall reading that, for some RDBMs, when doing such batch
> inserts, they recommend turning off the indices at the start, do the
> inserts, then reactivate the indices -- apparently it is faster to
> rebuild an index after the data has been inserted, then to continually
> update the index.
>[/color]

UPC is my only index - its a varchar with 20 characters. I am only
opening the connection once, then doing 3000 execute statements in a for
loop.

I do have two "TEXT" fields in the table which contain the long and
short description. The average length of the long description is about
167 characters, the longest is 1800 characters. Is this whats making it
slow?

[Dennis Lee Bieber]

Re: MySQLdb

On Wed, 26 Jan 2005 08:27:34 +0000, Daniel Bowett
<daniel@bowetts olutions.com> declaimed the following in
comp.lang.pytho n:
[color=blue]
> I do have two "TEXT" fields in the table which contain the long and
> short description. The average length of the long description is about
> 167 characters, the longest is 1800 characters. Is this whats making it
> slow?[/color]

Varying length fields /might/ have an effect -- I don't have the
experience to know. Sorry.

--[color=blue]
> =============== =============== =============== =============== == <
> wlfraed@ix.netc om.com | Wulfraed Dennis Lee Bieber KD6MOG <
> wulfraed@dm.net | Bestiaria Support Staff <
> =============== =============== =============== =============== == <
> Home Page: <http://www.dm.net/~wulfraed/> <
> Overflow Page: <http://wlfraed.home.ne tcom.com/> <[/color]

[fedor]

Re: MySQLdb

Hi Daniel,

You should probably take a look at the executemany method of the cursor.
Your insert times might drop by a factor 20 . Here's an example.

Cheers,

Fedor

import time
import MySQLdb


db=MySQLdb.Conn ect(user="me",p asswd="my password",db="t est")
c=db.cursor()
n=0
tic=time.time()
for i in range(3000):
n+=c.execute('I NSERT INTO testtable VALUES (%s)', (i,))
toc=time.time()
t1=toc-tic
print 'separate sql statements: %s, inserted %s records' % (t1,n)



tic=time.time()
n=c.executemany ('INSERT INTO testtable VALUES (%s)', [(i,) for i in
range(3000)])
toc=time.time()
t2=toc-tic
print 'all at once %s inserted %s records' % (t2,n)

OUTPUT>>>
separate sql statements: 0.571248054504, inserted 3000 records
all at once 0.0253219604492 inserted 3000 records