forcing remote evaluation of like filters with linked servers

i have server1
server 2 is linked to server1
server 3 is linked to server1
all sql2005
on server1 i go
select foo from [server2].[db].[dbo].table where bar like '%ff%'
union all
select foo from [server3].[db].[dbo].table where bar like '%ff%'
by looking at the execution plan I can see that the remote selects have no
filter on them and that the filtering is applied locally by server1. this is
of course extremely inefficient.
The docs say that if I say thet the provider has sql like capability then
the query planner 'might' consider asking the remote server to do the like
processing.
I did that (even though it seems redundant since server1 knows that this is
another sql sevrer database).
What other magic buttons do I have to pressHi Paul
Using OPENQUERY would allow you to filter remotely.
John
"paul moore" wrote:
> i have server1
> server 2 is linked to server1
> server 3 is linked to server1
> all sql2005
> on server1 i go
> select foo from [server2].[db].[dbo].table where bar like '%ff%'
> union all
> select foo from [server3].[db].[dbo].table where bar like '%ff%'
> by looking at the execution plan I can see that the remote selects have no
> filter on them and that the filtering is applied locally by server1. this is
> of course extremely inefficient.
> The docs say that if I say thet the provider has sql like capability then
> the query planner 'might' consider asking the remote server to do the like
> processing.
> I did that (even though it seems redundant since server1 knows that this is
> another sql sevrer database).
> What other magic buttons do I have to press
>
>|||thanks - works great
seems like the 'normal' query processor should do it automatically though -
defaulting to the most inefficient way is surprising
"John Bell" wrote:
> Hi Paul
> Using OPENQUERY would allow you to filter remotely.
> John
> "paul moore" wrote:
> > i have server1
> > server 2 is linked to server1
> > server 3 is linked to server1
> > all sql2005
> >
> > on server1 i go
> > select foo from [server2].[db].[dbo].table where bar like '%ff%'
> > union all
> > select foo from [server3].[db].[dbo].table where bar like '%ff%'
> >
> > by looking at the execution plan I can see that the remote selects have no
> > filter on them and that the filtering is applied locally by server1. this is
> > of course extremely inefficient.
> >
> > The docs say that if I say thet the provider has sql like capability then
> > the query planner 'might' consider asking the remote server to do the like
> > processing.
> > I did that (even though it seems redundant since server1 knows that this is
> > another sql sevrer database).
> >
> > What other magic buttons do I have to press
> >
> >
> >|||Hi
I think if you were not using like you would be ok, but I haven't tested it!
John
"paul moore" wrote:
> thanks - works great
> seems like the 'normal' query processor should do it automatically though -
> defaulting to the most inefficient way is surprising
> "John Bell" wrote:
> > Hi Paul
> >
> > Using OPENQUERY would allow you to filter remotely.
> >
> > John
> >
> > "paul moore" wrote:
> >
> > > i have server1
> > > server 2 is linked to server1
> > > server 3 is linked to server1
> > > all sql2005
> > >
> > > on server1 i go
> > > select foo from [server2].[db].[dbo].table where bar like '%ff%'
> > > union all
> > > select foo from [server3].[db].[dbo].table where bar like '%ff%'
> > >
> > > by looking at the execution plan I can see that the remote selects have no
> > > filter on them and that the filtering is applied locally by server1. this is
> > > of course extremely inefficient.
> > >
> > > The docs say that if I say thet the provider has sql like capability then
> > > the query planner 'might' consider asking the remote server to do the like
> > > processing.
> > > I did that (even though it seems redundant since server1 knows that this is
> > > another sql sevrer database).
> > >
> > > What other magic buttons do I have to press
> > >
> > >
> > >