perf(spanner): optimize query result decoding by olavloite · Pull Request #17375 · googleapis/google-cloud-python

olavloite · 2026-06-04T14:40:49Z

Work in progress.

Optimizes the decoding and reading of (large) result sets for Spanner.

gemini-code-assist

Code Review

This pull request optimizes performance across Spanner streaming and helper modules by caching list append methods, pre-compiling regular expressions, and replacing helper functions with direct attribute getters or lambdas. However, several critical issues were identified in _helpers.py: missing imports for datetime and base64 will cause runtime errors, BYTES values are incorrectly encoded to UTF-8 instead of being base64-decoded, and the new timestamp parsing logic ignores timezone offsets for non-UTC inputs, resulting in incorrect times.

olavloite · 2026-06-04T15:50:28Z

/gemini review

gemini-code-assist

Code Review

This pull request introduces several performance optimizations to the Spanner client library, such as caching local variables and methods during row merging, using lambdas and operator.attrgetter for type decoding, pre-compiling regex patterns, and optimizing timestamp parsing. The reviewer provided valuable feedback to further boost performance, including checking the more common number_value field first for float decoding, using string slicing instead of regular expressions for timestamp parsing, and removing the unused re import.

olavloite · 2026-06-04T16:30:01Z

The time needed to decode 100,000 rows is significantly reduced from approx 6-7s to approx 3-3.5s. The increasing gap between the two lines is due to the nature of this benchmark. The benchmark simulates a randomized poisson load. A small burst in traffic is enough to completely overload the old implementation, and cause requests to queue up and start taking an exponentially increasing time.

rahul2393

LGTM - with minor comments/questions and test assert fix

rahul2393 · 2026-06-04T18:26:09Z

+        if val[19] == ".":
+            if val.endswith("Z"):
+                offset = "Z"
+                fraction = val[20:-1]


Spanner Timestamp seems to be always UTC Z at the API/wire level. Non-Z offset only come from CAST(ts as String), which is a STRING column and routes through the STRING decoder.

+/-HH:MM handling plus the astimezone call won't ever be executed on real TIMESTAMP value? Can we drop it to shrink the risky code, or comment marking it deliberately defensive. The Z fast-path is the legitimate win and short-circuits before this anyway?

I agree that Spanner (currently) always sends timestamps in Zulu time. BUT: It is not strictly part of the API contract, meaning that future feature changes in Spanner could (in theory) trigger timestamps to be sent with an offset. Also, the previous implementation also supported offsets, so I included this in order not to cause any potential regressions from the previous implementation.

I added an additional test for parsing timestamps with an offset (in this case one without a sub-second fraction).

rahul2393 · 2026-06-04T18:27:49Z

+
+        parsed = self._callFUT(value_pb, field_type, field_name)
+        self.assertIsInstance(parsed, datetime_helpers.DatetimeWithNanoseconds)
+        self.assertEqual(parsed, expected)


DatetimeWithNanoseconds.eq is inherited from datetime and compares only to microsecond - it ignores .nanosecond. So this passes even if sub-us digits are wrong.

Oh, great catch, thanks! I updated the various checks in all the tests in this file, and also added a test that would otherwise have reported a false positive.

rahul2393 · 2026-06-04T18:29:24Z

+                    index = 0
+        else:
+            for value in values:
+                if value.HasField("null_value"):


value.HasField("null_value") duplicates _parse_nullables's semantics(still used in lazy/array/single-value paths). Should we add comment here to keep in sync so duplication doesn't silently drift?

Added a comment to explain both why it is there, and that it needs to be kept in sync.

rahul2393 · 2026-06-04T18:31:01Z

+        return operator.attrgetter("bool_value")
    elif type_code == TypeCode.INT64:
-        return _parse_int64
+        return lambda value_pb: int(value_pb.string_value)


Maybe a comment on why only STRING/BOOL use attrgetter(faster) while transform trypes use lambdas?

Added a comment to explain.

PR created by the Librarian CLI to initialize a release. Merging this PR will auto trigger a release. Librarian Version: v0.19.0 Language Image: us-central1-docker.pkg.dev/cloud-sdk-librarian-prod/images-prod/python-librarian-generator@sha256:234b9d1f2ddb057ed7ac6a38db0bf8163d839c65c6cf88ade52530cddebce59e <details><summary>gapic-generator: v1.35.0</summary> ## [v1.35.0](gapic-generator-v1.34.1...gapic-generator-v1.35.0) (2026-06-11) ### Features * setup.py matches prerelease versions (#17370) ([25b857e](25b857e1)) ### Bug Fixes * require protobuf 6.33.5 to address CVE-2026-0994 (#17349) ([6642263](66422636)) </details> <details><summary>google-auth: v2.54.0</summary> ## [v2.54.0](google-auth-v2.53.0...google-auth-v2.54.0) (2026-06-11) ### Features * implement regional access boundary support for standalone JWT and async service accounts (#17025) ([35af616](35af6168)) ### Bug Fixes * configure mTLS for impersonated credentials (#17404) ([57269d5](57269d56)) * fail-fast on missing ECP config file to avoid 30s hang (#17377) ([e096127](e0961270)) * Rename the 'seed' argument for setting an initial regional access boundary for clarity (#17186) ([e5c8cf9](e5c8cf92)) * update incorrect urls in setup.py to point at monorepo vs splitrepo (#17237) ([eaed04b](eaed04ba)) </details> <details><summary>google-cloud-alloydb: v0.11.0</summary> ## [v0.11.0](google-cloud-alloydb-v0.10.0...google-cloud-alloydb-v0.11.0) (2026-06-11) ### Features * update API sources and regenerate (#17413) ([59fe7cf](59fe7cf8)) </details> <details><summary>google-cloud-biglake: v0.5.0</summary> ## [v0.5.0](google-cloud-biglake-v0.4.0...google-cloud-biglake-v0.5.0) (2026-06-11) ### Features * update API sources and regenerate (#17431) ([2e75c78](2e75c78c)) </details> <details><summary>google-cloud-ces: v0.7.0</summary> ## [v0.7.0](google-cloud-ces-v0.6.0...google-cloud-ces-v0.7.0) (2026-06-11) ### Features * update API sources and regenerate (#17413) ([59fe7cf](59fe7cf8)) </details> <details><summary>google-cloud-confidentialcomputing: v0.11.0</summary> ## [v0.11.0](google-cloud-confidentialcomputing-v0.10.0...google-cloud-confidentialcomputing-v0.11.0) (2026-06-11) ### Features * update API sources and regenerate (#17413) ([59fe7cf](59fe7cf8)) </details> <details><summary>google-cloud-modelarmor: v0.7.0</summary> ## [v0.7.0](google-cloud-modelarmor-v0.6.0...google-cloud-modelarmor-v0.7.0) (2026-06-11) ### Features * update API sources and regenerate (#17413) ([59fe7cf](59fe7cf8)) </details> <details><summary>google-cloud-network-services: v0.10.0</summary> ## [v0.10.0](google-cloud-network-services-v0.9.0...google-cloud-network-services-v0.10.0) (2026-06-11) ### Features * update API sources and regenerate (#17431) ([2e75c78](2e75c78c)) </details> <details><summary>google-cloud-oracledatabase: v0.6.0</summary> ## [v0.6.0](google-cloud-oracledatabase-v0.5.0...google-cloud-oracledatabase-v0.6.0) (2026-06-11) ### Features * update API sources and regenerate (#17413) ([59fe7cf](59fe7cf8)) </details> <details><summary>google-cloud-spanner: v3.68.0</summary> ## [v3.68.0](google-cloud-spanner-v3.67.0...google-cloud-spanner-v3.68.0) (2026-06-11) ### Features * add asynchronous code snippets and minor cleanup changes (#17337) ([d6aaf61](d6aaf610)) ### Performance Improvements * optimize query result decoding (#17375) ([3f70b2f](3f70b2ff)) </details> <details><summary>google-cloud-storage: v3.12.0</summary> ## [v3.12.0](google-cloud-storage-v3.11.0...google-cloud-storage-v3.12.0) (2026-06-11) ### Features * full object checksum: implement rolling checksum and verification in reads resumption strategy (#17262) ([2361ba6](2361ba6e)) * Enable full object checksum PR 1/3 : parse finalize_time and server crc32c in async object stream (#17261) ([72c7a27](72c7a272)) * full object checksum: integrate full-object checksum in AsyncMultiRangeDownloader (#17263) ([b6a85e4](b6a85e49)) </details> <details><summary>google-developer-knowledge: v0.1.0</summary> ## [v0.1.0](google-developer-knowledge-v0.0.0...google-developer-knowledge-v0.1.0) (2026-06-11) ### Features * add google-developer-knowledge (#17417) ([ca02afc](ca02afce)) </details>

perf(spanner): optimize query result decoding

9d80835

gemini-code-assist Bot reviewed Jun 4, 2026

View reviewed changes

Comment thread packages/google-cloud-spanner/google/cloud/spanner_v1/_helpers.py Outdated

Comment thread packages/google-cloud-spanner/google/cloud/spanner_v1/_helpers.py

Comment thread packages/google-cloud-spanner/google/cloud/spanner_v1/_helpers.py Outdated

perf: further optimizations + timestamp fix

7820f5c

gemini-code-assist Bot reviewed Jun 4, 2026

View reviewed changes

Comment thread packages/google-cloud-spanner/google/cloud/spanner_v1/_helpers.py Outdated

Comment thread packages/google-cloud-spanner/google/cloud/spanner_v1/_helpers.py Outdated

Comment thread packages/google-cloud-spanner/google/cloud/spanner_v1/_helpers.py Outdated

chore: address review comments

10173de

olavloite marked this pull request as ready for review June 4, 2026 16:30

olavloite requested a review from a team as a code owner June 4, 2026 16:30

olavloite changed the title ~~perf(spanner): [WIP] optimize query result decoding~~ perf(spanner): optimize query result decoding Jun 4, 2026

rahul2393 approved these changes Jun 4, 2026

View reviewed changes

chore(spanner): address review comments

1c89fac

olavloite merged commit 3f70b2f into main Jun 5, 2026
32 checks passed

olavloite deleted the spanner-optimize-decoding branch June 5, 2026 08:52

sofisl mentioned this pull request Jun 11, 2026

chore: librarian release pull request: 20260611T192009Z #17432

Merged

Conversation

olavloite commented Jun 4, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

gemini-code-assist Bot left a comment

Choose a reason for hiding this comment

Code Review

Uh oh!

Uh oh!

Uh oh!

Uh oh!

olavloite commented Jun 4, 2026

Uh oh!

gemini-code-assist Bot left a comment

Choose a reason for hiding this comment

Code Review

Uh oh!

Uh oh!

Uh oh!

Uh oh!

olavloite commented Jun 4, 2026

Uh oh!

rahul2393 left a comment

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

olavloite commented Jun 4, 2026 •

edited

Loading