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.
asked 20 Jun '14, 08:50
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.
answered 20 Jun '14, 09:23
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.
answered 20 Jun '14, 09:25