SQL Server Remote Query issue...
Date: 09/19/05
(SQL Server) Keywords: database, sql
Alright, here's the scenario. I have 65 million row table of zip codes... the table just has three columns: zip_cd varchar(10), lattitude, and longitude values. Zip_cd is varchar(10) because we have to store zip + plus 4 values in "xxxxx-xxxx" format... and no, my senior DBA has already overruled me adding two more numeric columns which would offer more optimized indexing. There's a clustered index on the zip_cd and we only ever run lookups on zip_cd, to get lat/long values.
Anyway, we have one simple query as follows:
--
DECLARE @zip_cd varchar(10)
SET @zip_cd = '60440-0001'
IF EXISTS(SELECT zip_cd
FROM serverX.databaseY.dbo.geodata
WHERE zip_cd = @zip_cd)
PRINT 'one'
ELSE
PRINT 'two'
--
Starting late last week, one of our servers running this query started taking forever and a day. Upon running it manually and checking the execution plan, I see that SQL Server, in all of its wisdom, decided to run the "SELECT zip_cd" portion WITHOUT the WHERE clause on the remote server, THEN apply a "filter" step for the WHERE clause. I attempted to replicate this on other servers and 2/3s of our other servers runs the query correctly, by sending everything to the remote server, whereas 2 other servers also just pull all 65 million rows down THEN filters it locally.
So my question is this... how in the hell can I force SQL Server to run the query in one fashion, rather than another? I know about using table and query hints, but the problem is that this is a remote server scenario, which Query Analyzer was complaining that index hints won't work.
Help?
x-posted...
Source: http://community.livejournal.com/sqlserver/34761.html