h2命名查询的数据库性能问题 [英] h2 Database performance issues with Named Query

问题描述

仅提供一些预信息.我们正在使用的H2文件数据库已经约为15 GB.

Just some pre-information. We are using a H2 File Database which is already around 15 GB.

我们的应用程序在

• Windows客户端
• Jetty Web服务器
• H2文件数据库

每次需要在客户端上更新数据时，用户都将获得一个带有XML文件的zip文件. XML文件将被导入到数据库中，或者该XML文件具有标志删除"，并且DB中的条目将被删除.每次导入zip文件都会有一个数据版本.导入是使用Java手动完成的. XML文件反序列化为POJO，并映射到我们的域实体.

Every time Data needs to be updated on client side, the user will get a zip File with XML-Files. Either an XML file will be imported to the DB or the xml file has a flag "delete" and the entry in the DB will be deleted. Every import of a zip file has a data version. The import is done manually with Java. XML Files are deserialized to POJOs and mapped to our Domain Entitys.

借助此方法，我们还能够将所有数据完全导入数据库(这只需要8小时的时间).

With this, we are also able to make a full import of all data to the Database (which just takes ages - 8h).

对于我们的问题:

发生问题的表大约有290.000行.

The table where our problem occurs has around 290.000 rows.

结构为:

我们有一个命名查询:

    @NamedNativeQuery(name="getRawTecdocWithMaxVersionAndGivenLocale", 
            query = "select tdo.tecdoc_guid as guid, tdo.tecdoc_locale as locale , tdo.tecdoc_version as version, tdo.data as data "
                    + " from TECDOC_OBJECTS tdo "
                    + " left outer join TECDOC_OBJECTS tdo1 "
                    + " on (tdo.tecdoc_guid = tdo1.tecdoc_guid and tdo.tecdoc_locale = tdo1.tecdoc_locale and tdo.tecdoc_version < tdo1.tecdoc_version) "
                    + " where tdo1.id is null " 
                        +  " and tdo.tecdoc_guid in ( ?1 ) "
                        +  " and tdo.tecdoc_locale = ?2 ",
            resultSetMapping = "rawTecdocs")

在数据更新(导入zip文件)后1秒钟左右会变得很慢.数据更新后，具有给定guid的实际查询没有更改.

Which gets quite slow around 1 sec after a data update (zip file import). The actual query, with given guid did not change after the data update.

我们在选中的列上有索引.

We have index on the columns which are selected.

奇怪的地方

如果我们用完整的更新(通过XML导入的所有15GB数据)填充数据库，则查询似乎又是快速"(20-50毫秒).

If we fill our Database with a full update (all 15GB of data imported through XML), the query seems to be "fast" (20-50 ms) again.

也许有人暗示我/我们克服了这个问题?

Maybe someone has a hint for me/us to overcome this issue?

推荐答案

我回答了这个问题，在此我明确地问过一个H2特定问题.

I answered this question, where I asked explicitly a H2 specific question.

我现在在末尾删除了一些组合索引，现在性能又更快了.

I now deleted at the end some combined indexes and now the performance is faster again.

与某些客户端上的ANALYZE一样，它解决了一些使其(或其他部分)情况恶化的问题.

As with ANALYZE on some clients it solved the problem on some it made it (or other parts) worse.

USE INDEX中有一个选项，但这仅在1.4.194之后可用，这也使其他一些查询非常慢，甚至由于内存不足而无法执行.

There is an option with USE INDEX, but this is only available after 1.4.194, which also made some other queries very slow or even impossible to execute due to not enough memory.

这篇关于h2命名查询的数据库性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！