Pipelined database operations with python-oracledb 2.4 – Christopher Jones
Python-oracledb 2.4 introduces pipelining functionality to improve the performance and scalability of your Python applications. Pipelining is simple in python-oracledb: you add operations such as INSERT and SELECT statements to a “pipeline” object, send that object to the database which processes the statements, and finally all the results will be returned to the application. Since the API is asynchronous, your application can submit the pipeline and continue with other local tasks while the database is doing its processing. This lets the database server and the application be kept busy, giving efficiencies and letting them work concurrently instead of waiting on each other for statements and results to be submitted/fetched.
Multiple SQL statements are sent in a single round-trip. Your application continues doing non-database work (i.e. do_local_stuff()) while the SQL statements are being executed. Results are returned from the database when it has executed all statements.
Pipelining Overview
Pipelining is useful when many small database operations need to be performed in rapid succession. It is supported by various drivers, including JDBC, Oracle Call Interface, ODP.NET, and Python.
The benefits of Oracle Database 23ai Pipelining:
Your app can do local work at the same time the database is processing statements.Your app doesn’t have to wait on one database response before sending a second statement.Your app is kept busy.After the database finishes one statement, it doesn’t have to wait for your app to fetch results and send a second statement.The database is kept busy.Fewer round-trips: reduced Oracle network listener wake-ups. Reduced interrupts. More efficient network usage.Better overall system scalability.
The reduction in round-trips is a significant contributor to pipelining’s performance improvement in comparison to executing the equivalent SQL statements individually. But, even with high-speed networks, where the performance benefit of pipelining may be lower, the database and network efficiencies of pipelining can still help system scalability.
Pipelining in Python is available via python-oracledb’s Async classes: this means you must use the default Thin mode of python-oracledb, and use it in an asynchronous programming style. Pipelining works with Oracle Database 23ai. (Although you can actually use the new python-oracledb API when connected to older database versions, you won’t get the internal pipelining behavior and benefits — this is recommended only for migration or compatibility reasons). You can get Oracle Database 23ai from Oracle Database Software Downloads.
You can use the following python-oracledb calls to add operations to a pipeline:
add_callfunc() – calling a stored PL/SQL functionadd_callproc() – calling a stored PL/SQL procedureadd_commit() – commiting current transaction on the connectionadd_execute() – executing one SQL statementadd_executemany() – executing one SQL statement with many bind valuesadd_fetchall() – executing a query and fetching all the resultsadd_fetchmany() – executing a query and fetching a set of the resultsadd_fetchone() – executing a query and fetching one row
Note the database processes the pipelined statements sequentially. The concurrency gain is betwen your application’s local work and the database doing its work.
Query results or OUT binds from one operation cannot be passed to subsequent operations in the same python-oracledb pipeline. If you need to use results from a pipeline operation in a subsequent database step, you can use multiple pipelines.
Social Network Example
An example use case is a social networking site. After you log in, your home page needs to show information gathered from various sources. It might show how many of your friends are also logged in. A news feed might show the top news items of the day. Current and forecast temperatures could be shown. Some of this data could be in a database, but require several distinct queries to fetch. Finding the current temperature might require Python calling out to a service to return that data. This is great use case for Pipelining: the distinct queries can be sent in a pipeline for processing, while the remote temperature sensor data is gathered at the same time.
The full code for this simple example web app is in
Truncated by Planet PHP, read more at the original (another 11198 bytes)
