链接服务器查询“大” oracle views：性能问题 [英] Linked server querying "big" oracle views : performance issue

问题描述

假设我想从LinkedServer..Schema.View where statement中做一个select count（*）。

Let's say I want to do a select count(*) from LinkedServer..Schema.View where statement.

Schema.View是我的Oracle视图。

Schema.View is my Oracle view.

使用OLEDB提供程序，第一步是createsession，然后oledb在视图上执行openRowSet，其中max rows = 1（顶部1的种类）并且没有where过滤器。然后它执行获取列信息然后命令开始（快速因为有
a过滤器）。

Working with OLEDB provider, the first step is the createsession, then oledb does an openRowSet on the view with max rows = 1 (kind of top 1) and without the where filter. Then it does a Get Column Info and then the command begins (fast because there is a filter).

我不明白为什么这个GetColumnInfo在命令之前发生它是一个真正的问题，因为我的观点很大。因此，SQL Server在oracle上查询非常大的视图，然后只获得1行来获取元数据。这是性能下降......

I don't understand why this GetColumnInfo occurs before the command and it's a real problem because my view is very big. So SQL Server queries a very big view on oracle and then only get 1 row to get the metadata. It's performance killing ...

如果我使用OpenQuery，我在命令之前没有这些步骤。不幸的是在我的情况下我不能使用这个（不可预测的SQL）

If I use OpenQuery, I don't have these steps before the command. Unfortunately in my case I can not use this (unpredictable SQL)

问题：

- 为什么这种"肮脏"的查询以获取元数据？

- Why this kind of "dirty" query to get the metadata ?

- 如何避免？

我正在使用Oracle OLE DB 12.1 64位和SQL Server 2017的提供程序。

I'm using Oracle Provider for OLE DB 12.1 64 bits and SQL Server 2017.

推荐答案

Hi
Rom_ain ，

 

感谢您的发布。

 

看起来OpenQuery在远程服务器上完全运行查询。链接服务器调用似乎在远程服务器上运行每个语句并在本地聚合。

It looks like OpenQuery runs the query completely on the remote server. The linked server call seems to run each statement on the remote sever and aggregates it locally.

 

分布式查询：已链接服务器四部分查询也称为分布式查询。使用分布式查询， 
您可以在单个查询中引用不同数据源/服务器上的表。查询优化器通过查看查询命名法创建执行计划，并将其分解为远程和本地查询。本地查询在本地执行，远程
查询的数据从远程服务器收集，在本地清理，组合在一起并作为单个记录集呈现给最终用户。

Distributed Query : Linked server four part queries are also called distributed queries. Using distributed queries,  you can refer tables on different data sources/servers in a single query. Query optimizer creates an execution plan by looking at the query nomenclature and breaks it into remote and local queries. Local queries are executed locally and data for remote queries are collected from the remote servers, scrubbed locally, combined together and presented to end user as single record set.

 

OpenQuery：在指定的链接服务器上执行指定的传递查询。 SQL Server将传递查询作为未解释的查询字符串发送到OLE DB数据源。
也就是说，SQL不会对查询应用任何类型的逻辑，也不会尝试估计该查询会做什么，它只是将指定的查询原样传递给目标链接服务器。当您在一个查询中没有引用多个服务器
时，打开查询很有用。它通常很快，因为SQL不会  将其分解为多个操作，并且不对接收的输出执行任何本地操作。

OpenQuery : Executes the specified pass-through query on the specified linked server. SQL Server sends pass-through queries as un-interpreted query strings to an OLE DB data source . That is, SQL won’t apply any kind of logic on the query and won’t try to estimate what that query would do, it would simply pass the specified query as it is to the target linked server. Open queries are useful when you are not referencing multiple servers in one query. It’s generally fast as SQL does not  break it into multiple operations and does not perform any local action on the output received.

 

我认为
有点"脏"的查询 您提到的并不是很脏，只有
一种不同的方法 来处理。有一些关于链接服务器性能的文章，请参考它们。

  ;

Best Performer：Distributed
query （四部分）或在SQL Server中执行链接服务器查询时OPENQUERY

 

Top 3 PERFORMANCE KILLERS for LINKED SERVER QUERIES

 

希望它可以帮到你。

 

最好的问候，

Rachel

这篇关于链接服务器查询“大” oracle views：性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！