• Slow performance when joining three tables

    By Tommy Valand 1 decade ago

    In the testapp, none of the views are local to the db (two views in one db, one in another), but on the same server.

    In the , I created a view based on the select, but got the same performance as a regular query.

     

    I also tried creating indexes on the tables. But as they are virtual, the indexes need to be specified on creation of the tables (if I understand virtual tables correctly). 

    Any plans on implementing specification of indexes in the domsql specification? E.g.  

    • Create domino indexes

      By Philippe Riand 1 decade ago

      SQLite cannot create table indexes on virtual tables, as it does not control the data coming from these tables.

      On the other hand, it uses the existing index and our implementation use the index created within the NSF. These indexes (also named collations) are created when the columns are sorted. Then our driver takes advantages of them.

      In short, if you have a table A that you want to join to a column of table B, make sure that the column in B is sorted (primary sort order, or secondary).

      The driver has an option that you can set programmatically (see the demo db and the option checkboxes) that will report the missing indexes when executing a join.

      • Thanks

        By Tommy Valand 1 decade ago

        I'll take a look at it. Thanks for the tips :)