GCP Cloud Spanner Read Performance Degradation When Using Large IN Clause in SQL Queries
Can someone help me understand Quick question that's been bugging me - I'm working through a tutorial and I'm confused about I've looked through the documentation and I'm still confused about I'm experiencing important performance degradation when executing SQL queries against Google Cloud Spanner that utilize a large `IN` clause... Specifically, I'm querying a table with millions of rows and trying to filter results based on a list of identifiers that can contain several hundred entries. Here's a simplified version of my query: ```sql SELECT * FROM Users WHERE UserID IN ('id1', 'id2', ..., 'idN'); ``` When `N` exceeds 100, the query execution time often spikes to over 10 seconds, which is unacceptable for my application. I've checked the Spanner logs and found no apparent issues, but the latency seems to increase linearly with the size of the `IN` clause. As a workaround, I've tried breaking down the query into smaller batches of 50 IDs each, which does improve performance, but it's cumbersome to implement and maintain. I've also considered using a temporary table to store the IDs and then joining that table in my main query, but Iām unsure if that would yield better performance or complicate my architecture unnecessarily. Additionally, I'm using the latest version of the Spanner client library for Node.js (v5.0.0) and have configured my instance with high availability settings. My Spanner database is in a multi-region configuration. Any insights or recommendations on optimizing the performance of these queries would be greatly appreciated! My development environment is Windows. This is part of a larger CLI tool I'm building. Thanks in advance! The stack includes Sql and several other technologies. The stack includes Sql and several other technologies.