select on linked server view is slow when using a variable in WHERE clause

Question

select on linked server view is slow when using a variable in WHERE clause

Ted Zatopek Old

Ten Centuries

Points: 1299
More actions
May 17, 2006 at 3:00 pm

#152981

I've got 2 queries ...
SELECT * FROM RemoteServer.dbname.dbo.viewname
where fieldname = @var
SELECT * FROM RemoteServer.dbname.dbo.viewname
where fieldname = 'abc'
...and the 1st query runs in 2 sec, while the second runs in 30 secs.   I'm guessing the difference is that the first query actually pulls all the records over and then screens out the unwanted records, whereas the second query only pulls over the matched records.
Is there a server option or something that can be set in order to force that first query to send back over only the matched records?
The application that uses this data can only makea connection to the local server, it cannot connect to the remove server.   So I have to supply either a view or a stored procedure in the local database that hits this remote view.   The application will only want back specific rows from the remote view, so I need to be able to screen also.
My best solution to needing to use a variable like this is to dynamically build the SQL stmt so that what actually gets executed is the second stmt.   Although this means using a SP and for this particular application a view on the local server would work much better.
Any help is appreciated.
...thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

David McFarland SSChampion Points: 11815 More actions · Answer 1

I have a feeling you're just seeing the results of caching. Try switching the order you run them in and see what happens. If it's not a caching issue, please give us a bit more details, such as size of table being pulled from, a bit of detail about the data within, the value of @var, etc.

Ted Zatopek Old Ten Centuries Points: 1299 More actions · Answer 2

I have run these in different order multiple times and consistently get the same results, the times of 30 secs and 2 secs are an average skipping the first run.

The screened result set returned is 1 record, while an unscreened result set is 6500.

Variable is a varchar(12) that is typically 12 chars long with both numbers and letters.

The result set is roughly 20 fields with some char and some integer fields. The underlying view behind the scenes on the remote servers is a join of say 15 tables.

David McFarland SSChampion Points: 11815 More actions · Answer 3

It seems like you are comparing apples and oranges. To see if the problem is with the variable, change your constant to have the exact same value as the variable and then give it a shot. I don't know of any reason that:

DECLARE @var varchar(3)

SET @var = 'abc'

SELECT * FROM RemoteServer.dbname.dbo.viewname where fieldname = @var

would run any slower on a linked server than:

SELECT * FROM RemoteServer.dbname.dbo.viewname where fieldname = 'abc'

but it sounds like that's not how you're testing the two.

Perhaps I'm missing something, so feel free to give as much detail about what you are doing as possible.

noeld SSC Guru Points: 96590 More actions · Answer 4

use Openquery if your literal value is hardcoded. If you can create an sp with a parameter and it works as you needed why do you need dynamic sql?

* Noel

Ted Zatopek Old Ten Centuries Points: 1299 More actions · Answer 5

I tried looking at the execution plans for each stmt and they show that the stmt with @var returns the whole recordset and then filters locally. Whereas the other stmt doesn't and only returns the one record.

noeld SSC Guru Points: 96590 More actions · Answer 6

Well then use dynamic and Openquery. It is for sure you'll get the filtered records only

Cheers,

* Noel

Ted Zatopek Old Ten Centuries Points: 1299 More actions · Answer 7

btw...

Thanks for the responses everybody they are a help.

Noel, my goal is to be able to use a view on the local server that uses a view on the remote server. So even though I have a way to use an SP, for this app it would work best if this was a view.

So if I use OpenQuery I think I have to dynamically build my SQL stmt and translate the variable into a literal. I've heard that OpenQuery doesn't allow variables.

David, yep that is exactly how ran my test.

Ted Zatopek Old Ten Centuries Points: 1299 More actions · Answer 8

So what I'm now hoping that somebody knows of is a way make a locally executed stmt like ...

declare @var VARCHAR(12)

select @var = 'ABCD1234EF56'

SELECT *

FROM RemoteServer.DBName.dbo.VW_name

WHERE FieldName = @var

... to do the filtering on the remote server before pasing the result set back to the local server.

noeld SSC Guru Points: 96590 More actions · Answer 9

If you are planing to join(inner) those results with some local table there may be a chance. Other wise the only way to ensure remote dynamic filtering is Openquery and yes it does not takes parameters that's why I mentioned dynamic sql in my previous post!

Cheers,

* Noel

Ted Zatopek Old Ten Centuries Points: 1299 More actions · Answer 10

Yes, exactly! I was hoping to be able to use the view to join to local table(s). So obviously that would be more efficient if the filter on the remote view was applied before the recordset was returned to the local server. What did you have in mind that I could persue?