Considered doing it backwards? Product has Suppliers (Kmart,Best Buy, Radio Shack)
If you have a limited number of suppliers you would increase storage but it would be faster for this type of query. Because you would go Get_By_Name(Product) Which is a fast operation. -Brandon -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Robert Kluin Sent: Sunday, May 22, 2011 11:30 AM To: [email protected] Subject: Re: [google-appengine] Re: how to avoid an IN query 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. -- 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.
