On Mon, Sep 9, 2013 at 11:01 AM, Mohit Sindhwani <m...@onghu.com> wrote:
> Hi Guys, > > I have hit a new problem and would like some advice if there's a way > around it. > > Error: near line 2: out of memory > > Basically, I have a table with 291 columns, 1.5 million rows. 288 columns > have numeric values, additionally there is an itenid and day. > I want to use SQL to basically do count(Vn), avg(Vn), sum(Vn) and then > group by itenid and daytype (daytype comes from a mapping of day--> daytype > from a different table). > > Statistics: > * Table has 291 columns > * There are about 50,000 unique itemid and the day runs from 1 - 31. > * The dump with count, avg, sum would have 288 x 3 + a couple of other > columns > * day --> daytype mapping results in 3 different daytypes > > So, we are doing averages, counts, sum on 288 columns and grouping the > data from 5 - 20 rows each to generate 1 statistics row (itemid, daytype). > These would eventually result into 50,000 x 3 results. Currently, I am > trying to do this a limit 1 and it's already giving me the out of memory > error. > > This is based on using sqlite3.exe downloaded from the sqlite3 shell > version 3.7.4. > > So, the questions are: > * Am I doing something wrong? > * What can I change? > * Is there a setting that I could change? > * Am I just asking SQLite for a calculation that naturally needs a lot of > memory and I just need to think of restructuring it in some way. > 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.) I'm guessing you have set "PRAGMA temp_store=MEMORY" which causes certain temporary tables to be kept in memory rather than on disk. 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. Can you try setting "PRAGMA temp_store=FILE" and see if that doesn't solve your OOM problem? -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
