Proxy tables just seem to not like me.
This query is full pass thru. If I run it on sql server, it completes in less than a second with three results. For some reason running it through SQLA as a proxy table takes forever. I can query top 1 from this table via SQLA and it returns right away.

This takes 13 minutes to complete. The original query is sent to SQL Server (verified via a sql trace - Im confident the issue is not on sql server as copying and pasting the query into Sql Management Studio runs as expected - right away).

How many rows are returned by the query? The remote data access layer will probably try to perform a wide fetch of 50 rows at a time. Is it possible that one of the rows beyond the first row takes a long time to come back? You might want to consider turning on ODBC tracing and seeing what is happening in the ODBC driver. Since the query is full passthru, SA is doing nothing more than wide fetching the rows and returning them to the client.

A mere three rows are being returned. ODBC tracing is showing nothing - no file even getting created (I tried manually editing registry settings as well). This query runs on sql server almost instantly - there should be no delay at all.

Note that you need to shut the ODBC application down first before you start ODBC tracing. So in this case, you would need to shut SA down and then start ODBC tracing. Having said that, if SQL Server returns the rows instantaneously; then SA should do the same. I would suggest you try ODBC tracing again just to see what is going on. Also, are any of the columns long (i.e. long varchar or long binary)? If you are using SA 11.x or below, then are any of the columns varchar(n) or char(n) where n is larger than 255?

wow - magically tonight its working fast. I would think 'fewer users on the system' however the sql server query as I mentioned was extremely fast, and no one else is using this SQLA database. All the other queries performed OK except this one so it wasn't from load on the system. This is weird - leaves me a bit unsettled.

ODBC tracing still isnt working after client restart and server service restart.

Very strange. If the problem reappears and you get an idea of what circumstances cause the slow down, then open a case or post the extra information and I'll see if I can figure out what is going on. By the way, I believe you mentioned in an earlier post that you previously had a second SA server as the remote and are now switching to having SQL Server be the remote instead. If that is correct, then did you have any such problems when SA was the remote? I'm not actually point the finger at SQL Server, but rather trying to get a better handle on what might be going wrong.

Also, with respect to ODBC tracing, if it is still not working for you, then you might want to consider turning on the "machine-wide tracing" option in the ODBC administrator's "Tracing" tab and see if that does the trick.