主页 / dba / 问题 / 335338
Accepted
Brent Ozar
Asked: 2024-01-31 02:49:21 +0800 CST

SQL Server 链接服务器查询 Postgres 不过滤行

  • 772

当我在具有 PostgreSQL 链接服务器连接的 SQL Server 2022 服务器上运行此查询时：

SELECT TOP 1 * 
FROM PGSTACK.stackoverflow.[public].users 
WHERE Id = 1;

SQL Server 通过网络获取远程用户表的全部内容（所有行），并在本地过滤它们。查询运行时间超过 3 分钟。

实际的查询计划不显示本地过滤器 - 这意味着 SQL Server 仅从远程服务器获取 1 行：

查询计划

  • 问题 1：是否可以在不重写查询以使用 OPENQUERY 的情况下避免这种情况（获取远程表的全部内容）？
  • 问题 2：即使只是尝试获取估计的执行计划，实际上也会获取远程表中的所有行，并且仅需要 3 分钟以上才能获得估计的计划。这可以避免吗？

其他技术细节：

  • 远程服务器在 id 上有一个主键，查询在 Postgres 上以毫秒为单位运行
  • 远程 Postgres 服务器的 pg_stat_activity 显示 SQL Server 正在运行此查询：select * from "stackoverflow"."public"."users"- 请注意表上缺少任何过滤器
  • Postgres ODBC 驱动程序 16.00 2023-09-16，最新版本来自此处
  • SQL Server 2022 内部版本 16.0.4095.4
  • 网络控制面板甚至显示查询开始后吞吐量急剧上升 - 再说一次，我在这里只拉一行：

网络控制面板

sql-server

4 个回答

  1. Best Answer

    您是否在 odbc 驱动程序选项中将“Use Declare/Fetch”设置为 true？从文档中“如果为真，驱动程序会自动使用声明游标/获取来处理 SELECT 语句，并在缓存中保留 100 行。这主要是一个很大的优势，特别是如果您只对读取而不是更新感兴趣。它会导致驱动程序不会占用大量内存来缓冲整个结果集。如果设置为 false，则不会使用游标，驱动程序将检索整个结果集。”

    https://odbc.postgresql.org/docs/config.html

    • 3

  2. 我认为问题在于关键字TOP... SQL Server 不知道 PostgreSQL ( LIMIT) 中的等效关键字，因此他获取所有表内容，然后应用您这边保留的任何过滤器和关键字...可以您尝试用WITH要测试的子句重写 select 语句吗？

    像这样的东西

    WITH pgt AS (SELECT * 
             FROM PGSTACK.stackoverflow.[public].users 
             WHERE Id = 1)
SELECT TOP 1 * 
FROM pgt

    我没有任何带有 PG 和 SQL 的虚拟机来测试它，但这是我首先想到的

    • 0

  3. 在回顾了 Brent 对链接服务器的看法并询问应用程序是否可以直接打开与 Postgres 的连接之后。我将创建一个存储过程，将查询移动到 Postgres 服务器上的存储过程，然后调用该过程。

    • -1 
  4. select * from openquery(PGSTACK,'select * from users limit 1')

    通过使用 openquery，您可以编写更复杂的 SQL（包括联接），并且仍然具有其他数据库上索引的性能。

    declare @sql varchar(max) 
declare @ID int =1 
set @sql = 'select * from openquery(PGSTACK,''select * from users where id ='+cast(@ID as varchar)+' '')'
exec(@sql)
    • -1

相关问题