Proxy table query takes a long time for 3 results/pass thru query/indexed table

[Former Member]

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.

I. 02/08 10:40:44. The query is being processed in FULL PASSTHRU mode
I. 02/08 10:40:44. The Original Statement is 
I. 02/08 10:40:44.  select M_Reject.Unique_ID from M_Reject where(M_Reject.RX_Number = '1234567') and(M_Reject.Dispenser_ID = '27845') and(M_Reject.Claimant_ID = '0') and(M_Reject.Service_ID = '0') and(M_Reject.New_Refill_Code = '0') and(M_Reject.Status = '1') order by M_Reject.UNIQUE_ID desc
I. 02/08 10:40:44. The Virtual Statement is 
I. 02/08 10:40:44.  select vt_1.expr_1 from vt_1
I. 02/08 10:40:44. The Remote Statement for vt_1 is 
I. 02/08 10:40:44.  select M_Reject.Unique_ID from M_Reject where(M_Reject.RX_Number = '1234567') and(M_Reject.Dispenser_ID = '27845') and(M_Reject.Claimant_ID = '0') and(M_Reject.Service_ID = '0') and(M_Reject.New_Refill_Code = '0') and(M_Reject.Status = '1') order by M_Reject.UNIQUE_ID desc
I. 02/08 10:40:44. Execute (testdb): 
I. 02/08 10:40:44. SELECT t1.Unique_ID FROM testdb.CLAIMS.M_Reject t1  WHERE t1.Status = '1'  AND t1.New_Refill_Code = '0'  AND t1.Service_ID = '0'  AND t1.Claimant_ID = '0'  AND t1.Dispenser_ID = '27845'  AND t1.RX_Number = '1234567' ORDER BY t1.Unique_ID DESC 

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).

Any thoughts?

[Former Member]

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.

[Former Member]

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.

[Former Member]

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?

[Former Member]

This is just an int column. The application on the client machine side was shut down and restarted, will no effect. I'll keep trying

[Former Member]

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.

[Former Member]

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.

[Former Member]

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.