Performance Issue Query

[jiaulislam]

Hello, I'm learning python oracledb techniques to execute PL/SQL SP. After getting the cursor from oracle query, I'm type casting or converting it to a Dataclass or Pydantic Model. Will there be any performance issue for say millions for records? If yes ! then what would be the best practice to do below for n rows where n could be millions.

"""
CREATE OR REPLACE PROCEDURE sp_test_1 (v_customer_id   IN     VARCHAR2,
                                       v_records          OUT SYS_REFCURSOR)
IS
BEGIN
    OPEN v_records FOR
        SELECT *
          FROM customers, addresses
         WHERE     customers.id = v_customer_id
               AND customers.id = addresses.customer_id(+);
END;
/
"""

from pydantic.dataclasses import dataclass


@dataclass
class CustomerF:
    id: int
    name: str
    contact_number: str
    is_active: bool
    address_id: int
    customer_id: int
    present_address: str
    permenent_address: str

connection = engine.raw_connection()
ref_cursor = connection.cursor()

try:
    with connection.cursor() as cursor:
        cursor.callproc('sp_test_1',(42, ref_cursor))

    customers = [CustomerF(*row) for row in ref_cursor]

    print(customers)
finally:
    ref_cursor.close()
    connection.close()

[anthony-tuininga]

A couple of comments that may be of interest:

• You can use the rowfactory attribute on cursors to reduce some overhead

ref_cursor.rowfactory = CustomerF
customers = ref_cursor.fetchall()

• the majority of the overhead will be in Python itself so if you don't need to convert to a dataclass you'll save plenty that way alone! Of course, that isn't nearly as convenient -- so you have to decide which is more important to you. :-)

[cjbj]

• Check the doc on Tuning Fetching from REF CURSORS.
• Think about whether you really need to have the data in Python, or whether you can do any desired data conversions using a PL/SQL block which will save data transfer costs

[jiaulislam]

Thanks for your reply. Well this was just a test to check how it's done. But in some projects I was thinking to do that. But the question is do I need prioritize the DX experience with performance? I guess not in my perspective. It's good to have some autocomplete in VSCode but performance does matter first in the end.