Issue: select (15 minutes) vs execute_sql (35 seconds) 
Author: netsamir
State: closed
Created: 2021-08-25 06:25:21

Hi,


Please find below two very simple script that execute the exact same query, but:

- The first one, `select`, takes 15 min to complete
- The second one, `execute_sql`, takes 35 seconds to complete

Do you know how to improve performance of the first script please ?

### Here are the details of the database:

The database I am using is : sqlite3.  It is 5.1GB.

$ sqlite3 --version
3.7.17 2013-05-20 00:56:22 118a3b35693b134d56ebd780123b7fd6f1497668


    [ins] In [7]: print_table_sql(Sag)
    CREATE TABLE IF NOT EXISTS "sag" (
      "id" INTEGER NOT NULL PRIMARY KEY,
      "LogSysTime" DATETIME NOT NULL,
      "LogId" VARCHAR(255) NOT NULL,
      "Apd" VARCHAR(255) NOT NULL,
      "UnitName" VARCHAR(255) NOT NULL,
      "Primitive" VARCHAR(255),
      "Priority" VARCHAR(255),
      "RequestControl" VARCHAR(255),
      "RequestRef" VARCHAR(255),
      "RequestType" VARCHAR(255),
      "Sender" VARCHAR(255),
      "Receiver" VARCHAR(255),
      "ReceiverRef" VARCHAR(255),
      "ResponseControl" VARCHAR(255),
      "Service" VARCHAR(255),
      "Status" VARCHAR(255),
      "SwtRef" VARCHAR(255),
      "SwtRequestRef" VARCHAR(255),
      "SwtResponseRef" VARCHAR(255),
      "SwtTime" VARCHAR(255),
      "TransferRef" VARCHAR(255),
      "PayloadSize" INTEGER
    )


### Here are the details of the scripts:

1. Script : `select`

    $ cat script1.py
    
    from parser_User_stats_all_traffic import User, db
    from peewee import *
    
    db = SqliteDatabase('user_db.sqlite')
    
    \# database schema and creation

    for row in User.select(User, fn.count(1).alias('count')).group_by(User.Service):
        print(row.Service, row.count)

    
    $ time python script1.py
    
    <output>
    
    real    15m7.341s
    user    1m10.136s
    sys     1m6.303s


**Additional info:**  This script create 2 temporary files with 4.4 GB and then it hanged for quiet some time without consuming CPU nor Memory.


2. Script : `execute_sql`

    
    $ cat script2.py
    
    from parser_User_stats_all_traffic import User, db
    from peewee import *
    
    db = SqliteDatabase('user_db.sqlite')
    
    \# database schema and creation

    for row in db.execute_sql('select Service, count(1) from User group by Service'):
        print(row)

    $ time python script2.py

    <output>
    
    real    0m35.093s
    user    0m19.478s
    sys     0m9.827s

**Additional info:**  It creates 2 temporary files of 147 MB and return.

Hi,

I think I have found the solution to my problem by logging with DEBUG.

I have rewritten the query like that :

    for row in User.select(User.Service, fn.count(1).alias('count')).group_by(User.Service):
        print(row.Service, row.count)

    DEBUG:peewee:('SELECT "t1"."Service", count(?) AS "count" FROM "sag" AS "t1" GROUP BY "t1"."Service"', [1])

instead of 

    DEBUG:peewee:('SELECT "t1"."id", "t1"."LogSysTime", "t1"."LogCorrelationId", "t1"."ApplicationId", "t1"."UnitName", "t1"."Primitive", "t1"."Priority", "t1"."RequControl", "t1"."RequtRef", "t1"."RequType", "t1"."Requestor", "t1"."Resp", "t1"."ReserRef", "t1"."ResponseControl", "t1"."Service", "t1"."Status", "t1"."SwtRef", "t1"."SwRequestRef", "t1"."SwResponseRef", "t1"."SwTime", "t1"."TransferRef", "t1"."PayloadSize", count(?) AS "count" FROM "sag" AS "t1" GROUP BY "t1"."Service"', [1])

If you were using postgres your first query would not have even run because you were selecting all columns but only grouping by one of them.

The second query is correct:

    q = User.select(User.Service, fn.count(User.id).alias('count')).group_by(User.Service)