Re: count resulting rows in sliced valuesqueryset

[Peter of the Norse]

I did some research on windowing functions since your post. They tend to be 
slower since they ignore optimizations. For example, when using count(*), 
indexes are used whenever possible to avoid hitting the table data and outer 
joins are ignored altogether.  And limited searches quit as soon as they reach 
the proper count.

One case where it might be faster to combine them is looking up the last page. 
For example:
count = qs.count()
qs = qs[ count-100 : count ]

When I run my slow query on that, I get 0.2 sec + 1.9 sec. YMMV. That’s why 
there’s https://docs.djangoproject.com/en/1.5/ref/models/querysets/#reverse 

On Nov 24, 2012, at 4:06 PM, ?manu* wrote:

> On Saturday, November 24, 2012 8:03:06 AM UTC+1, Peter of the Norse wrote:
> On Nov 21, 2012, at 3:53 AM, ?manu* wrote: 
> 
> > Suppose I have a queryset qs. For paginating purposes I need to do 
> > something like: 
> > 
> > count = qs.count() 
> > qs = qs[0:100] 
> > 
> > Unfortunately this executes the query twice, which I don't want. 
> 
> Are you sure? This is such a common pattern that I suspect that it’s not 
> slower than making it into one query. I ran some tests on the slowest query I 
> have, and the two statements were faster than trying to combine them. 0.2 + 
> 1.5 sec vs. 1.9 sec. 
> 
> You are right! (thanks also to Javier). It is not clear to me how it is 
> possible but effectively it seems that the two queries are not slower than a 
> single one...
> 
> Thank you also for the other answers.
> 
> E.
>  

Peter of the Norse
rahmc...@radio1190.org



-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: count resulting rows in sliced valuesqueryset

[?manu*]

On Saturday, November 24, 2012 8:03:06 AM UTC+1, Peter of the Norse wrote:

> On Nov 21, 2012, at 3:53 AM, ?manu* wrote: 
>
> > Suppose I have a queryset qs. For paginating purposes I need to do 
> something like: 
> > 
> > count = qs.count() 
> > qs = qs[0:100] 
> > 
> > Unfortunately this executes the query twice, which I don't want. 
>
> Are you sure? This is such a common pattern that I suspect that it’s not 
> slower than making it into one query. I ran some tests on the slowest query 
> I have, and the two statements were faster than trying to combine them. 0.2 
> + 1.5 sec vs. 1.9 sec. 
>

You are right! (thanks also to Javier). It is not clear to me how it is 
possible but effectively it seems that the two queries are not slower than 
a single one...

Thank you also for the other answers.

E.
 

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/django-users/-/zylmsQNJeDoJ.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: count resulting rows in sliced valuesqueryset

[Peter of the Norse]

On Nov 21, 2012, at 3:53 AM, ?manu* wrote:

> Suppose I have a queryset qs. For paginating purposes I need to do something 
> like:
> 
> count = qs.count()
> qs = qs[0:100]
> 
> Unfortunately this executes the query twice, which I don't want. 

Are you sure? This is such a common pattern that I suspect that it’s not slower 
than making it into one query. I ran some tests on the slowest query I have, 
and the two statements were faster than trying to combine them. 0.2 + 1.5 sec 
vs. 1.9 sec.

> I can use postgresql windowing function like that:
> 
> qs = q.extra(select = {'count': 'COUNT(*) OVER()'})
> 
> to get the 'count' attribute on every object. So far this is very nice. The 
> problem is that my queryset qs is actually a ValuesQueryset, i.e. a queryset 
> build with .values(...).annotate(...). In this case the extra select field is 
> ignored (as reported in the documentation). This makes sense because even in 
> postgres it seems not possible to mix window functions with "group by" 
> clauses. The solution with raw SQL is the use of a subquery. I can get the 
> correct results with the following query, where I enclose the sql produced by 
> django in a subquery:
> 
> sql = 'SELECT COUNT(*) OVER() , subquery.* FROM (%s) AS subquery' % qs.query
> 
> Now the questions:
> 
> 1. in this snippet qs.query is converted to string. The documentation says 
> that parameters in the resulting query string are not correctly escaped, so 
> maybe I should take care of SQL injection? How do I get correct escaping of 
> parameters?

You don't. The problem is qs.query is constructed for your convenience, it’s 
not what’s passed to the database. For a query like User.objects.fiter(id=5), 
the underling DB call looks like cursor.execute("SELECT * FROM user WHERE id = 
%s", (5,)). That is, the SQL has placeholders for the values, and the values 
are passed separately. Django doesn't actually escape the values, it just lets 
the DB backend do it. It’s much safer and a little faster to do it that way. 
Look at qs.query.sql_with_params()

> 2. Is it possible to re-attach the sql statement to my ValuesQuerySet? To 
> keep the rest of the code unchanged, I need to modify qs so that it executes 
> the new raw query. However I don't see how to construct a ValuesQueryset with 
> a raw SQL code...

sql, params = qs.query.sql_with_params()
s = ValuesQuerySet.objects.raw('SELECT COUNT(*) OVER() , subquery.* FROM 
('+sql+') AS subquery', params);
see https://docs.djangoproject.com/en/1.4/topics/db/sql/

Let me know if that works.

Peter of the Norse
rahmc...@radio1190.org



-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: count resulting rows in sliced valuesqueryset

[Javier Guerra Giraldez]

On Wed, Nov 21, 2012 at 5:53 AM, ?manu*  wrote:
> count = qs.count()
> qs = qs[0:100]
>
> Unfortunately this executes the query twice, which I don't want.

while clearly non-optimal, i would guess that Postgresql's caches
should be able to skip most of the processing.  Have you measured the
time impact of the qs.count() over the non-counting case?

--
Javier

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

count resulting rows in sliced valuesqueryset

[?manu*]

Suppose I have a queryset qs. For paginating purposes I need to do 
something like:

count = qs.count()
qs = qs[0:100]

Unfortunately this executes the query twice, which I don't want. 

I can use postgresql windowing function like that:

qs = q.extra(select = {'count': 'COUNT(*) OVER()'})

to get the 'count' attribute on every object. So far this is very nice. The 
problem is that my queryset qs is actually a ValuesQueryset, i.e. a 
queryset build with .values(...).annotate(...). In this case the extra 
select field is ignored (as reported in the documentation). This makes 
sense because even in postgres it seems not possible to mix window 
functions with "group by" clauses. The solution with raw SQL is the use of 
a subquery. I can get the correct results with the following query, where I 
enclose the sql produced by django in a subquery:

sql = 'SELECT COUNT(*) OVER() , subquery.* FROM (%s) AS subquery' % qs.query

Now the questions:

1. in this snippet qs.query is converted to string. The documentation says 
that parameters in the resulting query string are not correctly escaped, so 
maybe I should take care of SQL injection? How do I get correct escaping of 
parameters?

2. Is it possible to re-attach the sql statement to my ValuesQuerySet? To 
keep the rest of the code unchanged, I need to modify qs so that it 
executes the new raw query. However I don't see how to construct a 
ValuesQueryset with a raw SQL code...

E.

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/django-users/-/sqVpNtxw4-YJ.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.