-
Notifications
You must be signed in to change notification settings - Fork 39
/
c07_profiling.py
41 lines (33 loc) · 1 KB
/
c07_profiling.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
"""
Profiling with and without details
"""
import pyexasol
import _config as config
import json
import sys
import pprint
printer = pprint.PrettyPrinter(indent=4, width=140)
C = pyexasol.connect(dsn=config.dsn, user=config.user, password=config.password, schema=config.schema)
C.execute("ALTER SESSION SET QUERY_CACHE = 'OFF'")
C.execute("ALTER SESSION SET PROFILE = 'ON'")
# Normal profiling
stmt = C.execute("""
SELECT u.user_id, sum(p.gross_amt) AS total_gross_amt
FROM users u
LEFT JOIN payments p ON (u.user_id=p.user_id)
GROUP BY 1
ORDER BY 2 DESC NULLS LAST
LIMIT 10
""")
printer.pprint(stmt.fetchall())
json.dump(C.ext.explain_last(), sys.stdout, indent=4)
# Profiling with extra details per node (IPROC column)
C.execute("""
SELECT u.user_id, sum(p.gross_amt) AS total_gross_amt
FROM users u
LEFT JOIN payments p ON (u.user_id=p.user_id)
GROUP BY 1
ORDER BY 2 DESC NULLS LAST
LIMIT 10
""")
json.dump(C.ext.explain_last(details=True), sys.stdout, indent=4)