Hello,
Mr. Hipp please excuse my attitude on my first email (the one your
replied to). It came after 3 days of intense pressure to find out what
the problem of machines coming to a crawl whenever a particular query
with sufficiently large union-ed all tables was run.
Due to the quality of SQLite it literally was the last thing that i
looked at. I even had prepared emails to other people asking them to
look at their software, and to not to buffer on the hard disk so much.
Concerning your effort to fix it. I also suspected about the "path of
least resistance", considering "union all". This is why in my next
emails i mainly asked for a switch to change the "/var/tmp" path. The
other thing that i've asked for, was for the documentation to have a
warning about union all's behaviour. I believed that warning could save
a lot of time and effort for other people that tripped on the same thing
as me.
Thank you very very much for your fix. I'm glad that you put the
considerable effort to it. My Phd was in databases, so i can understand
how much effort this fix required. I have already downloaded the patch
and i'll test it asap.
Due to me also working/having worked on Open Source software (madIS,
Rigs of Rods), i realize your second point in my skin. Nevertheless
sometimes, i also slide to this kind of behaviour. I'll try to be on
guard against it in the future.
Best regards,
lefteris.
On 30/10/12 03:08, Richard Hipp wrote:
On Fri, Oct 26, 2012 at 2:16 PM, Eleytherios Stamatogiannakis
<est...@gmail.com <mailto:est...@gmail.com>> wrote:
I have been observing the following freaky behaviour of SQLite. When
i run:
select count(*) from (select * from huge_table union all select *
from huge_table);
Sqlite starts writting in /var/tmp/ a file like:
/var/tmp/etilqs___gblRd6vUPcx91Hl, the root partition of fills up
and an error is raised.
Why does SQLite 3.7.14.1 need to write at all when doing union all?
It seems to me that there is no reason for doing so.
The reason for using a temp table for UNION ALL in a subquery is because
that is the path of least resistance. The same identical code can
handle UNION ALL, UNION, INTERSECT, EXCEPT and various other cases.
Some queries (such as your UNION ALL) can in fact do without the temp
table. But those are special cases that have to coded separately.
Adding, testing, and maintaining that extra code involves a lot of
work. And the extra code risks introducing bugs that might appear even
for people who are not doing a UNION ALL in a subquery. And in over 12
years of use, in over a million different applications, with over 2
billion deployments, nobody has ever before requested this optimization.
At http://www.sqlite.org/src/info/7af3acbbd4 there is a patch that adds
the optimization to avoid using a temp table for your UNION ALL
queries. This one small patch represents about 12 hours of intense
work, so far. Much more work will be required to get the patch
performing to our release standards. All of this effort on your behalf
you are receiving for free. In return, we ask two things:
(1) Please download and test the patch and report any problems,
including performance problems.
(2) Please learn to be less grumpy, demanding, and condescending when
requesting help with software towards which you have contributed
nothing. You have received this latest patch, and indeed all of SQLite,
by grace. Therefore, please extend the same grace toward others.
Best regards,
lefteris.
_________________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org <mailto:sqlite-users@sqlite.org>
http://sqlite.org:8080/cgi-__bin/mailman/listinfo/sqlite-__users
<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
--
D. Richard Hipp
d...@sqlite.org <mailto:d...@sqlite.org>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
