Performance issues

[bymoye]

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 #148 (comment)

[bymoye]

test_cross_library_comparison.py.txt

This test file was created by GitHub Copilot.

[chandr-andr]

@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.

[bymoye]

@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.

[chandr-andr]

@bymoye
Sounds awesome! Can you make a PR with these changes?

[bymoye]

@bymoye Sounds awesome! Can you make a PR with these changes?

Sure, I'll do it when I have time. I'm busy lately.