Google-cloud-php: [Spanner] Supporting "executeSql" API, not just "executeStreamingSql"?

[castaneai]

Currently, it seems that only "ExecuteStreamingSql" API is supported in google-cloud-php.
https://github.com/GoogleCloudPlatform/google-cloud-php/blob/master/Spanner/src/Operation.php#L189

But, "ExecuteSql" (not streaming) API exists on Cloud Spanner API documentation.
https://cloud.google.com/spanner/docs/reference/rpc/#googlespannerv1spanner

In the case of repeatedly reading small data, we think that we can expect reduction of latency.
What do you think?

[jdpedrie]

Hi @castaneai,

I'll look into this and get back to you soon.

[jdpedrie]

Hi @castaneai,

I ran some tests to see whether the performance varied between the two execute methods.

My test was, using a single-use transaction, to repeatedly execute a SELECT query returning a single row from a table with several thousand rows. While this may not be an extremely realistic scenario and the results are dependent on the schema and environment I'm testing in, it should suffice to compare relative performance of streaming and non-streaming.

for ($i = 0; $i < 100; $i++) {
    // `iterator_to_array` to be sure the entire result is consumed and processed.
    iterator_to_array($db->execute('SELECT * FROM Writes LIMIT 1'));
}

for ($i = 0; $i < 100; $i++) {
    // Added this method for testing purposes.
    $db->executeNonStreaming('SELECT * FROM Writes LIMIT 1');
}

I ran both tests three times; see the results below.

Both methods had almost identical latency:

| Streaming      | Non-streaming |
----------------------------------
| 1877 ms        | 1934 ms       |
| 2129 ms        | 2125 ms       |
| 2126 ms        | 2068 ms       |
----------------------------------

I've sent these results to some other members of the team for their thoughts as well.

[castaneai]

@jdpedrie Thank you for your detailed results!
I understood that there is little difference between the two.

[jdpedrie]

Great! Please let us know if there's anything else. I'm going to close this, but if you've still got questions, or discover any other relevant information which could be relevant, please feel free to reopen.