Hi Jonhatan, That query inserts around 1 million of rows nowadays (in more or less 8 minutes on remote DB — while the profiling data in this thread are on localhost — ) 2/3 times a day. This is expected to increase of a factor around 10x in next months/year.
I'm personally not targeting any particular performance. For me 8 minutes looks
reasonable but possibly in the future with scaling that number could scale up
soon. I was asked to furtherly optimise that query and the solution that was
found was to build up the query textually. I really hate it for a multitude of
reasons, but well… I’m not the one making the decisions! :/
Unfortunately postgresql is not an option either. The stack has been based on
MySQL + PHP. Because with postgresql and the Array native datatype I could have
saved everything in an Array (currently the data are related to another table
with a one-to-many relationship through the passageID foreign key) dropping the
relationship, since it’s useless being these data just numerical parameters
that do not need to be queried but “just” ingested by some workers.
Maybe we could use JSON column type instead of a table in a one-to-many
relationship.
However thank you for your suggestion… The CLI version does not work for us
since the resulting data come from a fairly complex optimisation procedure.
_
-. .´ |∞∞∞∞
', ; |∞∞∞∞∞∞
˜˜ |∞∞∞∞∞∞∞∞∞ RdB
,., |∞∞∞∞∞∞
.' '. |∞∞∞∞
-' `’
https://rdb.is
On 2 novembre 2018 a 22:05:08, Jonathan Vanasco (jvana...@gmail.com) scritto:
can you elaborate on how much data is being loaded and what performance you're
targeting ?
if you're concerned with loading many MB of data as periodic batches, the best
performance by far is going to be generating a text file in one of the formats
your database server natively supports, and using a commandline client to load
it into the server.
i'm not familiar with how mysql handles foreign key checks, but in postgresql
deferring the constraint check OR dropping and rebuilding the constraint will
make things run even faster.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
signature.asc
Description: Message signed with OpenPGP using AMPGpg
