|
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 Also if I use InteractiveSQL to test my queries, in messages tab it apparently shows the 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. |
|
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. 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
(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. |
