I am using sqlanydb python interface to query from sql anywhere 11. I struggle to understand one thing.

Since its very important to me to optimize queries, I was watching how much time it takes to do curs.execute(sql), this time was very low (around 5-10 ms). I would expect that query is done in this step. So it was surprising for me that next step (rowset = curs.fetchall()) takes much longer for more complicated queries, in my case up to 500ms and its fetching only few results (5-50). Which suggest that its not transferring of the results that takes time, but its probably doing the query.

Also if I use InteractiveSQL to test my queries, in messages tab it apparently shows the curs.execute(sql) time, because its always very low (around few milliseconds), which is misleading, because it takes longer to show the results for the more complex queries.

Could you please clarify what happens when execute and fetchall is called? I did not find relevant documentation which would explain that.

Also if you could suggest any tool/workflow for profiling the queries, so I can at least see the real time the query takes?

Thank you! Hopefully its not stupid question.

asked 20 Jun '14, 08:50

jarda_kotesovec's gravatar image

jarda_kotesovec
1214510
accept rate: 0%


FYI: There is no such thing as a "stupid question" on this forum - thanks for asking :-)

The curs.execute(sql) only starts the execution of the statement - parse, annotate, optimization, plan generation - and stops when the opened cursor is ready to fetch the first row. The fetchall() statement will do all of the actual work to fetch all of the rows and transfer them from the server to the client. Depending on the statement and the number of rows being fetched the execute could take longer (if a very complex statement that returns few or no rows) or the fetchall could take longer (if a simple statement and returns many many rows).

ISQL is designed to use JDBC (a 'native' interface) and ISQL only fetches enough rows from the cursor to fill the display - it does not fetch all of the rows - and hence the fetch portion is much more efficient (than python fetchall which (a) needs to move all data from the 'native' low level driver into python structures, and (b) fetches all of the rows!).

If you are only going to be using a few rows of the result set then you should not use fetchall but rather fetch each row individually until you have the number of rows that you require.

permanent link

answered 20 Jun '14, 09:23

Mark%20Culp's gravatar image

Mark Culp
23.2k9132273
accept rate: 40%

What you have described is what I would expect, but surprisingly my observation is different. If I use one particular more complex query that returns 2 rows as result (and two columns). I got these results: in InteractiveSQL it shows 2 rows and shows execution time: 9ms. In python execute time is 4ms and fetchall time is 328.8ms. Which really suggest that it queried the database once fetchall was called. Because If I do the same thing with less complex query I got following times: InteractiveSQL execution time: 8ms. Python execute time: 2.3ms and fetchall time 0.9ms.

(20 Jun '14, 10:02) jarda_kotesovec
Replies hidden

How many times did you run your test? I'm wondering if a checkpoint (or something else) occurred and that is what caused the extra time during the fetchall?

Typically when we're doing performance testing we run each test 5 or 10 (or more) times to make sure that transient events don't skew the results.

(20 Jun '14, 10:17) Mark Culp
Comment Text Removed

It was at least 3 times in row. Pretty sure that measurements are correct.

(10 Jul '14, 12:37) jarda_kotesovec
1

You may be able to answer some of your questions by turning on request logging to see what is sent to and from the server, and when.

(10 Jul '14, 14:29) Breck Carter

While I cannot answer that question in detail, you are apparently relating to the concept of cursors that tie a query's result set to an application/database client. The Python interface may not deal with cursors in an obvious way (say, other than APIs like ESQL or ODBC) but nevertheless they are used here, too.

Some cursor types (INSENSITIVE cursors) require that the result set is completed before the first row can be fetched whereas others (SENSITIVE cursors) require that the resulting rows are queried dynamically while they are fetched one by one - other types are between these extremes.

Some (but certainly not all revevant!) aspects that will matter here:

So the division of labour between the "execute" and the fetching phase will depend on the query's and the cursor's characteristics.

permanent link

answered 20 Jun '14, 09:25

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

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:

×242
×16

question asked: 20 Jun '14, 08:50

question was seen: 844 times

last updated: 10 Jul '14, 14:29