indexed table

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?

proxy proxy-tables

asked 08 Feb '11, 16:12

Adam
19●3●3●5
accept rate: 0%

edited 08 Feb '11, 16:32

flat view

One Answer:

active answers oldest answers newest answers popular answers

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.

permanent link

answered 08 Feb '11, 17:39

Karim Khamis
5.6k●5●38●70
accept rate: 40%

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.

(08 Feb '11, 18:33) Adam

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?

(08 Feb '11, 19:34) Karim Khamis

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

(09 Feb '11, 04:04) Adam

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.

(09 Feb '11, 05:29) Adam

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.

(09 Feb '11, 12:35) Karim Khamis

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.

(09 Feb '11, 12:36) Karim Khamis

More comments hidden

showing 5 of 6 show all flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

proxy-tables ×67
proxy ×7

question asked: 08 Feb '11, 16:12

question was seen: 2,587 times

last updated: 08 Feb '11, 17:39

SAP SQL Anywhere

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

Follow this question

Related questions