带有联接的精确在线查询运行超过15分钟 [英] Exact Online query with joins runs more than 15 minutes
问题描述
我正在使用以下查询:
set use-result-cache false
set use-http-cache false
create or replace table settings@inmemorystorage
as
select '29676ec4-61b5-45eb-a5a3-6feffe03d1d3' sor_id
, '[[Exploded]]' exploded_signal_text
, '{res:itgen_eol_sales_order}' entity_name_singular
, '{res:itgen_eol_sales_orders}' entity_name_plural
from me
select ...
from settings@inmemorystorage stg
left
outer
join ExactOnlineREST..salesorders sor
on sor.orderid = stg.sor_id
left
outer
join ExactOnlineREST..salesorderlines soe
on soe.orderid = stg.sor_id
left
outer
join BillOfMaterialItemDetails bom
on bom.billofmaterialitemdetails_billofmaterial_item_id_attr_guid = soe.item
left
outer
join ExactOnlineREST..items itm
on itm.ID = bom.item_id_attr_guid
left
outer
join ExactOnlineREST..itemsread itr
on itr.code = bom.item_code_attr
where sor.orderid is not null
and itm.class_10 in ('A', 'D', 'M', 'S', 'Z')
从Exact Online检索数据.在我的测试环境中，它将运行大约1秒钟，以将物料清单"爆炸应用于销售订单(在Exact Online的XML和REST API上读取大约5次).但是，它在客户站点上运行超过15分钟.它似乎与物料清单中使用的项目(物品)的检索有关；在我的测试环境中，大约有100个项目，而客户站点有250.000个项目.
to retrieve data from Exact Online. In my test environment it runs approximately 1 second to apply the Bill of Material explosion on a sales order (approximately 5 reads on the XML and REST API of Exact Online). However, on a customer site it runs more than 15 minutes. It seems to be related to the retrieval of the items (articles) used in the Bill of Material; in my test environment there are approximately 100 items, whereas the customer site has 250.000 items.
但是，此查询在交互式程序中使用，应在2.5秒内运行.
However, this query is used in an interactive program and should run within 2,5 seconds.
我尝试将itemsread和items结合起来以限制检索到的项目，但是它们具有两个表都需要的不同字段.
I've tried to combine itemsread and items to restrict the items retrieved, but they have different fields which are needed from both tables.
如何优化此查询以在处理大量数据时运行得更快?
How can I optimize this query to run faster with a large volume of data?
推荐答案
问题出在第二个查询中:有很多项目，Exact Online的API每秒可能有300个项目的吞吐量.因此，这一切都将永无改变.
The problem is within the second query: there are many items and the APIs of Exact Online have a throughput of maybe 300 items per seconds. So this is gone take forever without changes.
有两种替代路线:
- 优化查询
- 使用缓存
查询优化可确保较高的性能，并且在首次使用和后续使用时几乎不会占用资源.缓存的使用可以缩短第二次使用时的响应时间，但是比优化查询需要更多的资源.
The query optimization ensures great performance and little resource usage on first and subsequent use. The use of caching improves response time on second use, but requires more resources than an optimized query.
要优化查询，您将需要指示优化器如何更正确地处理联接，因为Exact Online默认没有可用的统计信息和参考数据.我将添加以下提示:
To optimize the query, you will need to instruct the optimizer how to handle the joins more correctly since there are no statistics and referential data available by default on Exact Online. I would add the following hints:
select /*+ join_set(soe, orderid, 100) join_set(itm, id, 100) join_set(itr, code, 100) */ ...
from settings@inmemorystorage stg
...
第一个提示join_set(soe, orderid, 100)指示优化器在最多返回100行时，将连接算法从哈希连接更改为按索引的循环，以与orderid(右侧)上的soe进行连接执行路径中的上一步.在这种情况下，settings将仅返回一行.在itm和itr上的连接也是如此.
The first hint join_set(soe, orderid, 100) instructs the optimizer to change the join algorithm from hash joins to a loop by index for the join with soe on orderid (right hand side) when there are at most 100 rows returned from the previous step in the execution path. In this case, there will be exactly one row returned from settings. The same holds for the join on itm and itr.
对于大型的精确在线"环境，这将确保在销售订单上少于60行时，您始终可以进行5次查找.通常需要1秒钟.
For a large Exact Online environment, this will ensure that you have always 5 lookups when there are less than 60 lines on a sales order. That typically takes 1 second.
将PostgreSQL，SQL Server，Oracle或MySQL数据库配置为Invantive Data Cache支持数据库提供程序时，可以将部分查询的结果存储在普通数据库中.当数据缓存仍然足够新鲜"时，优化器会使用ANSI SQL自动查询该普通数据库.
When you configure a PostgreSQL, SQL Server, Oracle or MySQL database as a Invantive Data Cache backing database provider, you can have the outcome of parts of the queries memorized in a normal database. The optimizer automatically queries this normal database using ANSI SQL when the data cache is still sufficiently "fresh".
例如:
select /*+ ods(true, interval '7 days') */ ...
告诉优化器在不超过7天前将数据放置在数据高速缓存中时，将数据高速缓存用于所有精确在线"数据.超过7天时，它将创建一个新版本，并将其存储在数据缓存中并使用.
tells the optimizer to use the data cache for all Exact Online data when the data was placed in the data cache not more than 7 days ago. When it is older than 7 days, it creates a new version, stores it in the data cache and uses it.
当您需要在Exact Online中包含近乎实时的更改时，您将必须配置数据复制.然后，它通过Web挂钩检索所有插入/更新/删除事件，并将它们应用到您的数据缓存中.但是缓存可能仍旧存在30分钟，因为传播可能要花一些时间.
When you need near real-time changes in Exact Online to be included, you will have to configure data replication. It then retrieves through web hooks all insert/update/delete events and applies them on your data cache. But the cache can still be maybe 30 minutes old, since the propagation can take some time.
与没有缓存和没有优化的性能相比，性能要好得多.通常，使用250.000项的吞吐量将提高1000倍，因此与使用250项的吞吐量相当.假设Exact Online的典型页面大小为60，则感觉为5 + 5 + 3 = 13个I/O，因此大约需要2.6秒，接近2.5秒的边界.
The performance is a lot better than without cache and without optimization. In general, the throughput with 250.000 items will be 1.000 times better, so comparable with using 250 items. Given the typical page size of 60 of Exact Online, it will feel as 5 + 5 + 3 = 13 I/Os, so approximately 2,6 seconds which is near the boundaries given of 2,5 seconds.
请注意，由于当前没有可用的索引，因此您正在使用的材料明细表永远不会使用按索引查找.因此，如果所有产品的物料清单都很大，则必须缓存数据以实现合理的性能.
Please note that the bill of materials table you are using will NEVER use a lookup by index, since there is no index available at this moment. So if you have a large bill of materials across all products, you must data cache for a reasonable performance.
这篇关于带有联接的精确在线查询运行超过15分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
