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.
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 1select distinct i_users_sourcefrom wdb02.web_log.dbo.log_browses lbwhere lb.i_users_target=1 Query 2declare @iUsers intselect @iUsers=1select distinct i_users_sourcefrom wdb02.web_log.dbo.log_browses lbwhere 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 |
|
|
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 |
|
|
|
|
|
|
|