使用JDBC在PostgreSQL上缓慢插入 [英] Slow insert on PostgreSQL using JDBC
问题描述
我在一个将数据从云系统下载到本地数据库(PostgreSQL，MySQL等)的系统上工作.现在我遇到了PostgreSQL性能问题，因为插入数据需要花费很多时间.
I work on a system which downloads data from a cloud system to a local database (PostgreSQL, MySQL, ...). Now I'm having an issue with PostgreSQL performance because it takes a lot of time to insert the data.
列数和数据大小可能会有所不同.在一个示例项目中，我有一张约有一张桌子. 170列.有一个唯一的索引-但是即使删除了索引，插入的速度也没有改变.
A number of columns and the size of the data may vary. In a sample project, I have a table with approx. 170 columns. There is one unique index - but even after dropping the index the speed of the insert did not change.
我正在使用JDBC驱动程序连接到数据库，并且要分250行插入数据(使用
I'm using JDBC driver to connect to the database and I'm inserting data in batches of 250 rows (using NamedParameterJdbcTemplate).
花了我大约. 18秒将数据插入Postgres . MySQL上的相同数据集只花了我一秒钟.那是一个巨大的差异-它是从哪里来的? Postgres JDBC驱动程序慢吗?可以通过某种方式对其进行配置以使其更快吗?我还想念其他东西吗? Postgres和MySQL之间的差异是如此之大.还有其他想法如何使其更快?
It took me approx. 18 seconds to insert the data on Postgres. The same data set on MySQL took me just a second. That's a huge difference - where does it come from? Is Postgres JDBC driver that slow? Can it be configured somehow to make it faster? Am I missing something else? The difference between Postgres and MySQL is so huge. Any other ideas how to make it faster?
我制作了一个示例项目，可在Github上使用- https://github.com/varad/postgresql-vs- mysql .一切都在运行"中的 LetsGo类中进行方法.
I made a sample project which is available on Github - https://github.com/varad/postgresql-vs-mysql. Everything happens in LetsGo class in the "run" method.
推荐答案
似乎这是Spring"bug"和驱动程序"bug"的组合.
It seems that this is a combination of a Spring "bug" and a driver "bug".
每次调用setValue()时，Spring都会尝试确定列的数据类型.通过调用PreparedStatementMetaData.getParameterMetaData()
Spring tries to determine the data type of a column each time setValue() is called. It does this by calling PreparedStatementMetaData.getParameterMetaData()
这显然会导致准备"语句发送到数据库，该语句本身速度非常快(在我的笔记本电脑上从来不超过1ms)，但是因为每一行的每个列都会调用它这将花费大量时间(每个非空值都会调用该值，导致大约23.000次调用)
This apparently causes a "prepare" statement to be sent to the database which in itself is quite fast (never more then 1ms on my laptop) but as it is called for each column for each row this sums up to a lot of time (it's called for every not-null value which results in approx. 23.000 calls)
在某种程度上，这是Spring的错误而不是驱动程序的错误，因为不缓存参数元数据实际上没有任何意义(至少在我看来). MySQL JDBC驱动程序不支持getParameterMetaData()，Spring知道这一点，因此MySQL不会显示此错误"，因为spring从未调用该方法.
To some extent this is more a Spring bug then a driver bug because not caching the parameter meta data doesn't really make sense (at least in my opinion). The MySQL JDBC driver does not support getParameterMetaData() and Spring knows this and so this "bug" doesn't show up with MySQL because spring never calls that method.
我不确定Postgres的JDBC驱动程序行为是否可以归类为错误，但是如果驱动程序在第一次调用后缓存该元数据，那肯定会很好.
I am not sure if Postgres' JDBC driver behavior can be classified as a bug, but it sure would be nice if the driver was caching that meta data after the first call.
可以说服Spring不能通过属性spring.jdbc.getParameterType.ignore
Spring can be convinced to not obtain the statement meta data through the property spring.jdbc.getParameterType.ignore
因此，输入:
System.setProperty("spring.jdbc.getParameterType.ignore", "true");
在该行之前:
LetsGo letsGo = new LetsGo();
此行为被禁用.
必须在之前设置属性，然后再初始化Spring.
The property must be set before Spring is initialized.
当我对您的示例项目执行此操作时，插入物将在笔记本电脑上运行500毫秒.
When I do that with your sample project, the insert runs in 500ms on my laptop.
修改
在看到有关使用Postgres-NG驱动程序的评论后，我深入研究了官方"驱动程序和NG驱动程序的来源，并且NG驱动程序在第一次调用后确实缓存了参数元数据，而官方驱动程序并不能解释为什么使用NG驱动程序的速度如此之快(无需在Spring中禁用通话)
After seeing the comment regarding the use of the Postgres-NG driver I dug into the sources of the "official" driver and the NG driver, and the NG driver does cache the parameter meta data after the first call whereas the official driver does not which explains why using the NG driver is so much faster (without disabling the call in Spring)
这篇关于使用JDBC在PostgreSQL上缓慢插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
