Gmd4q41u

1# -*- coding: utf-8 -*-
2"""
3Performance test for different types of update
4
5
6
7Results sample
8-----------------------------------------------
9
10In [1]: import db_test
11
12In [2]: db_test.prepare()
13
14In [3]: %timeit db_test.insert_on_duplicate_key_update()
1510 loops, best of 3: 61 ms per loop
16
17In [4]: db_test.prepare()
18
19In [5]: %timeit db_test.update()
2010 loops, best of 3: 52.1 ms per loop
21
22In [6]: db_test.prepare()
23
24In [7]: %timeit db_test.update_case()
2510 loops, best of 3: 33.3 ms per loop
26
27"""
28import random
29import MySQLdb
30
31db = MySQLdb.connect(host='localhost', db='db_test', read_default_file='~/.my.cnf')
32db.autocommit(False)
33
34nrows = 100
35nrows_total = 100000
36
37
38def prepare():
39    """
40    Prepare mysql table "foo"
41    """
42    c = db.cursor()
43    c.execute("""
44    DROP TABLE IF EXISTS `foo`;
45    CREATE TABLE `foo` (
46        `id` int(11) NOT NULL AUTO_INCREMENT,
47        `rating` int(11) NOT NULL,
48         PRIMARY KEY (`id`)
49    ) ENGINE InnoDB DEFAULT CHARSET latin1;
50    """)
51    c.close()
52    c = db.cursor()
53    rating_values = [(1, ) for _ in xrange(nrows_total)]
54    c.executemany("INSERT INTO `foo` (`rating`) VALUES (%s)", rating_values)
55    c.close()
56    db.commit()
57
58
59def insert_on_duplicate_key_update():
60    """
61    Update multiple rows at once with "INSERT .. ON DUPLICATE" statement
62    """
63    ids = random.sample(xrange(1, nrows_total + 1), nrows)
64    rating_values = [(id, 2) for id in ids]
65    c = db.cursor()
66    c.executemany('INSERT INTO `foo` (`id`, `rating`) VALUES (%s, %s) '
67                  'ON DUPLICATE KEY UPDATE `rating` = VALUES(`rating`)',
68                   rating_values)
69    c.close()
70    db.commit()
71
72
73def update():
74    """
75    Update multiple rows in transaction
76    """
77    ids = random.sample(xrange(1, nrows_total + 1), nrows)
78    c = db.cursor()
79    for id in ids:
80        c.execute('UPDATE `foo` SET `rating` = %s WHERE `id` = %s', [2, id])
81    c.close()
82    db.commit()
83
84
85def update_case():
86    """
87    Update multiple rows with case / when / then
88    """
89    ids = random.sample(xrange(1, nrows_total + 1), nrows)
90    values = [2 for _ in xrange(nrows)]
91    c = db.cursor()
92    when_statement = ' '.join('WHEN %s THEN %s' % args for args in zip(ids, values))
93    in_statement = ','.join(str(i) for i in ids)
94
95    c.execute('''UPDATE `foo` SET `rating` = CASE `id`
96                    %s ELSE `rating` END
97                 WHERE `id` IN (%s)''' % (when_statement, in_statement))
98    c.close()
99    db.commit()