Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Linked server performance w/ variables in WHERE

Author  Topic 

aiken
Aged Yak Warrior

525 Posts

Posted - 2006-01-18 : 21:15:21
Aaaaargh. This took me a couple of hours to track down, and now I'm mystified as to how to solve it.

The scenario: two DB servers, wdb01 and wdb02. wdb02 is a logging server, and the query in question need to pull data from wdb02 into a stored procedure running on wdb01.

For testing purposes, the same issues happpen in QA just running the query, the SP isn't necessary. Both servers have each other as linked servers, using the collation compatible flag.

Both of these queries run against wdb01 (in the real environment, the results are stuffed into a temp table and used later; this is a stripped down version just to illustrate the problem).

Query 1
select distinct i_users_source
from wdb02.web_log.dbo.log_browses lb
where lb.i_users_target=1


Query 2
declare @iUsers int
select @iUsers=1

select distinct i_users_source
from wdb02.web_log.dbo.log_browses lb
where lb.i_users_target=@iUsers


Functionally identical, right? Query 1 takes about 50ms, query 2 takes about 90 seconds. the log_browses table is big, about 10 million records, so I'm figuring that for some reasn the variable version is making SQL pull the table across and then run the query locally on wdb01.

But why? the i_users_target column is an int, just like the @iUsers variable. There's an index on that column, too (and obviously it runs fast replacing the variable with just "1").

Anyone seen this? It seems really bizarre.

Thanks
-b

aiken
Aged Yak Warrior

525 Posts

Posted - 2006-01-18 : 21:47:36
I've found a workaround: by adding another linked server that uses "Microsoft OLE DB Provider for SQL Server" instead of the "SQL Server" radio button for server type, and then setting the "provider settings" to include "dynamic parameters", everything works as it should.

Bit isn't that really weird? Shouldn't SQL server know that another SQL server can handle parameters? Both servers are SQL 2000 SP3. wdb01 is running on windows 2000 server, wdb02 is running in windows 2003 server, but I can't see that mattering.

Cheers
-b
Go to Top of Page

aiken
Aged Yak Warrior

525 Posts

Posted - 2006-01-21 : 03:19:42
Bumping this, because it's irritating me that I can't just use a normal linked server here. Anyone seen something like this?

Thanks
-b
Go to Top of Page
   

- Advertisement -