Thanks for your answers. As far as I understand, I won't be able to take advantage of sqlite to perform an 'in memory' aggregation. Actually, I did the same type of test with a 'normal' table and ended to the same conclusion. When no index is provided, all rows are retrieved and store in a temporary table, then sorted, and finally aggregated.
What do you think? Should I contribute to sqlite source code? I have the feeling that this need is not only relevant for virtual tables and might be required for large unsorted tables... -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Clemens Ladisch Sent: jeudi 19 décembre 2013 12:43 To: [email protected] Subject: Re: [sqlite] virtual tables and group by: how could we prevent to retrieve all rows from a vtab and sort them later ? Hick Gunter wrote: > You are in error on number 4. > > SQLite reads your CFL table once and performs the aggregation in a > temporary table that holds 1 entry per "contract" i.e. about > 1000 rows of max 16 byte records which I estimate to using less than > 64k. > > For each record read, SQLite will update or insert the matching row in > the temporary table. sqlite> CREATE VIRTUAL TABLE t USING fts3(x,y); EXPLAIN SELECT x, SUM(y) sqlite> FROM t GROUP BY x; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 1 SorterOpen 1 3 0 keyinfo(1,BINARY) 00 nColumn=3 ... 7 VOpen 0 0 0 vtab:82F090:7F0C37EF47A0 00 8 Noop 0 0 0 00 Begin Join Loop 0 9 Integer 0 10 0 00 r[10]=0 10 Integer 0 11 0 00 r[11]=0 11 VFilter 0 18 10 00 i0an=r[10] z0an='' 12 VColumn 0 0 12 00 r[12]=vcolumn(0); t.x 13 Sequence 1 13 0 00 r[13]=rowid 14 VColumn 0 1 14 00 r[14]=vcolumn(1); t.y 15 MakeRecord 12 3 15 00 r[15]=mkrec(r[12..14]) 16 SorterInsert 1 15 0 00 17 VNext 0 12 0 00 ... This copies all records into the temporary table without yet aggregating them. Regards, Clemens _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ----------------------------------------- Moody's monitors email communications through its networks for regulatory compliance purposes and to protect its clients, employees and business and where allowed to do so by applicable law. Parties communicating with Moody's consent to such monitoring by their use of the email communication. The information contained in this e-mail message, and any attachment thereto, is confidential and may not be disclosed without our express permission. If you are not the intended recipient or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that you have received this message in error and that any review, dissemination, distribution or copying of this message, or any attachment thereto, in whole or in part, is strictly prohibited. If you have received this message in error, please immediately notify us by telephone, fax or e-mail and delete the message and all of its attachments. Thank you. Every effort is made to keep our network free from viruses. You should, however, review this e-mail message, as well as any attachment thereto, for viruses. We take no responsibility and have no liability for any computer virus which may be transferred via this e-mail message. ----------------------------------------- _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
