强制 SQL Remote 查询远程过滤而不是本地过滤 [英] Forcing a SQL Remote Query to filter remotely instead of locally

问题描述

我有一个 MS SQL 查询，它通过远程服务器提取数据.我拉下的数据需要按运行时确定的日期进行过滤.当我像这样运行查询时:

I have a MS SQL Query that is pulling data via from a remote server. The data that I'm pulling down needs to be filtered by a date that is determined at run time.. When I run the query like this:

SELECT * FROM SERVER.Database.dbo.RemoteView
WHERE EntryDate > '1/1/2009'

然后远程应用过滤器......但是，我实际上不想使用1/1/2009"作为日期 - 我希望日期由用户定义的函数提供，如下所示:

then the filter is applied remotely... However, I don't actually want to use '1/1/2009' as the date - I want the date to be supplied by a user-defined function, like this:

SELECT * FROM SERVER.Database.dbo.RemoteView
WHERE EntryDate > dbo.MyCustomCLRDateFunction()

其中该函数是一个返回日期时间的自定义 CLR 标量值函数......(你可能会问我为什么需要这样做......细节有点复杂，所以相信我 -我必须这样做.)

where the function is a custom CLR scalar-valued function that returns a date time... (You may ask why I need to do this... the details are a bit complicated, so just trust me - I have to do it this way.)

当我运行这个查询时，远程查询没有被远程过滤 - 过滤是在所有数据被下拉后完成的(400,000 行 vs 100,000 行)，这会产生显着差异.

When I run this query, the remote query is NOT filtered remotely - the filtering is done after all of the data is pulled down (400,000 rows vs 100,000 rows) and it makes a significant difference.

有没有办法强制查询远程进行过滤?

Is there a way that I can force the query to do the filtering remotely?

谢谢！

推荐答案

你不能像这样发送一个查询，还是必须在 select 语句中实际调用 clr 函数?

Can't you just send a query like this, or does the clr function have to actually be called inside the select statement?

Declare @datetime datetime
Set @datetime = dbo.MyCustomCLRDateFunction()

SELECT * FROM SERVER.Database.dbo.RemoteView
WHERE EntryDate > @datetime

这篇关于强制 SQL Remote 查询远程过滤而不是本地过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！