cursor.fetchmany vs DECLARE BINARY CURSOR and FETCH

Question

I am not sure which is the most effective or the disadvantages/advantages of each approach or whether they are technically the same thing ?

That is

cursor.execute("DECLARE super_cursor BINARY CURSOR FOR SELECT names FROM myTable")

while True:
cursor.execute("FETCH 1000 FROM super_cursor")
rows = cursor.fetchall()

as expressed in the answer given by alecxe python postgres can I fetchall() 1 million rows?

In comparison to:

while True:
    results = cursor.fetchmany(1000)
    if not results:
        break
    for result in results:
        yield result

Should one use fetchmany as specified in psycopg2 or DECLARE BINARY.

I Have assumed that fetchmany and DECLARE BINARY both setup a temporary table on the database server side... The client side is an Apache server.

The website I am working with does calculations on user input to that of data in the database... Hence needs to load large amounts of data for pattern matching.

Thank you.

So you can certainly measure the performance of each approach! — under_the_sea_salad, Oct 07 '15 at 17:07
I guess I could... Was just wondering if they are equivalent or if one approach is better than the other. When I do speed tests I will let you know but for no I have just gone with fetchmany — red8alice, Oct 07 '15 at 23:09
Well, you should expect that fetchmany fetches batches of rows whereas fetchall fetches all, but this is not necessary so because fetchall could give you back an iterator (and your batch size would then be 1). It's even harder to tell how this works specifically for postgres. I think timing each approach is your best bet. — under_the_sea_salad, Oct 08 '15 at 00:42

score 0 · Answer 1 · answered Oct 10 '19 at 04:34

I have 1B records in my DB, DECLARE BINARY CURSOR solution can imediately iterate over the table whereas fetchmany has a very big delay to start working which I assume is because of preprocessing and loading data to the memory. So in my case BINARY CURSOR is more efficient.

cursor.fetchmany vs DECLARE BINARY CURSOR and FETCH

1 Answers1