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. -----Ursprüngliche Nachricht----- Von: Perrin, Lionel [mailto:[email protected]] Gesendet: Mittwoch, 18. Dezember 2013 14:58 An: [email protected] Betreff: [sqlite] virtual tables and group by: how could we prevent to retrieve all rows from a vtab and sort them later ? Hello, I plan to use sqlite to implement an 'aggregation tool'. Basically, the design would be the following: 1. I implement a virtual table CFL(contract, amount) which may provide up to 1 billion unsorted rows. 2. The aggregation phasis will be defined at run time and may consists in something as simple as 'select contract, sum(amount) from cfl group by contract'. 3. Since there will be only ~1000 different contracts, I expect SQLite to aggregate 'on the fly' the rows from CFL. 4. Unfortunately, as shown by the explain plan, it looks that SQLite first select all rows from CFL. When all rows are retrieved (which implies something like a 8Go temporary file), the rows are sorted and aggregated. [0, 0, 0, "SCAN TABLE CFL VIRTUAL TABLE INDEX 0: (~0 rows)"] [0, 0, 0, "USE TEMP B-TREE FOR GROUP BY"] Is there a way to force rows to be dispatched (using the group by) and aggregated on the fly instead of being stored, sorted and then aggregated? Note that I can't change the production order of the rows in the virtual table. Thanks for your help, Regards, Lionel ----------------------------------------- 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 fr ee 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 -------------------------------------------------------------------------- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: [email protected] This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
