1

I'm going through a database with python and using try and except a lot to handle queries. In trying to optimize my code, I've hit a weird bump.

This code:

try:
    cursor.execute("SELECT my_name FROM {}.{} LIMIT 1".format(myschema,mytable))
except(Exception, psycopg2.DatabaseError) as error:
    conn.rollback()
else:
    origName = cursor.fetchone()
    if origName is None:
        outputName = "ERROR2"
        return outputName
try:
    cursor.execute("SELECT different_column FROM {}.{} ORDER by a_column DESC LIMIT 1".format(myschema, mytable))
except(Exception, psycopg2.DatabaseError) as error:
    conn.rollback()
    try:...
        #more try/excepts and so on

takes about 19 minutes to run through the entire data warehouse.

but THIS code:

try:
    cursor.execute("SELECT my_column FROM {}.{} LIMIT 1".format(myschema,mytable))
except(Exception, psycopg2.DatabaseError) as error:
    conn.rollback()

origName = cursor.fetchone()
if origName is None:
    outputName = "ERROR2"
    return outputName
try:
    cursor.execute("SELECT different_column FROM {}.{} ORDER by a_column DESC LIMIT 1".format(myschema, mytable))
except(Exception, psycopg2.DatabaseError) as error:
    conn.rollback()
    try:...
        #exact same code with try/excepts and so on

literally completes running in about 1-1.5 minutes. Why is it so much faster if I remove that else? Is something being skipped/ignored? I feel like I must be doing wrong. I don't really understand how to just continue with my code after a try:except: statement.

singmotor
  • 3,930
  • 12
  • 45
  • 79
  • 2
    In the second version, if there's an error, you `fetchone` anyway, get None, and abort the whole thing (or maybe `fetchone` raises an exception and you abort that way). – user2357112 Apr 18 '17 at 21:53
  • 1
    yeah, in the first one, if there is no error, the else block runs. So if there is an error, there is no return, and the second try/except runs. in the second one the "else block" code always runs, so if you have an error, it will complete with outputname ERROR2, before the second try/except runs. Maybe try seeing if that's what's happening. – jh44tx Apr 18 '17 at 21:57
  • 1
    that was it, thanks @jh44tx – singmotor Apr 19 '17 at 18:46

1 Answers1

-1

This is because in the python world try-except blocks are very common and incredibly cheap to use. A try-except is cheaper than a false if statement if a != 1:.

The else-clause itself is interesting. It runs when there is no exception but before the finally-clause. That is its primary purpose. So if your try-except block doesn't catch, the else statement still runs. This is the primary reason it slows down your code.

Without the else-clause, the only option to run additional code before finalization would be the clumsy practice of adding the code to the try-clause. That is clumsy because it risks raising exceptions in code that wasn't intended to be protected by the try-block.

The use-case of running additional unprotected code prior to finalization doesn't arise very often. So, don't expect to see many examples in published code. It is somewhat rare.

Look at this post for some more information

Community
  • 1
  • 1
arshbot
  • 12,535
  • 14
  • 48
  • 71
  • How does this answer the question? Are you suggesting that the 18 minutes of improvements come from the evaluation of `else` statement itself? I'm not sure, because in the second version the `fetchone` block is always executed, while in the first version it is not, but the second version is faster. Perhaps you can include more details, like the one suggested by jh44tx in comments, that because of the return statement in the block in the else statement, it is faster. – justhalf Apr 18 '17 at 23:58