I noticed that when you use python sqlanydb module on a WAN (when the server is in a different datacenter), the sqlanydb module is very slow : it can take 16-30 seconds to fetch 1000 rows. Looks like the prefetching does not work. dbisql works fine with the same query and connection.

From a WAN :

$ time ./test.py real 0m20.118s user 0m0.112s sys 0m0.043s

From the same LAN :

real 0m0.168s user 0m0.081s sys 0m0.050s

Test case test.py:

import sqlanydb

def connect(): print "Before connecting"

conn = sqlanydb.connect(DSN="IQ_DB",UID="foo",PWD="bar", preFetchRows="1000")
print "After connecting"
return conn

c = connect()

cursor = c.cursor() query = """ select top 1000 foo from dbo.bar"""

cursor.execute(query) print "Executed"

results = cursor.fetchall() print "Fetched all"

for r in results: print r[0]

Will raise this to the github project as well. We are SAP customers btw.

This question is marked "community wiki".

asked 03 Aug '16, 18:20

huayingdebaba's gravatar image

huayingdebaba
41115
accept rate: 0%

Sound suspicious. I'd recommend you to profile the requests (just in case) to see if there is any difference. The SQL profiler should display you where timings were spent.
Then it might make sense to sniff the traffic between the application and the server with Wireshark and compare the results... but this is just an assumption.

(04 Aug '16, 04:51) Vlad

In fact, I am pretty sure it is not a network issue because dbisql / jdbc works fine : the issue is specific to python pyodbc / sqlanydb :

time dbisql -c "DSN=IQ_DB;UID=foo;PWD=bar" -nogui "select top 1000 foo from dbo.bar"

real 0m1.640s user 0m0.685s sys 0m0.052s

(04 Aug '16, 05:01) huayingdebaba
Replies hidden

Again, please run the SQL profiler with your DB. What is seen?

(04 Aug '16, 07:48) Vlad

What is SQL profiler and how to use it ?

(04 Aug '16, 09:23) huayingdebaba
Replies hidden

Here it is: http://dcx.sap.com/index.html#sqla170/en/html/28ded994d15a4ec1a423d06a5cd133bb.html*loio28ded994d15a4ec1a423d06a5cd133bb

But wait, you are using Sybase IQ as tag. So, please don't listen me. This forum is for SA, and I am afraid nobody will help you here. Please go to http://scn.sap.com/community/iq

(04 Aug '16, 15:44) Vlad

IQ may be the client, SA the database... it's not absolutely clear from the question.

(04 Aug '16, 17:01) Breck Carter

What version of SQL Anywhere are you using?

How does IQ fit in the picture?

(04 Aug '16, 17:03) Breck Carter
Replies hidden

It is IQ 16 and SQLAnywhere 16 client standalone and also IQ client 16 SP11 which comes with SA 16 client ... I tried all combinations and it is always slow. JDBC, dbisql are not slow. ODBC is slow too. The server side is IQ 16.

(04 Aug '16, 17:52) huayingdebaba

The SQL Profiler and other SQL-Anywhere-specific tools (probably) won't help diagnosis of a IQ database performance problem.

You might find someone on this forum to help, but also try the IQ community like Vlad suggested.

(05 Aug '16, 17:57) Breck Carter

Hi Breck,

Release 16. We use the SQLanywhere connector to connect to IQ. In fact, I found out what the issue was :

the prefetch server option was set to 'Conditional'.

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00801.1604/doc/html/san1281565175756.html

When setting it back to default 'Always', the problem goes away.

Best regards,

Arnaud

(09 Aug '16, 07:15) huayingdebaba
showing 4 of 10 show all flat view

The problem comes from this IQ server option : prefetch

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00801.1604/doc/html/san1281565175756.html

You need to set it to default 'Always'. The value 'Conditional' caused the issue.

permanent link

answered 09 Aug '16, 07:17

huayingdebaba's gravatar image

huayingdebaba
41115
accept rate: 0%

Your answer
toggle preview

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

Question tags:

×34

question asked: 03 Aug '16, 18:20

question was seen: 370 times

last updated: 09 Aug '16, 07:17