优化python csv处理到父和EAV子表(Optimize python csv processing into parent and EAV child table)

 在线有几个类似的问题，用于使用python将大型csv文件处理成多个postgresql表。 但是，似乎没有人解决围绕优化数据库读/写和系统内存/处理的几个问题。 
 
 假设我有一行产品数据如下所示： 
 
 名称，SKU，日期时间，小数，小数，小数，小数，小数，小数 
 
 如果名称和sku存储在一个表（父）中，则每个十进制字段存储在子EAV表中，该表基本上包含decimal，parent_id和datetime。 
 
 假设我在csv文件中有20000个这样的行，所以我最终将它们分块。 现在，我采取了2000行的这些行并逐行循环。 每次迭代检查产品是否存在，如果不存在则创建它，检索parent_id。 然后，我有一个大的insert子语句列表，为子表生成十进制值。 如果用户选择仅覆盖未修改的十进制值，那么这也会检查每个单独的十进制值，以查看它是否在添加到插入列表之前已被修改。 
 
 在这个例子中，如果我遇到了最糟糕的情况，我最终会进行160,000次数据库读取以及10-20010次写入的任何地方。 我还将在每个块的内存列表中存储多达12000个插入语句（但是，这只是一个列表，因此该部分不是那么糟糕）。 
 
 我的主要问题是： 
 
 
 
 如何优化此更快，使用更少的数据库操作（因为这也会影响网络流量），并使用更少的处理和内存？ 如果它可以节省其他两个优化，我宁愿让处理速度变慢，因为那些在转换为AWS等服务器/数据库处理定价时会花费更多的钱。 
 
 
 一些子问题是： 
 
 
 
 有没有一种方法可以组合所有产品读/写并在执行小数之前将其替换为文件？ 
 
 
 我应该做一个较小的块大小来帮助记忆吗？ 
 
 
 我应该使用线程还是保持线性？ 
 
 
 我是否可以构建一个更有效的SQL查询，如果不存在并且引用内联，产品创建，从而将一些处理转移到sql而不是python？ 
 
 
 我可以优化子插入语句来做比数千个INSERT INTO语句更好的操作吗？ 
 

There's been several similar questions around online for processing large csv files into multiple postgresql tables with python. However, none seem to address a couple concerns around optimizing database reads/writes and system memory/processing.
 
Say I have a row of product data that looks like this:
 
name,sku,datetime,decimal,decimal,decimal,decimal,decimal,decimal
 
Where the name and sku are stored in one table (parent), then each decimal field is stored in a child EAV table that essentially contains the decimal, parent_id, and datetime.
 
Let's say I have 20000 of these rows in a csv file, so I end up chunking them up. Right now, I take chunks of 2000 of these rows and loop line by line. Each iteration checks to see if the product exists and creates it if not, retrieving the parent_id. Then, I have a large list of insert statements generated for the child table with the decimal values. If the user has selected to only overwrite non-modified decimal values, then this also checks each individual decimal value to see if it has been modified before adding to the insert list.
 
In this example, if I had the worst case scenario, I'd end up doing 160,000 database reads and anywhere from 10-20010 writes. I'd also be storing up to 12000 insert statements in a list in memory for each chunk (however, this would only be one list, so that part isn't as bad).
 
My main question is:
 
 
 
How can I optimize this to be faster, use less database operations (since this also affects network traffic), and use less processing and memory? I'd also rather have the processing speed to be slower if it could save on the other two optimizations, as those ones cost more money when translated to server/database processing pricing in something like AWS.
 
 
Some sub questions are:
 
 
 
Is there a way I can combine all the product read/writes and replace them in the file before doing the decimals?
 
 
Should I be doing a smaller chunk size to help with memory?
 
 
Should I be utilizing threads or keeping it linear?
 
 
Could I have it build a more efficient sql query that does the product create if not exists and referencing inline, thus moving some of the processing into sql rather than python?
 
 
Could I optimize the child insert statements to do something better than thousands of INSERT INTO statements?
 

原文：https://stackoverflow.com/questions/44829082