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:[email protected]] Im
Auftrag von Dimitris Bil
Gesendet: Montag, 24. Oktober 2016 15:10
An: SQLite mailing list <[email protected]>
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 <[email protected]> on behalf of
Hick Gunter <[email protected]>
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:[email protected]] Im
Auftrag von Dimitris Bil
Gesendet: Samstag, 22. Oktober 2016 15:31
An: [email protected]
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
[email protected]
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: [email protected]
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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
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: [email protected]
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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
