CODEWITHSUNDEEP
google-cloud-platformgoogle-cloud-spanner
foxygen
foxygen

Reputation: 1388

Cloud Spanner - read performance with large number of items in WHERE clause

I'm in the process of evaluating some different data stores for a project and I have a strange but inflexible requirement to check the existence of a 1500 keys per query... Basically the only query I'll be running is of the form:

SELECT user_id, name, gender
WHERE user_id in (user1, user2, ..., user1500)

I will have around 3.5 billion rows in the table. One data store that has caught my eye is Spanner. I was wondering if querying the data in this way would be feasible or if I would run into performance issues due to the large number of items in my WHERE clause. I have only been able to test these queries on a small amount of data so far so I'm leaning more on what the theoretical performance hit might look like instead having the luxury to just "try and found out".

Also, are there other data stores that might work better for this read pattern? I expected to run no more than 80 queries per second. Also, the data will be bulk loaded on a weekly basis. The data is structured by nature but we don't use it in a relational way (i.e. no joins).

Anyways, sorry if this question is vague in any way. I'm happy to provide more detail if needed.

Upvotes: 2

Views: 1240

Answers (1)

John Corwin
John Corwin

Reputation: 359

1500 keys should not be a problem if you use a bound array parameter to specify the keys:

SELECT user_id, name, gender
FROM table
WHERE user_id in UNNEST(@users)

https://cloud.google.com/spanner/docs/sql-best-practices#write_efficient_queries_for_range_key_lookup

Upvotes: 5

Related Questions