SQL Queries slow due to Fetch performance

MichaelAquilina / Reddit-Recommender-Bot

Indentifying Interesting Documents for Reddit using Recommender Techniques

7 stars 0 forks source link

SQL Queries slow due to Fetch performance #77

Closed MichaelAquilina closed 10 years ago

MichaelAquilina commented 10 years ago

SQL filtering is very fast with the large number of indexes in use. However due to the large volume of information that comes through, fetching performance is lacking.

This can probably be performed by:

Further limiting results through a restriction in the where clause
Removing ROW_FORMAT=FIXED for character based columns. This should also decrease the size of the database on disk.
Choosing a more appropriate cursor might also help. Currently trying to understand what each cursor does on this stackoverflow post

MichaelAquilina commented 10 years ago

When dealing with queries that have a short query time but very large fetch time (because of a large amount of data being sent back) it is more efficient to make use of SSCursor which retrieves data one at a time using the following syntax:

sscur.execute("""...""")
for row in sscur:
     dosomething()

Do not use fetchall as it completely defeats the purpose of using an SSCursor. This information is based on the reply of this stackoverflow post