On Wednesday 01 August 2007 17:37, Pavel Cisar wrote:

> Hi all,
>
> I think that external data sources should be internally handled as
> streams of rows that provide only sequential access and potentially at
> great expense (high cost value for optimizer). We need a clean metadata
> specification in our formats at Firebird side and flexibility at
> external side. The closest thing that match this specification we
> already have in Firebird are selectable (and normal) stored procedures,
> so why don't provide external data streams as selectable stored
> procedures that happen to access external database internally ? We will
> just define PSQL API to execute arbitrary statements on external data
> providers and bind expressions into input parameters and return values
> from row to local variables/output parameters (once we would have Java /
> whatever language SP's, they could use their own native access method
> like JDBC, ODBC, Python DBAPI etc. The hardest part would be to define
> how the transaction scope is handled, but it's inescapable anyway.
>
> This approach is simple, flexible, and doesn't require changes in
> optimizer / execution engine, neither complex DDL additions and
> enhancements.
>
> It has a drawback that it doesn't allow any smart and magical internal
> optimizations when the external data source happens to be the Firebird
> server or another database at the same server. But personally I see it
> more as advantage, as I think that any attempt to get smart and create
> such optimizations is either doomed to fail

Pavel, QLI does it :)

> or would bite us big time at
> the end (external data sources should be rather used as escape routes,
> not as basic building blocks).

This is what should be taken into an account.
On the other hand, imagine, please. One common database with very big data
(i.e. houses/streets/towns all over the world). And a lot of others are
linked to it. I want to:

select local_table.*, remote.adr.text
from local_table
join remote.adr on local_table.adr = remote.adr.id
where local_table.id between 100 and 110

It would be really sad to read ALL external table when I really need not more
than 11 records.