I’m not sure I explained myself that well. Our system has two models, Product
and ProductSearch. The Product tables come from third party sources, and can’t
be edited from the front end. Users can setup their own ProductSearches, which
are then used to construct the queryset. So our code actually looks more like:
model = Product.get_subtable(‘foo’)
logger.info(’start first run of get_queryset’)
qs = user_product_search.get_queryset(request, model)
logger.info(‘finished first run of get_queryset’)
logger.info(’starting count() of qs for the first time’)
product_count = qs.count()
logger.info(‘finish count() of qs’)
if product_count == 0:
model = Product.get_subtable(‘foo’)
logger.info(’start second run of get_queryset’)
qs = user_product_search.get_queryset(request, model)
logger.info(‘finished second run of get_queryset’)
In the worst case scenario, the actual count() runs in about 150-250ms, (we
have some good indexes) the first get_queryset call takes ~800ms (there are DB
look-ups) and the second can take 200ms (we cache the DB values). This is
200ms slower than the old way of “c = qs.filter(source=‘foo’).count(); if c>0:
qs = qs.filter(source=‘foo’) else: qs = qs.filter(source=‘bar’)”. And about
3000ms faster than building up the raw SQL that we used to do. (Actually, the
problem comes because we use the queryset in a dozen different ways depending
on other things that need to be looked up. Seriously, the ORM is faster than
trying to roll our own.)
> On Mar 18, 2015, at 11:04 PM, James Schneider <[email protected]> wrote:
>
> Wow, that's quite the queryset!
>
> If I understand your snippet right, it looks as though you always run an
> initial large query against one of the tables, and if it doesn't find
> anything, run the similar query against the other table. If the filters, etc.
> are exactly the same, you could take the raw SQL from the first query and
> perform a substitution for the new model name, and just run the raw SQL for
> the second iteration, rather than depending on the ORM to (re)build the query
> for you. That may not be an option if you are heavily depending on the ORM to
> package up the results nicely for you. Given the complexity of your query,
> raw SQL may buy you some serious performance gains if you are able to
> optimize the query that the ORM would normally generate.
>
> This may be one of those cases where the limitations of the ORM are being
> reached, however, I'll defer any further comment to others on the list.
>
> -James
>
>
> On Wed, Mar 18, 2015 at 9:35 PM, Peter of the Norse <[email protected]
> <mailto:[email protected]>> wrote:
> I’m already doing all of that. The problem is we often have logic that goes
> like:
>
> model = Product.get_subtable(‘foo’)
> queryset = model.objects.
> filter(…).
> select_related(…).
> exclude(…).
> extra(…)
> … and so on and so on. Seriously, the method that constructs the queryset
> is some 1500 lines long and can quite a bit to run
> if queryset.count() == 0:
> model = Product.get_subtable(‘bar’)
> queryset = model.objects.
> filter(…).
> select_related(…).
> exclude(…).
> extra(…)
> … the same expensive method again.
>
> A lot of our performance gains have come from caching the queryset creation
> function, but even with all of that the method can still take 200ms to run.
> (Seriously, there can be over 40 filters, some of which are complex Q() and
> F() objects.) The problem is the second queryset produces almost the exact
> same SQL, but with one table having a different name. Back when we were
> creating the raw SQL, it was a simple string replace to switch tables. I was
> hoping that someone had some kind of “switch table” command on the queryset
> or something similar.
>
>> On Mar 17, 2015, at 3:08 AM, James Schneider <[email protected]
>> <mailto:[email protected]>> wrote:
>>
>> That sounds more like an exact use case for model inheritance. You would
>> define a single model that matches the columns in both tables. You can
>> define that model as abstract, then have the two real models (one for each
>> table) inherit from that abstract model. In the definition for the child
>> models, you would specify the DB table names to match your two tables in the
>> existing schema.
>>
>> https://docs.djangoproject.com/en/1.7/ref/models/options/#db-table
>> <https://docs.djangoproject.com/en/1.7/ref/models/options/#db-table>
>> That way, you only maintain a single (abstract) model definition, even
>> though multiple models use it (each with a separate DB table).
>>
>> You may also need to mark the real models as unmanaged by the ORM if you're
>> doing funny things directly in the DB, and you will be managing the table
>> schema directly for those tables.
>>
>> https://docs.djangoproject.com/en/1.7/ref/models/options/#managed
>> <https://docs.djangoproject.com/en/1.7/ref/models/options/#managed>
>> I'm assuming you have already determined the logic deciding which model each
>> user uses. At that point, assuming that all other functionality remains the
>> same (calls/processing the data, regardless of table), the calls to those
>> models can be generalized by a simple utility function that returns a class
>> instance of the necessary model.
>>
>> def get_right_model(user):
>> # logic to pick ModelA or ModelB based on user
>> return ModelA
>>
>> Then later, maybe in a view:
>>
>> selected_model = get_right_model(self.request.user)
>>
>> all_data = selected_model.objects.all()
>>
>> #all_data should now contain the results of the query against the right
>> model/table in the DB
>>
>> Trying to flip between tables in the ORM by tweaking the innards probably is
>> going to lead to excessive complication.
>>
>> -James
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Django users" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected]
>> <mailto:[email protected]>.
>> To post to this group, send email to [email protected]
>> <mailto:[email protected]>.
>> Visit this group at http://groups.google.com/group/django-users
>> <http://groups.google.com/group/django-users>.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/django-users/CA%2Be%2BciXKkQ2Lpq1F-KBpHkndLWxi_8dCVamXJ-G7Bji3QZWcmA%40mail.gmail.com
>>
>> <https://groups.google.com/d/msgid/django-users/CA%2Be%2BciXKkQ2Lpq1F-KBpHkndLWxi_8dCVamXJ-G7Bji3QZWcmA%40mail.gmail.com?utm_medium=email&utm_source=footer>.
>> For more options, visit https://groups.google.com/d/optout
>> <https://groups.google.com/d/optout>.
>
> Peter of the Norse
> [email protected] <mailto:[email protected]>
>
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected]
> <mailto:[email protected]>.
> To post to this group, send email to [email protected]
> <mailto:[email protected]>.
> Visit this group at http://groups.google.com/group/django-users
> <http://groups.google.com/group/django-users>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/0435A65C-4A7A-4DFA-A1C2-8951437FFB2B%40Radio1190.org
>
> <https://groups.google.com/d/msgid/django-users/0435A65C-4A7A-4DFA-A1C2-8951437FFB2B%40Radio1190.org?utm_medium=email&utm_source=footer>.
>
> For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected]
> <mailto:[email protected]>.
> To post to this group, send email to [email protected]
> <mailto:[email protected]>.
> Visit this group at http://groups.google.com/group/django-users
> <http://groups.google.com/group/django-users>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/CA%2Be%2BciUcS608B7QPza2adH7KQ5pBjozzMRnW8Am8yY7SSN_UqA%40mail.gmail.com
>
> <https://groups.google.com/d/msgid/django-users/CA%2Be%2BciUcS608B7QPza2adH7KQ5pBjozzMRnW8Am8yY7SSN_UqA%40mail.gmail.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
Peter of the Norse
[email protected]
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/8324B201-5761-4976-8B39-4046BA8B356E%40Radio1190.org.
For more options, visit https://groups.google.com/d/optout.
