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"
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".
|
The problem comes from this IQ server option : prefetch You need to set it to default 'Always'. The value 'Conditional' caused the issue. |
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.
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
Again, please run the SQL profiler with your DB. What is seen?
What is SQL profiler and how to use it ?
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
IQ may be the client, SA the database... it's not absolutely clear from the question.
What version of SQL Anywhere are you using?
How does IQ fit in the picture?
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.
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.
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