Hi Richard
On 9/9/2013 11:10 PM, Richard Hipp wrote:
SQLite does not normally *require* a lot of memory. (But it generally runs
faster the more memory you give it, so the default configuration is to use
as much as it wants.)
That is what I have always observed.
I'm guessing you have set "PRAGMA temp_store=MEMORY" which causes certain
temporary tables to be kept in memory rather than on disk.
I get this:
sqlite> pragma temp_store;
0
Does 0 equate to MEMORY? Maybe it does because PRAGMA temp_store=FILE
seems to set it to 1.
In the case of
your GROUP BY query, if there is no index on the GROUP BY terms, then
SQLite has to do a sort, and that sort will occur in memory and require
sufficient memory to hold the entire 1.5-million-row table.
The GROUP BY terms come from different tables, TI and WD. The join
field is day.
WHERE TI.day=WD.day
GROUP BY itemid, WD.wday
I just added indexes as best I saw:
* an index on TI(itemid) -- this was already there
* an index on TI(itemid, day) -- I just added this
* an index on WD(day, wday) -- I just added this
* an index on WD(wday) -- I just added this
[WD(day) is actually the primary key on that table]
It still doesn't solve it - still out of memory.
This is the query plan:
0|0|0|SCAN TABLE itemdata AS TI (~1000000 rows)
0|1|1|SEARCH TABLE weekdays AS WD USING INTEGER PRIMARY KEY (rowid=?)
(~1 rows)
0|0|0|USE TEMP B-TREE FOR GROUP BY
(I can see the TEMP B-TREE but just don't know what to do about it)
Can you try
setting "PRAGMA temp_store=FILE" and see if that doesn't solve your OOM
problem?
Unfortunately, the temp_store=FILE hasn't yet solved the problem! I
still get the same error.
Thanks... I have already learned something new today (temp_store).
Best Regards,
Mohit.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
