We've begun implementing some virtual tables with latest SQLite build and run into a few questions performance-wise, all these questions revolve around the same issue (join performance).
1) Is there a way to know which fields are going to be potentially requested by xColumn? For instance if I have a virtual table with 10 columns, but in the query only 2 of them are referred, is there a way to know which these are? (without parsing the SQL independantly from SQLite) I'm asking because the virtual tables we've been considering would be aggregates, with potentially hundreds of virtual columns, and knowing which xColumn calls may happen after an xFilter would be quite helpful. 2) Since SQLite uses loops for joins, this can result in a lot of xFilter calls, and when the actual data is in an other DB server, this means a lot of actual queries, which are slow, even when xFilter queries for a specific primary key/index. To reduce this load, we've used temporary tables with good success (selecting from the virtual table into the temp table, then joining against the temp table). Would there be a way to use sub-selects to that purpose? (ie. without manually creating the temp table) 3) As soon as there are "or" or "in" terms in the SQL, xBestIndex isn't solicited, and xFilter requests everything unfiltered, the solution to that we found was to "union" the selects, which doesn't exactly improve readability. Is there a better solution? In our case, merely having a range constraint would already be enough to drastically improve the row count of xFilter (ie. "field in (40, 50, 70)" being presented as a "field>=40 and field<=70" constraint) Thanks, Eric _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
