gistfile1.py

#!/usr/bin/env python
"""Attempt to see whether indices offers significant performance benefits.

"""
import os
import time
import sqlite3
import numpy as np
import random

DB='hlr_test.sqlite3'

create_indexes = [
    #'''CREATE INDEX IF NOT EXISTS idx_equipmentwatch_eqipment_id ON EquipmentWatch(equipment_id)''',
    #'''CREATE INDEX IF NOT EXISTS idx_equipmentwatch_subscriber_equipment_id ON EquipmentWatch(subscriber_id, equipment_id)''',
    #'''CREATE INDEX IF NOT EXISTS idx_equipment_imei ON Equipment(imei)''',
    #'''CREATE INDEX IF NOT EXISTS idx_sms_sent ON SMS(sent)''',
    #'''CREATE INDEX IF NOT EXISTS idx_sms_receiver_id ON SMS(receiver_id)''',
    #'''CREATE INDEX IF NOT EXISTS idx_sms_src_addr ON SMS(src_addr)''',
    '''CREATE INDEX IF NOT EXISTS idx_sms_dest_addr ON SMS(dest_addr)''',
    #'''CREATE INDEX IF NOT EXISTS idx_subscriber_imsi ON Subscriber(imsi)''',
    #'''CREATE INDEX IF NOT EXISTS idx_subscriber_imei ON Subscriber(tmsi)''',
    #'''CREATE INDEX IF NOT EXISTS idx_subscriber_lac ON Subscriber(extension)'''
]

queries = [
  '''SELECT Equipment.*
    FROM Equipment JOIN EquipmentWatch ON EquipmentWatch.equipment_id=Equipment.id
    WHERE EquipmentWatch.subscriber_id = ? 
    ORDER BY EquipmentWatch.updated DESC
  ''',
  '''SELECT id FROM Equipment WHERE imei = ?
  ''',
  '''INSERT OR IGNORE INTO Equipment (imei, created, updated) 
    VALUES (?, datetime('now'), datetime('now'))
  ''',
  '''INSERT OR IGNORE INTO EquipmentWatch (subscriber_id, equipment_id, created, updated) 
    VALUES (?, ?, datetime('now'), datetime('now'))
  ''',
  '''UPDATE EquipmentWatch 
    SET updated = datetime('now') 
    WHERE subscriber_id = ? AND equipment_id = ?
  ''',
  '''SELECT SMS.* FROM SMS
    JOIN Subscriber ON SMS.receiver_id = Subscriber.id
        WHERE SMS.id >= 1 AND SMS.sent IS NULL AND Subscriber.lac > 0
        ORDER BY SMS.id LIMIT 1
  ''',
  #'''DELETE FROM SMS WHERE src_addr=? OR dest_addr=?
  #''',
  '''SELECT SMS.* FROM SMS
    JOIN Subscriber ON SMS.dest_addr = Subscriber.extension 
    WHERE Subscriber.id >= ?
      AND SMS.sent IS NULL 
      AND Subscriber.lac > 0
      AND SMS.deliver_attempts < ? 
    ORDER BY Subscriber.id, SMS.id LIMIT 1
  ''',
  '''UPDATE Subscriber
    SET updated = datetime('now'), name = ?, extension = ?, authorized = ?, tmsi = ?, lac = ?, expire_lu = NULL
      WHERE imsi = ?
  ''',
  '''SELECT * from Subscriber WHERE LAC != 0 AND authorized = 1
  ''',
  '''SELECT id FROM Subscriber
    WHERE lac != 0 AND ( expire_lu is NOT NULL AND expire_lu < datetime('now') )
    LIMIT 1
  ''',
  '''SELECT * FROM Subscriber WHERE tmsi = ?
  ''',
  '''SELECT * FROM Subscriber WHERE extension = ?
  '''
]

def params_for(query):
  random.seed()
  return [random.randint(100, 1000000) for i in range(query.count('?'))]
    

def prepare_fresh_db(with_indexes):
  import shutil
  shutil.copy('hlr.sqlite3', DB)

  if with_indexes:
    conn = sqlite3.connect(DB)
    c = conn.cursor()
    for q in create_indexes:
      c.execute(q)

    conn.commit()
    conn.close()


def run_queries():
  params = [params_for(q) for q in queries]
  REPEAT=10000

  for with_indexes in [True]:
    print "With indexes: %i"%with_indexes
    prepare_fresh_db(with_indexes)
    
    conn = sqlite3.connect(DB)
    conn.text_factory = str
    c = conn.cursor()
    ts_start = time.time()
    for n in range(REPEAT):
      for idx,q in enumerate(queries):
        #print q, params[idx]
        c.execute(q, params[idx])
      conn.commit()

    execution_time = time.time() - ts_start
    print "Results of %d repeats for batch of %d queries"%(REPEAT, len(queries))
    print "%f sec, %f sec/batch"%(execution_time, execution_time/REPEAT)


run_queries()