Hick,
sorry, I think I misunderstood your first reply. Yes, of course I am calling each method twice when I use the wrapper: one time in the wrapper and one time in B, so an overhead is absolutely expected. But I would not expect this overhead to be equal to the time of the total execution without the wrapper, as the wrapper's methods pretty much do nothing more that calling the methods of table B that do the actual work. So I would expect the time to be double, if apart from calling the wrapper's methods I would also scan table A twice and searching B table double the number of the times I do in the normal execution. Best, Dimitris ________________________________ From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Hick Gunter <h...@scigames.at> Sent: Monday, October 24, 2016 1:59 PM To: 'SQLite mailing list' Subject: Re: [sqlite] Virtual table acting as wrapper of a regular table When you prepare() your statement, SQLite translates this into 2 nested loops: Outer loop: Rewind table A (retrieve first row) Extract id from table A row ... Retrieve next row from table A Inner loop: Set table B to A.id Extract id from B Check for match (return result row) Retrieve next row from table B When you are using your wrapper, the inner loop gets replaced by: Call xFilter() -> reset(), bind(), step(), column_value() .> Set table B to A.id, Extract id from table B row, check for match Call xEof() Call xColumn() -> return result value Call xNext() -> step(), column_value() -> Retrieve next row from table B This is about twice as much work. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dimitris Bil Gesendet: Montag, 24. Oktober 2016 15:10 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] Virtual table acting as wrapper of a regular table Indeed I am not checking argc. This code just only means to serve as a test in order to make comparisons for execution speed. I have stripped it of any other functionality. Regarding prepare, I am only preparing the statement in xConnect (which also acts as xCreate). This is only executed once, when connection to the virtual table is opened, right? Regarding the step method, I am executing a step the first time inside xFilter in order to advance the cursor in the first result. Then each time the xNext is called, I am executing step one more time to continue advancing the cursor. I think this is in accordance to what the sqlite page says: "If the virtual table contains one or more rows that match the search criteria, then the cursor must be left point at the first row. Subsequent calls to xEof must return false (zero). If there are no rows match, then the cursor must be left in a state that will cause the xEof to return true (non-zero). The SQLite engine will use the xColumn and xRowid methods to access that row content. The xNext method will be used to advance to the next row." Also, I am using sqlite3_result_int64 only one time per result tuple inside xColumn. In my case, as each result tuple is the same, this indeed could be avoided, but I don't think that this is the cause for the overhead. thanks again, Dimitris ________________________________ From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Hick Gunter <h...@scigames.at> Sent: Monday, October 24, 2016 10:39 AM To: 'SQLite mailing list' Subject: Re: [sqlite] Virtual table acting as wrapper of a regular table Your xFilter method is blindly assuming that there is always an argv[0] without checking argc first. You are incurring an extra "prepare" in your xConnect method, an extra "step" and "column" in your xFilter/xNext methods and an extra "result" in your xColumn function. Doing twice as much work taking twice as long seems quite reasonable. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dimitris Bil Gesendet: Samstag, 22. Oktober 2016 15:31 An: sqlite-users@mailinglists.sqlite.org Betreff: [sqlite] Virtual table acting as wrapper of a regular table Hello, I am trying to create a simple virtual table that acts as wrapper for a normal database table for a specific query. For example, consider that I have tables A and B in my database and the query: select count(A.id) from A CROSS JOIN B where A.id=B.id Now I am using a virtual table acting as wrapper for table B, so I can execute the query select count(A.id) from A CROSS JOIN w where A.id=w.id, where w is the virtual table instance, and get the same result. My problem is that the second query is about 2 times slower. I would normally expect some overhead, but this seems quite slow, so I was wondering if there is something wrong with my code. I am using a prepared statement in connect method for query "select id from B where id=?1" and reset/bind/step in filter method and (if there are more results) step in next method. Here's my code (I am just setting id=0 to denote eof) http://pastebin.com/ce8b4aLL Do you think there's something wrong or it's an unavoidable overhead that comes with the virtual table usage? Are there any chances to improve performance? thanks, Dimitris _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users