Hi Richard, My first thought was similar to Brandon's, query all and and filter in memory. Perhaps you could optimize it a bit by looking at the number of providers and if it is 'relatively low' use the IN filter. This method would probably not be too bad for the extremes (a few or most providers), but not necessarily the best for the middle. You know you data, so if your data points are largely at the extremes maybe it is something to think about.
There are probably other solutions too, depending some details of the problem. Are providers grouped in some way? If so maybe you could make use of that fact to reduce the number of items in your filter list (and hence the number of queries run by the IN filter). Do you really want *all* products back? Or would the 'most popular' products (or some other grouping) from each provider be more valuable? If the most-popular would be valuable, maintain that list for each provider and structure it so that you can do a keys-only fetch by provider to get that information, then grab the list of popular product keys for each provider and do a batch get. Obviously the grouping here could be done in a wide variety of ways. You can periodically maintain the grouping lists via a cron-job or tasks. Robert On Sun, May 22, 2011 at 08:04, Richard Arrano <[email protected]> wrote: > I considered this but there are ~2000 records for each product > provider, so it wouldn't be feasible real time. I thought I had a > solution; I was thinking I could create a model with a > ReferenceProperty to the product data in each entity group, > replicating a few thousand records but they'd all just be keys. But I > would need to be able to filter on the reference, which I've > discovered is impossible. > > On May 21, 11:48 am, "Brandon Wirtz" <[email protected]> wrote: >> Consider getting all, and then adding them to an array and using IN Array >> rather than doing it in the query. >> >> I don't know that this will be faster, but it may be. >> >> -----Original Message----- >> From: [email protected] >> >> [mailto:[email protected]] On Behalf Of Richard Arrano >> Sent: Saturday, May 21, 2011 6:33 AM >> To: Google App Engine >> Subject: [google-appengine] how to avoid an IN query >> >> Hello, >> I'm working on a problem that at the moment seems to me to require an >> expensive IN query which I'd like to avoid. Basically, each group of >> users(and there may be thousands of such groups) draws product data from a >> subset of currently 8 providers(though it could reach ~16). The subset must >> contain at least 1, and can contain as many as the number of providers. Each >> user's inventory contains a reference property to the product and the >> product has a reference to its provider. What I'd like to do is to be able >> to create a view for each group of the products available given the >> providers they're drawing from(which can and will vary from user group to >> user group). So one way to do this is a query like: >> >> Product.all().filter('provider IN ', providers) >> >> Where providers is a list that represents the subset of providers that user >> group is drawing from. But this of course is quite slow. Given that the >> number of providers is relatively small, is there any way to change my >> approach or how to model the data in such a way that I can create this view >> in a speedy manner? >> >> Thanks, >> Richard >> >> -- >> You received this message because you are subscribed to the Google Groups >> "Google App Engine" group. >> To post to this group, send email to [email protected]. >> To unsubscribe from this group, send email to >> [email protected]. >> For more options, visit this group >> athttp://groups.google.com/group/google-appengine?hl=en. >> >> > > -- > You received this message because you are subscribed to the Google Groups > "Google App Engine" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/google-appengine?hl=en. > > -- You received this message because you are subscribed to the Google Groups "Google App Engine" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/google-appengine?hl=en.
