+1
I use SQlite3 on lower traffic production sites due to it's ease of install
and deployment. Even though I only once I reached this 1000 variable limit I
would like to see some django work around.
-----Original Message-----
From: akaariai
Sent: Monday, April 23, 2012 8:06 AM
To: Django users
Subject: Re: 'too many SQL variables' error with ModelMultipleChoiceField
On Apr 23, 10:48 am, Lukas Zilka <lu...@zilk4.eu> wrote:
Hey Russ,
thanks for your reply. I looked at the bulk insertion problem, and
that made me think that probably using the variables themselves is the
problem. It does not really make sense to me why anybody bothers to
use variables in a long SQL query for the IN clause, let alone a bulk
insertion? Does that benefit anything?
Anyway, I conducted an experiment and tried a simple SQL query with
lots of items in the IN clause to prove that its the variables
themselves, not the limitation of IN clause in SQLite, is whats
causing the problem. I have been successfully able to execute a query
as "SELECT * FROM projects WHERE id IN (...)" where on the place of
`...` there were 15,000 numbers. So SQLite has no problem with lot of
stuff as an argument of the IN operator. Now, the only limitation is
the length of the SQL query itself. According to the SQLite
documentation it is 1megabyte, and that suffices for 100k+ elements.
With that big query, you are right, it would be very impractical and
slow for the end users to interact with the application (sending 1M of
data to the webserver will probably be very unresponsive), so I think
this is a fair limitation that should never be exceeded for this use.
In Django code it seems that it would suffice to make a change in the
file 'db/models/sql/where.py'. Particularly, condition on the number
of elements of IN, and, if it is over let's say 100 of them, put them
into the SQL query directly as a string (e.g. '1,2,3') - not as
variables('?, ?, ?').
Though, for the future, I still believe there should be a better
solution than to rely on not reaching this limit. I would propose that
a temporary table should be created, filled up with the right
arguments of IN operator, and the query rewritten as a JOIN on the
original left argument of IN (or for simplicity, and perhaps worse
performance, a nested query) and this temporary table. That of course
only if the number of elements on the right side of IN is more than
some number. But this is for another discussion.
My question is therefore: Will the change in Django code that I
propose have any bad consequences or do you think it might actually
work satisfactorily?
First the "write the query as "1, 2, 3", not as "%s, %s, %s", (1, 2,
3). The problem is SQL injection. You could do that for integer
parameters easily, but on the whole it is not a nice way.
The create temp table + join seems hard. But you could do "exists"
query instead. A query like "select * from tbl where id in (a list)"
could be rewritten to "select * from tbl where exists (select 1 from
temp_table where temp_table.id = tbl.id)". This could be handled in
sql/where.py somewhat easily.
For me this issue isn't that important. I don't use SQLite except for
testing. If somebody wants to work on this issue, I must warn that it
is possible (if not likely) that some core developer will say "too
ugly" to this solution. I might be willing to accept the solution if
it was clean enough, as this would nicely abstract away this limit of
SQLite.
So, in short: this idea is definitely worth more investigation.
- Anssi
--
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.
Daniel Sokolowski
Web Engineer
KL Insight
http://klinsight.com/
Tel: 613-344-2116 | Fax: 613.634.7029
993 Princess Street, Suite 212
Kingston, ON K7L 1H3, Canada
--
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.
