Joins against virtual tables are handled in two steps:
1) your xBestIndex function is called (maybe more than once) with an array on
valid constraints the give the "shape" of the requested operation
Each "constraint" consists of a field number and and operation code; no
value is passed.
If no constraint is marked as useable, your xBestIndex function should
return the (approximate is sufficient) number of rows of the table as the cost
of doing a "full table scan"
If your implementation supports some form of indexed access AND the
constraints match a supported "index", then you need to set up the return
structure to indicate your internal index number, which constraints you can use
and their order
2) SQLite chooses a query plan and calls your xFilter function with the number
of the selected index and the values of the constraints in the order you
specified.
For your example, the expected calls are:
xBestIndex( t1, <none> ) -> return cost of full table scan on t1 and index
number 0;
xBestIndex( t2, <none> ) -> return cost of full table scan on t2 and index
number 0;
xBestIndex( t1, (name,"=") ) -> return cost of scan for index on t1(name) and
it's index number (e.g.1);
xBestIndex( t2, (name,"=") ) -> return cost of scan for index on t2(name) and
it's index number (e.g.1);
SQLite will then select the smallest product that contains at least one full
table scan (e.g. FTS t1 * index scan t2) and create a matching SQLite program
with the following call sequence:
xFilter( t1, 0 )
xEof( t1 )
xFilter( t2, 1, t1.name )
xEof( t2 )
... get fields, build result record, return curretn result row
xNext( t2 )
xNext( t1 )
The inner loop will be called once for every row in the outer loop. Whenever
xFilter is called, it needs to reset the VT's notion of "current row" to the
first row matching the contraint. If your "index access" guarantees that only
matching rows are returned, you can also set the "omit" flag on the constraint
to avoid SQLite re-checking the value and gain some more speed.
Gunter
-----Ursprüngliche Nachricht-----
Von: [email protected]
[mailto:[email protected]] Im Auftrag von Jain, Punit
Gesendet: Donnerstag, 14. Juli 2016 16:16
An: [email protected]
Betreff: [sqlite] Question on Join in Virtual Tables
Hi Folks,
We have implemented virtual tables that have around 12K rows. Running a simple
join operation on these tables takes a significant amount of time as by default
joins are of order of O(n^2).
E.g. a query such as "select * from t1 join t2 on t1.name=t2.name"
We tried to optimize it using xBestIndex and xFilter but to no avail. We
couldn't find a way to pass the value of t1.name (for each row) to the filter
of t2. Is there a way we could accomplish that? Or is there any other way to
optimize join operations?
Thanks in advance. We really appreciate your help!
Cheers,
Punit
_______________________________________________
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
