psqlpy-python
Performance issues
Hi, @chandr-andr , I'll look into the clippy issue later. One other thing you might want to look at is I ran a test I wrote locally and got the following results:
🎯 extreme_query Performance Comparison
| Library | Status | Throughput (ops/s) | Avg Latency (ms) | Memory Delta (MB) |
|---|---|---|---|---|
| AsyncPG | Success | 32.3 | 30916.50 | 0.3 |
| Psycopg3 | Success | 32.2 | 31050.15 | 0.1 |
| PSQLPy | Success | 16.2 | 61665.21 | 2.1 |
| Databases | Success | 8.3 | 120346.22 | 0.9 |
🎯 bulk_insert Performance Comparison
| Library | Status | Throughput (ops/s) | Avg Latency (ms) | Memory Delta (MB) |
|---|---|---|---|---|
| AsyncPG | Success | 32.4 | 30893.69 | 0.1 |
| PSQLPy | Success | 32.2 | 31030.38 | 0.1 |
| Psycopg3 | Success | 32.1 | 31117.88 | 0.1 |
| Databases | Success | 8.1 | 123635.79 | 0.1 |
🎯 concurrent_read Performance Comparison
| Library | Status | Throughput (ops/s) | Avg Latency (ms) | Memory Delta (MB) |
|---|---|---|---|---|
| AsyncPG | Success | 32.3 | 30893.80 | 0.1 |
| PSQLPy | Success | 32.3 | 30953.28 | 0.0 |
| Psycopg3 | Success | 32.2 | 31029.34 | 0.0 |
| Databases | Success | 8.4 | 118577.62 | 0.4 |
🎯 complex_query Performance Comparison
| Library | Status | Throughput (ops/s) | Avg Latency (ms) | Memory Delta (MB) |
|---|---|---|---|---|
| Psycopg3 | Success | 33.9 | 29478.60 | 0.0 |
| PSQLPy | Success | 32.6 | 30641.29 | 0.0 |
| AsyncPG | Success | 31.5 | 31770.39 | 0.0 |
| Databases | Success | 13.2 | 75851.61 | 0.1 |
✅ Cross-Library Performance Comparison Complete!
I investigated the cause a little bit (not in depth), and I found that the problem might be that the execute method will build a complete StatementBuilder regardless of whether there are any parameters passed in. So this causes this problem. Maybe there should be a fast track for simple query code?
Originally posted by @bymoye in https://github.com/psqlpy-python/psqlpy/issues/148#issuecomment-3032493737
@bymoye I looked deeper into this benchmark and found an interesting thing - there is no real asynchronous, all operations are executed sequentially. If there is a problem with memory in one test, all other metrics, such as Throughput (ops/s) or Avg Latency (ms), just show how fast the driver can send queries to the database.
PSQLPy's main goal is to perform well in asynchronous code, not in sequential execution.
@chandr-andr Okay, I've been quite busy lately and might not have time for more testing. Actually, I previously tried creating a fast path (bypassing StatementBuilder construction) in the PSQLPyConnection.execute method in src/connection/impls.rs, and I got performance reports comparable to AsyncPG and Psycopg3.