I'm developing an application with GAE + Google Datastore / flask / python / bootstrap and need some help with figuring out (from the pricing perspective) how to query and filter data in this app.
I need to provide the app with the ability to search the data in the Google Datastore tables. For example, show only the products or orders in which "xyz" is mentioned; these tables can grow up to 10K entities each. The text "xyz" could be in any of the table columns. The user might also want to sort the table on any of the columns.
The documentation and tutorials are full of Datastore queries with a limit of 10 entities "in order to save costs". So, when showing e.g. the orders table, by default I could show only the 10 most recent orders. Only if the user needs to see more orders, then I can go and query them. And I could also only present only the first 10 products ordered by product id and query more if needed. This would keep costs low but would make coding complex because to search all the orders or products where "xyz" is mentioned I would need to iterate through queries of 10 (maybe more) entities per time and apply a filter through all their properties. And doing all those queries one by one could decrease response speed.
On the other hand, I could load the whole orders table each time in one go, and display it with bootstrap + jquery. With minimal coding effort, I would present a table where the user can search in all fields, would have tabs, can sort it easily etc.
From the esthetic, user experience, and coding perspectives, I would prefer to retrieve the whole table each time and present it with bootstrap + jquery but am afraid of mounting costs: if say, the 10K orders table is opened 100 times a day, I would be retrieving 1 million entities daily. That would be some $150 a month, which I would need to charge my potential customers.
So, what is best practice in this regard?
Thanks in advance!
You have 2 issues to deal with: (1) pricing and (2) user experience (it takes a significant amount of time to retrieve 10,000 entities from datastore).
Best practice is to get only what you need to show on the screen (e.g., 10-100 entities at a time). When the user does something, then do another query based on what the user wants. For example, if the user hits a "next" button then you get the next set of entities or if the user sorts by column, then you get entities sorted by that value.
This way, your costs will be lower and the user should see results in 1 second instead of 30 seconds (that is a very rough estimate).