CODEWITHSUNDEEP
pythonsqlmysqldjangoinsert
Roee Adler
Roee Adler

Reputation: 33990

What's the most efficient way to insert thousands of records into a table (MySQL, Python, Django)

I have a database table with a unique string field and a couple of integer fields. The string field is usually 10-100 characters long.

Once every minute or so I have the following scenario: I receive a list of 2-10 thousand tuples corresponding to the table's record structure, e.g. 

[("hello", 3, 4), ("cat", 5, 3), ...]

I need to insert all these tuples to the table (assume I verified neither of these strings appear in the database). For clarification, I'm using InnoDB, and I have an auto-incremental primary key for this table, the string is not the PK.

My code currently iterates through this list, for each tuple creates a Python module object with the appropriate values, and calls ".save()", something like so:

@transaction.commit_on_success
def save_data_elements(input_list):
    for (s, i1, i2) in input_list:
        entry = DataElement(string=s, number1=i1, number2=i2)
        entry.save()

This code is currently one of the performance bottlenecks in my system, so I'm looking for ways to optimize it.

For example, I could generate SQL codes each containing an INSERT command for 100 tuples ("hard-coded" into the SQL) and execute it, but I don't know if it will improve anything.

Do you have any suggestion to optimize such a process?

Thanks

Upvotes: 14

Views: 12719

Answers (8)

roopesh
roopesh

Reputation: 1686

I donot know the exact details, but u can use json style data representation and use it as fixtures or something. I saw something similar on Django Video Workshop by Douglas Napoleone. See the videos at http://www.linux-magazine.com/online/news/django_video_workshop. and http://www.linux-magazine.com/online/features/django_reloaded_workshop_part_1. Hope this one helps.

Hope you can work it out. I just started learning django, so I can just point you to resources.

Upvotes: 1

weevilgenius
weevilgenius

Reputation: 388

Regardless of the insert method, you will want to use the InnoDB engine for maximum read/write concurrency. MyISAM will lock the entire table for the duration of the insert whereas InnoDB (under most circumstances) will only lock the affected rows, allowing SELECT statements to proceed.

Upvotes: 2

NathanD
NathanD

Reputation: 8231

This is unrelated to the actual load of data into the DB, but...

If providing a "The data is loading... The load will be done shortly" type of message to the user is an option, then you can run the INSERTs or LOAD DATA asynchronously in a different thread.

Just something else to consider.

Upvotes: 1

staticsan
staticsan

Reputation: 30555

If you can do a hand-rolled INSERT statement, then that's the way I'd go. A single INSERT statement with multiple value clauses is much much faster than lots of individual INSERT statements.

Upvotes: 4

Sean McSomething
Sean McSomething

Reputation: 6507

If you don't LOAD DATA INFILE as some of the other suggestions mention, two things you can do to speed up your inserts are :

  1. Use prepared statements - this cuts out the overhead of parsing the SQL for every insert
  2. Do all of your inserts in a single transaction - this would require using a DB engine that supports transactions (like InnoDB)

Upvotes: 4

Nadia Alramli
Nadia Alramli

Reputation: 114943

You can write the rows to a file in the format "field1", "field2", .. and then use LOAD DATA to load them

data = '\n'.join(','.join('"%s"' % field for field in row) for row in data)
f= open('data.txt', 'w')
f.write(data)
f.close()

Then execute this:

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

Reference

Upvotes: 13

Chad Birch
Chad Birch

Reputation: 74548

For MySQL specifically, the fastest way to load data is using LOAD DATA INFILE, so if you could convert the data into the format that expects, it'll probably be the fastest way to get it into the table.

Upvotes: 12

KM.
KM.

Reputation: 103587

what format do you receive? if it is a file, you can do some sort of bulk load: http://www.classes.cs.uchicago.edu/archive/2005/fall/23500-1/mysql-load.html

Upvotes: 1

Related Questions