Hi @zzzeek ,

I have been jumping all around the codebase to try and figure out the right set of attributes to enable for executemany or do_executemany support as you suggested. It seems like the bigquery dbapi cursor has support for executemany (shown here: https://github.com/googleapis/python-bigquery/blob/main/google/cloud/bigquery/dbapi/cursor.py#L236) but the dialect as a whole does not support the idea of "returning" as far as I can tell. With all this in mind, can you suggest the set of attributes I should set to get the best ORM bulk insert performance (better than single row entry)?

I can try and debug the bound parameter formatting, but my breakpoint statements are not even being reached because of various modes I am putting the program into via argument flags. I know that the sqlalchemy bigquery dialect was partially based on the Postgress dialect, so I have been trying to follow that as an example.

Here are some of my attempts:

• use_insertmanyvalues=True -> single row inserts
• use_insertmanyvalues=True and use_insertmanyvalues_wo_returning=True -> multi value inserts
• insert_executemany_returning=True -> single row inserts

does BigQuery support the concept of server-generated values, most primarily when you INSERT a bunch of rows, there's an auto-generated integer primary key? the ORM needs to get those back. if there isn't, then RETURNING is not very critical. Overall, if there's no RETURNING, there's not much point in implementing "insertmanyvalues" unless executemany() has performance issues.

If there are server-generated integer primary keys, then what is the means by which a newly generated integer primary key is returned to the client after a single-row INSERT statement?

BigQuery does not have server-generated values or the concept of primary keys, you could insert the same row over and over again if you wanted. That being said, I am just trying to ferry data over in the most efficient and performant way possible. The only indication I see of multivalue inserts in the current BigQuery dialect is "BigQueryDialect.supports_multivalues_insert = True"

Side Note: Although I know its completely un-safe and not guaranteed server-side, I am using SQLalchemy to create primary/foreign key relationships/constraints in my ORM. My application takes great lengths to ensure best effort primary key unique-ness before inserting data via ORM session eventing. I only say this in case it matters regarding application state when performing multi-value inserts.

Thanks for your reply! After looking at the DBAPI cursor executemany function, it looks like no work has been done to optimize its batch INSERT performance and it executes statements one at a time. I made this ticket pointing out the issue: googleapis/python-bigquery#2048

Thanks again for your guidance @zzzeek !

Hi @zzzeek, one more question for you. Do you know if there is any mechanism to speed up session inserts on a complex object by issuing child inserts in parallel?

All the documentation I see on sessions and async sessions say they are designed to insert objects one at a time and any parallelism should be done through multiple sessions. But what if I am trying to only insert one complex object and trying to speed up the underlying child object inserts? I was thinking of making some sort of thread pool that could issue all the inserts in parallel and delete all inserted child objects if there was an issue on one of the threads, but I was wondering if you have any experience with this?

Any suggestions on trying to "hide" the latency of serial inserts of child objects with complex relational ORM objects would be helpful, thanks!

you can't emit SQL statements in parallel on a single database connection. you need to have multiple connections, and you also have the challenge of rows that are dependent on each other being inserted in the correct order. so you'd probably want to do chunking horizontally, that is, have chunks of parent objects each with all of their child objects, so that different chunks are not dependent on each other.

the ORM Session has absolutely zero capability to itself do multiple tasks in parallel, at every level, so you'd need to use a distinct session per chunk. you can put this behind a fairly minimal facade but you'll never make it invisible and IMO nor should you want to.

Understood, thanks again!