Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 5486abc

Browse filesBrowse files
larkeelarkee
committed
chore: update benchmark to better compare performance
1 parent f767b35 commit 5486abc
Copy full SHA for 5486abc

File tree

Expand file treeCollapse file tree

2 files changed

+81
-28
lines changed
Filter options
Expand file treeCollapse file tree

2 files changed

+81
-28
lines changed

‎create_test_database.py

Copy file name to clipboardExpand all lines: create_test_database.py
+5-3Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -80,11 +80,13 @@ def create_test_instance():
8080
labels = {"python-spanner-sqlalchemy-systest": "true", "created": create_time}
8181

8282
instance = CLIENT.instance(instance_id, instance_config, labels=labels)
83-
84-
created_op = instance.create()
85-
created_op.result(1800) # block until completion
83+
if not instance.exists():
84+
created_op = instance.create()
85+
created_op.result(1800) # block until completion
8686

8787
database = instance.database("compliance-test")
88+
if database.exists():
89+
database.drop()
8890
created_op = database.create()
8991
created_op.result(1800)
9092

‎test/benchmark.py

Copy file name to clipboardExpand all lines: test/benchmark.py
+76-25Lines changed: 76 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -16,13 +16,16 @@
1616
A test suite to check Spanner dialect for SQLAlchemy performance
1717
in comparison with the original Spanner client.
1818
"""
19+
import base64
1920
import datetime
2021
import random
2122
from scipy.stats import sem
2223
import statistics
2324
import time
2425

2526
from google.api_core.exceptions import Aborted
27+
from google.api_core.exceptions import NotFound
28+
from google.cloud import spanner
2629
from google.cloud import spanner_dbapi
2730
from google.cloud.spanner_v1 import Client, KeySet
2831
from sqlalchemy import (
@@ -63,27 +66,43 @@ class BenchmarkTestBase:
6366
6467
Organizes testing data preparation and cleanup.
6568
"""
69+
_many_rows_ids = []
70+
_many_rows2_ids = []
6671

6772
def __init__(self):
73+
self._cleanup()
6874
self._create_table()
6975

70-
self._one_row = (
71-
1,
72-
"Pete",
73-
"Allison",
74-
datetime.datetime(1998, 10, 6).strftime("%Y-%m-%d"),
75-
b"123",
76-
)
76+
self._one_row = {
77+
"id": 1,
78+
"first_name": "Pete",
79+
"last_name": "Allison",
80+
"birth_date": datetime.date(1998, 10, 6),
81+
"picture": b"123",
82+
}
83+
self.keys = set([1])
84+
if not self._many_rows_ids:
85+
for i in range(99):
86+
self._many_rows_ids.append(self._generate_id())
87+
self._many_rows2_ids.append(self._generate_id())
88+
7789

7890
def _cleanup(self):
7991
"""Drop the test table."""
8092
conn = spanner_dbapi.connect(INSTANCE, DATABASE)
81-
conn.database.update_ddl(["DROP TABLE Singers"])
93+
try:
94+
conn.database.update_ddl(["DROP TABLE Singers"])
95+
except NotFound:
96+
pass
8297
conn.close()
8398

8499
def _create_table(self):
85100
"""Create a table for performace testing."""
86101
conn = spanner_dbapi.connect(INSTANCE, DATABASE)
102+
try:
103+
conn.database.update_ddl(["DROP TABLE Singers"])
104+
except NotFound:
105+
pass
87106
conn.database.update_ddl(
88107
[
89108
"""
@@ -96,10 +115,17 @@ def _create_table(self):
96115
) PRIMARY KEY (id)
97116
"""
98117
]
99-
).result(120)
118+
).result()
100119

101120
conn.close()
102121

122+
def _generate_id(self):
123+
num = 1
124+
while num in self.keys:
125+
num = round(random.random() * 1000000)
126+
self.keys.add(num)
127+
return num
128+
103129
def run(self):
104130
"""Execute every test case."""
105131
measures = {}
@@ -117,7 +143,7 @@ def run(self):
117143

118144

119145
class SpannerBenchmarkTest(BenchmarkTestBase):
120-
"""The original Spanner performace testing class."""
146+
"""The original Spanner performance testing class."""
121147

122148
def __init__(self):
123149
super().__init__()
@@ -127,12 +153,20 @@ def __init__(self):
127153

128154
self._many_rows = []
129155
self._many_rows2 = []
130-
birth_date = datetime.datetime(1998, 10, 6).strftime("%Y-%m-%d")
131-
for i in range(99):
132-
num = round(random.random() * 1000000)
133-
self._many_rows.append((num, "Pete", "Allison", birth_date, b"123"))
134-
num2 = round(random.random() * 1000000)
135-
self._many_rows2.append((num2, "Pete", "Allison", birth_date, b"123"))
156+
birth_date = datetime.date(1998, 10, 6)
157+
picture = base64.b64encode(u"123".encode())
158+
for num in self._many_rows_ids:
159+
self._many_rows.append(
160+
{
161+
"id": num,
162+
"first_name": "Pete",
163+
"last_name": "Allison",
164+
"birth_date": birth_date,
165+
"picture": picture,
166+
}
167+
)
168+
for num in self._many_rows2_ids:
169+
self._many_rows2.append((num, "Pete", "Allison", birth_date, picture))
136170

137171
# initiate a session
138172
with self._database.snapshot():
@@ -192,9 +226,8 @@ def __init__(self):
192226

193227
self._many_rows = []
194228
self._many_rows2 = []
195-
birth_date = datetime.datetime(1998, 10, 6).strftime("%Y-%m-%d")
196-
for i in range(99):
197-
num = round(random.random() * 1000000)
229+
birth_date = datetime.date(1998, 10, 6)
230+
for num in self._many_rows_ids:
198231
self._many_rows.append(
199232
{
200233
"id": num,
@@ -204,10 +237,10 @@ def __init__(self):
204237
"picture": b"123",
205238
}
206239
)
207-
num2 = round(random.random() * 1000000)
240+
for num in self._many_rows2_ids:
208241
self._many_rows2.append(
209242
{
210-
"id": num2,
243+
"id": num,
211244
"first_name": "Pete",
212245
"last_name": "Allison",
213246
"birth_date": birth_date,
@@ -255,8 +288,16 @@ def insert_one_row(transaction, one_row):
255288
Inserts a single row into a database and then fetches it back.
256289
"""
257290
transaction.execute_update(
258-
"INSERT Singers (id, first_name, last_name, birth_date, picture) "
259-
" VALUES {}".format(str(one_row))
291+
"INSERT INTO `Singers` (id, first_name, last_name, birth_date, picture)"
292+
" VALUES (@id, @first_name, @last_name, @birth_date, @picture)",
293+
params=one_row,
294+
param_types={
295+
"id": spanner.param_types.INT64,
296+
"first_name": spanner.param_types.STRING,
297+
"last_name": spanner.param_types.STRING,
298+
"birth_date": spanner.param_types.DATE,
299+
"picture": spanner.param_types.BYTES,
300+
}
260301
)
261302
last_name = transaction.execute_sql(
262303
"SELECT last_name FROM Singers WHERE id=1"
@@ -273,8 +314,18 @@ def insert_many_rows(transaction, many_rows):
273314
statements = []
274315
for row in many_rows:
275316
statements.append(
276-
"INSERT Singers (id, first_name, last_name, birth_date, picture) "
277-
" VALUES {}".format(str(row))
317+
(
318+
"INSERT INTO `Singers` (id, first_name, last_name, birth_date, picture) "
319+
"VALUES (@id, @first_name, @last_name, @birth_date, @picture)",
320+
row,
321+
{
322+
"id": spanner.param_types.INT64,
323+
"first_name": spanner.param_types.STRING,
324+
"last_name": spanner.param_types.STRING,
325+
"birth_date": spanner.param_types.DATE,
326+
"picture": spanner.param_types.BYTES,
327+
}
328+
)
278329
)
279330
_, count = transaction.batch_update(statements)
280331
if sum(count) != 99:

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.