How to speed up the execution of inserting values in postgresql

Question

data_input=open(ratingsfilepath,'r')
for row in data_input:
   cur_load.execute("INSERT INTO "+ratingstablename+" VALUES (%s, %s, %s)", (row.split('::')[0],row.split('::')[1],row.split('::')[2]))

I have 10 million records in .dat file I am loading them into table using python script. But it takes nearly 1 hour to load them. Is there anything to reduce the time

e4c5 · Accepted Answer · 2015-09-13 03:22:49Z

1

Inserting 10 million records will anyway take a very long time, but you can still speed it up by using your python script to convert your data file into CSV format that corresponds to your table structure. Then you can use the COPY FROM sql command to load it into the table in one go.

Using copy is considerably faster than 10 million inserts.

answered Sep 13, 2015 at 3:22

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to speed up the execution of inserting values in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related