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

« First Time Poster || Using SQL Server to... »


antivirus | apache | asp | blogging | browser | bugtracking | cms | crm | css | database | ebay | ecommerce | google | hosting | html | java | jsp | linux | microsoft | mysql | offshore | offshoring | oscommerce | php | postgresql | programming | rss | security | seo | shopping | software | spam | spyware | sql | technology | templates | tracker | virus | web | xml | yahoo | home