python - Problemas de rendimiento de la actualización masiva de sqlalchemy

sqlite sql-update

Question

Necesito incrementar los valores en una columna periódicamente con los datos que recibo en un archivo. La tabla tiene> 400000 filas. Hasta ahora, todos mis intentos resultan en un rendimiento muy pobre. He escrito un experimento que refleja mis requerimientos:

#create table
engine = create_engine(''sqlite:///bulk_update.db'', echo=False)
metadata = MetaData()

sometable = Table(''sometable'',  metadata,
    Column(''id'', Integer, Sequence(''sometable_id_seq''), primary_key=True),
    Column(''column1'', Integer),
    Column(''column2'', Integer),
)

sometable.create(engine, checkfirst=True)

#initial population
conn = engine.connect()
nr_of_rows = 50000
insert_data = [ { ''column1'': i, ''column2'' : 0 } for i in range(1, nr_of_rows)]
result = conn.execute(sometable.insert(), insert_data)

#update
update_data = [ {''col1'' : i, ''_increment'': randint(1, 500)} for i in range(1, nr_of_rows)]

print "nr_of_rows", nr_of_rows
print "start time   : " + str(datetime.time(datetime.now()))

stmt = sometable.update()./
        where(sometable.c.column1 == bindparam(''col1''))./
        values({sometable.c.column2 : sometable.c.column2 +     bindparam(''_increment'')})

conn.execute(stmt, update_data)

print "end time : " + str(datetime.time(datetime.now()))

Los tiempos que obtengo son estos:

nr_of_rows 10000
start time  : 10:29:01.753938
end time    : 10:29:16.247651

nr_of_rows 50000
start time  : 10:30:35.236852
end time    : 10:36:39.070423

por lo tanto, hacer una cantidad de más de 400000 filas tomará demasiado tiempo.

Soy nuevo en sqlalchemy, pero hice mucha lectura de documentos, y simplemente no puedo entender lo que estoy haciendo mal.

¡gracias por adelantado!

Answer 1

Está utilizando el enfoque correcto haciendo una actualización masiva con una sola consulta.

La razón por la que tarda tanto tiempo es porque la tabla no tiene índice en sometable.column1 . Solo tiene un índice primario en la columna id .

Su consulta de actualización usa sometable.column1 en la cláusula where para identificar el registro. Por lo tanto, la base de datos debe escanear todos los registros de la tabla para cada actualización de una sola columna.

Para que la actualización se ejecute mucho más rápido, necesita actualizar el código de definición de esquema de tabla para agregar la creación de índice a la definición de columna1 con , index=True :

sometable = Table(''sometable'',  metadata,
    Column(''id'', Integer, Sequence(''sometable_id_seq''), primary_key=True),
    Column(''column1'', Integer, index=True),
    Column(''column2'', Integer),
)

Probé el código actualizado en mi máquina: el programa tardó <2 segundos en ejecutarse.

Por cierto, felicitaciones a la descripción de tu pregunta: pones todo el código necesario para reproducir tu problema.