ENQccnnz

1try:
2    from psycopg2cffi import compat
3    compat.register()
4except ImportError:
5    pass
6
7from pyutil.decorators import *
8import tempfile, psycopg2, psycopg2.extras
9
10def setup_test_table(conn):
11    with conn.cursor() as cur:
12        cur.execute("""
13            create table if not exists foobar (
14                some_long_column1 integer,
15                some_long_column2 integer,
16                some_long_column3 integer,
17                some_long_column4 integer,
18                some_long_column5 integer,
19                some_long_column6 integer,
20                some_long_column7 integer,
21                some_long_column8 integer
22            )
23        """)
24
25    with conn.cursor() as cur:
26        cur.execute("""
27            truncate table foobar
28        """)
29
30    conn.commit()
31
32    with tempfile.NamedTemporaryFile() as fp:
33        for start in xrange(10000):
34            fp.write('\t'.join([ str(x) for x in xrange(start, start+8) ] ))
35            fp.write("\n")
36        fp.seek(0)
37        with conn.cursor() as cur:
38            cur.copy_from(fp, 'foobar', columns = [
39                'some_long_column1',
40                'some_long_column2',
41                'some_long_column3',
42                'some_long_column4',
43                'some_long_column5',
44                'some_long_column6',
45                'some_long_column7',
46                'some_long_column8',
47            ])
48        conn.commit()
49
50@benchmark
51def execute(conn, sql, **bind_params):
52    """
53    Executes a SQL command against the connection with optional bind params.
54    """
55    with conn.cursor() as cur:
56        cur.execute(sql, bind_params)
57
58def iter_results(conn, sql, **bind_params):
59    """
60    Delays fetching the SQL results into memory until iteration
61    Keeps memory footprint low
62    """
63    with conn.cursor() as cur:
64        cur.execute(sql, bind_params)
65        for row in cur:
66            yield row
67
68@benchmark
69def fetch_iter_results(conn, sql, **bind_params):
70    return [ x for x in iter_results(conn, sql, **bind_params) ]
71
72@benchmark
73def fetch_results(conn, sql, **bind_params):
74    """
75    Immediatly fetches the SQL results into memory
76    Trades memory for the ability to immediately execute another query
77    """
78    with conn.cursor() as cur:
79        cur.execute(sql, bind_params)
80        return cur.fetchall()
81
82def test_conn(name, conn):
83    test_query = "select * from foobar"
84    for x in xrange(1000):
85        fetch_iter_results(conn_default, test_query)
86        fetch_results(conn_default, test_query)
87        conn.rollback()
88
89    print name
90    print BenchResults.format_stats()
91    BenchResults.clear()
92
93if __name__ == '__main__':
94    db_info = {
95        'host'     : 'localhost',
96        'port'     : 5432,
97        'user'     : 'pyutil',
98        'password' : 'pyutil',
99        'database' : 'pyutil_testdb',
100    }
101
102    conn_default = psycopg2.connect(**db_info)
103    conn_dict    = psycopg2.connect(cursor_factory = psycopg2.extras.DictCursor, **db_info)
104    conn_named   = psycopg2.connect(cursor_factory = psycopg2.extras.NamedTupleCursor, **db_info)
105
106    setup_test_table(conn_default)
107    test_conn('Default', conn_default)
108    test_conn('DictCursor', conn_dict)
109    test_conn('Named', conn_named)
110
111# 1k calls, cume duration
112# 10k rows fetched
113# +--------------------+----------------+--------------------+-------------------------+
114# |   Default Cursor   | psycopg2/2.7.3 | psycopg2cffi/2.7.3 | psycopg2cffi/pypy-2.0.2 |
115# +====================+================+====================+=========================+
116# | fetch_results      | 18.072         | 18.076             | 32.817                  |
117# +--------------------+----------------+--------------------+-------------------------+
118# | fetch_iter_results | 20.560         | 20.691             | 33.817                  |
119# +--------------------+----------------+--------------------+-------------------------+
120# 
121# +--------------------+----------------+--------------------+-------------------------+
122# |     DictCursor     | psycopg2/2.7.3 | psycopg2cffi/2.7.3 | psycopg2cffi/pypy-2.0.2 |
123# +====================+================+====================+=========================+
124# | fetch_results      | 18.405         | 18.377             | 32.434                  |
125# +--------------------+----------------+--------------------+-------------------------+
126# | fetch_iter_results | 19.563         | 19.674             | 33.265                  |
127# +--------------------+----------------+--------------------+-------------------------+
128# 
129# +--------------------+----------------+--------------------+-------------------------+
130# |  NamedTupleCursor  | psycopg2/2.7.3 | psycopg2cffi/2.7.3 | psycopg2cffi/pypy-2.0.2 |
131# +====================+================+====================+=========================+
132# | fetch_results      | 18.296         | 18.291             | 32.158                  |
133# +--------------------+----------------+--------------------+-------------------------+
134# | fetch_iter_results | 19.599         | 19.650             | 32.999                  |
135# +--------------------+----------------+--------------------+-------------------------+