The adapter generates inefficient SQL request

[dnesteryuk]

Hello team,

I am trying to play with your gem within Rom gems. It looks like this generator creates a request which receives all records from a table and then only filters results. I don't think it is efficient way:

SELECT "id", "name" FROM (SELECT DISTINCT "id" AS "id", "name" AS "name" FROM "users") AS "users" WHERE "name" = 'Snow'

This solution will have issues with big tables.

rom = ROM::Environment.setup(sqlite3: 'sqlite3://test.db')

rom.schema do
  base_relation :users do
    repository :sqlite3

    attribute :id,   Integer
    attribute :name, String
  end
end

class User
  attr_accessor :id, :name
end

rom.mapping do
  users do
    model User

    map :id, :name
  end
end

puts rom[:users].restrict(name: 'Snow').to_a

[snusnu]

@blambeau is https://github.com/dkubb/axiom-sql-generator/pull/2#issuecomment-9329476 related to this?

[blambeau]

Partly, yes. There are two main sources of inefficiencies to be tackled in relational algebra -> SQL compilation techniques IMO:

1. The need to flatten the SQL query to avoid SELECT ... FROM (SELECT ... FROM (SELECT ...)) resulting from a 1-1 mapping of the algebra to SQL operators (this issue). Most SQL DBMSs will end up with inefficient query plans for those queries.

2. Pure algebra projection never returns duplicates, so it is safe to output a DISTINCT everytime, but most SQL DBMSs are unable to detect when they are not needed and therefore end up with inefficient query plans. This is what my #2 comment is about, indeed: candidate key inference allows knowing when a projection preserves at least a key (DISTINCT not needed)

[blambeau]

About #7, see e.g. https://github.com/alf-tool/alf-sql/tree/v0.16.0/lib/alf/sql/processor, which contains typical SQL rewriting rules and heuristics for flattening queries in practice.

About #2, see e.g. https://github.com/alf-tool/alf-sql/blob/v0.16.0/lib/alf/sql/compiler.rb#L94-L97, for compilation of project and casing on key preservation

I think @dkubb has similar plans with his sql gem (work in progress).