django sql performance analysis using sh script

sql query timing

local db

cat db-performance/make_data_quick_run_local_db_clean.txt | grep '(0' | cut -d '(' -f 2 | cut -d ')' -f 1 | awk '{ sum += $1; count++ } END { print "Count:", count; if(count > 0) print "Average in second:", sum/count }'

Count: 612633
Average in second: 6.83476e-05

remote db , post jemery changes

cat db-performance/make_data_quick_after_jeremy_changes_clean.txt | grep '(0' | cut -d '(' -f 2 | cut -d ')' -f 1 | awk '{ sum += $1; count++ } END { print "Count:", count; if(count > 0) print "Average in second:", sum/count }'

Count: 318074
Average in second: 0.0127492

Tiger’s two cents

I think the fundamental cause is the round-trip time excluding the process time in remote-db i.e. the time needed for a network package goes from our laptop to remote-db and back.

before you start, remove all data in the database

connect to postgres sqll

• for local db, make psql
• for remote db, use matt’s script in oneview-infra, python scripts/connect_db.py --developer your_name

drop and recreate database oneview

 \c postgres;  # by default, postgres connects to database called `oneview` and next drop command will fail
drop database oneview with (force);
create database oneview;

Then restart django container, you should see migrations are being applied.

how to log all django sql

1. enable database logs which logs sql duration and sql query

"django.db.backends": {
    "handlers": ["console"],
    "level": "DEBUG",
    "propagate": False,
},

docker exec --env -t -i oneview-django-1 poetry run python manage.py import_curo_data "[email protected]" --use_test_accounts 2> make_data_quick_run_after_jeremy_changes.txt > make_data_quick_run_after_jeremy_changes.txt

clean up file to prevent file > 100 megabyte to avoid github limit

> cat /Users/yuhao.huang/Documents/oneview/db-performance/make_data_quick_after_jeremy_changes.txt | grep '(0' | cut -d ' ' -f 1-10 > make_data_quick_after_jeremy_changes_clean.txt

with local db, stats about make data-quick

In total, make data-quick takes 17 minutes with 612633 sql query.

stats on SQL Keyword

> cat make_data_quick_run_local_db.txt | grep '(0' | cut -d ' ' -f 6 | sort | uniq -c | sort -h -r
176031 SELECT
164718 INSERT
94640 UPDATE
56522 SAVEPOINT
56522 RELEASE
28623 BEGIN;
28598 COMMIT;
6928 DELETE
  25 ROLLBACK;
  25 ROLLBACK
   1

curo sync timing

> cat db-performance/make_data_quick_run_local_db.txt | grep import_curo_data
2026-03-03 14:33:07,559 [INFO] oneview.management.commands.import_curo_data: Syncing CURO codes. CURO code count 0
2026-03-03 14:33:14,190 [INFO] oneview.management.commands.import_curo_data: Finished syncing. CURO code count 1329
2026-03-03 14:33:14,190 [INFO] oneview.management.commands.import_curo_data: Syncing CURO entities. CURO entities count 0
2026-03-03 14:34:01,516 [INFO] oneview.management.commands.import_curo_data: Finished syncing. CURO entities count 13029
2026-03-03 14:34:01,516 [INFO] oneview.management.commands.import_curo_data: Syncing Business Units. Business Units count 1
2026-03-03 14:34:02,780 [INFO] oneview.management.commands.import_curo_data: Finished syncing. Business Units count 39
2026-03-03 14:34:02,781 [INFO] oneview.management.commands.import_curo_data: Syncing Currencies. Currency count 0
2026-03-03 14:34:05,409 [INFO] oneview.management.commands.import_curo_data: Finished syncing. Currency count 178
2026-03-03 14:34:05,410 [INFO] oneview.management.commands.import_curo_data: Syncing site accounts. Site count 0
2026-03-03 14:34:06,211 [INFO] oneview.management.commands.import_curo_data: Finished syncing. Site count 16
2026-03-03 14:34:06,212 [INFO] oneview.management.commands.import_curo_data: Syncing staff accounts. Staff count 1
2026-03-03 14:35:23,070 [INFO] oneview.management.commands.import_curo_data: Finished syncing. Staff count 891
2026-03-03 14:35:23,081 [INFO] oneview.management.commands.import_curo_data: Syncing client contacts and accounts. Contact count 0. Account count 0
2026-03-03 14:36:29,508 [INFO] oneview.management.commands.import_curo_data: Finished syncing. Contact count 63. Account count 42
2026-03-03 14:36:29,509 [INFO] oneview.management.commands.import_curo_data: Syncing providers. Provider count 0
2026-03-03 14:37:15,868 [INFO] oneview.management.commands.import_curo_data: Finished syncing. Provider count 2956
2026-03-03 14:37:15,869 [INFO] oneview.management.commands.import_curo_data: Syncing products. Product count 0
2026-03-03 14:37:20,870 [INFO] oneview.management.commands.import_curo_data: Finished syncing. Product count 267
2026-03-03 14:37:20,871 [INFO] oneview.management.commands.import_curo_data: Syncing product provided. Product providers count 0
2026-03-03 14:37:23,500 [INFO] oneview.management.commands.import_curo_data: Finished syncing. Product providers count 104
2026-03-03 14:37:23,501 [INFO] oneview.management.commands.import_curo_data: Syncing Activity Groups. Activity Group count 0
2026-03-03 14:37:25,896 [INFO] oneview.management.commands.import_curo_data: Finished syncing. Activity Group count 131
2026-03-03 14:37:25,897 [INFO] oneview.management.commands.import_curo_data: Syncing Activity Sub Groups. Activity Sub Group count 0
2026-03-03 14:37:40,660 [INFO] oneview.management.commands.import_curo_data: Finished syncing. Activity Sub Group count 1031
2026-03-03 14:37:40,661 [INFO] oneview.management.commands.import_curo_data: Syncing Fee Component. Fee Component count 0
2026-03-03 14:37:52,877 [INFO] oneview.management.commands.import_curo_data: Finished syncing. Fee Component count 632
2026-03-03 14:37:52,878 [INFO] oneview.management.commands.import_curo_data: Syncing Annotations. Annotations count 0
2026-03-03 14:37:54,190 [INFO] oneview.management.commands.import_curo_data: Finished syncing. Annotations count 50
2026-03-03 14:37:54,191 [INFO] oneview.management.commands.import_curo_data: Syncing Provider Contacts. Provider Contacts count 0
2026-03-03 14:37:55,899 [INFO] oneview.management.commands.import_curo_data: Finished syncing. Provider Contacts count 52
2026-03-03 14:37:55,899 [INFO] oneview.management.commands.import_curo_data: Syncing Model Portfolios. Model Portfolios count 0
2026-03-03 14:38:02,693 [INFO] oneview.management.commands.import_curo_data: Finished syncing. Model Portfolios count 369
2026-03-03 14:38:02,694 [INFO] oneview.management.commands.import_curo_data: Syncing Project Categories. Project Categories count 0
2026-03-03 14:38:04,455 [INFO] oneview.management.commands.import_curo_data: Finished syncing. Project Categories count 86
2026-03-03 14:38:04,456 [INFO] oneview.management.commands.import_curo_data: Syncing Project Stages. Project Stages count 0
2026-03-03 14:38:15,978 [INFO] oneview.management.commands.import_curo_data: Finished syncing. Project Stages count 807
2026-03-03 14:38:15,978 [INFO] oneview.management.commands.import_curo_data: Mapping Project Categories and  Project Stages.
2026-03-03 14:38:16,230 [INFO] oneview.management.commands.import_curo_data: Finished mapping.
2026-03-03 14:38:16,231 [INFO] oneview.management.commands.import_curo_data: Syncing client income. Client income count 0
2026-03-03 14:39:46,361 [INFO] oneview.management.commands.import_curo_data: Finished syncing. Client income count 5000
2026-03-03 14:39:46,362 [INFO] oneview.management.commands.import_curo_data: Syncing client expenditures. Client expenditures count 0
2026-03-03 14:40:27,742 [INFO] oneview.management.commands.import_curo_data: Finished syncing. Client expenditures count 2242
2026-03-03 14:40:27,742 [INFO] oneview.management.commands.import_curo_data: Syncing PAD reviews
2026-03-03 14:40:59,667 [INFO] oneview.management.commands.import_curo_data: Finished syncing.