| Subject | Re: [IB-Architect] Proxy Query ? |
|---|---|
| Author | Marcelo Lopez Ruiz |
| Post date | 2001-03-07T03:02:54Z |
On mixing databases:
Any or all of what I'm saying here could be wrong, because MS doesn't give
away a lot of specifics on this. But the ideas are interesting.
This is how MS SQL 7 handles it. When a query is received, it is parsed and
compiled by a query processor engine, which talks to a separate layer (OLE
DB layer, an internal one in the case of MSSQL7 itself), and queries it for
information about metadata, available indexes, etc.
The query engine is responsible for executing the compiled instructions by
calling lower-level functions on an OLE DB interface. MSSQL7 uses, I
suspect, a lot of undocumented, highly-optimized internal interfaces to
report statistics.
This is how MS SQL 7 uses "linked servers", which allows the mixing of SQL
and other databases, such as Oracle or Jet (Access). Or just about anything
else, really. The point is that each OLE DB provider may implement a lot of
interfaces (which provide optimization hints, like the internal OLE DB
provider does) or just a few (enough to perform some rough operations on
plain text files, for example).
Through the use of well-designed interfaces, transaction contexts can also
be extended to encompass other databases.
I don't know how well IB separates query compilation, optimization and
execution, but I'm sure it will require major changes. However, look at the
bright side - if interfaces (structs of function pointes will do, thanks)
are used, the changes can be done in smaller increments - an index reporting
interface on this provider, an interface to help the optimizer on that one.
ting the local/remote logic to its own layer makes it a bit less
troublesome - the connected database is just a special case of "stuff that
exposeds interfaces/behaviour I can work with".
Having said all that, feel free to spank me into reality - the only time I
have ever looked at the code was on the security incident.
Marcelo Lopez Ruiz
Any or all of what I'm saying here could be wrong, because MS doesn't give
away a lot of specifics on this. But the ideas are interesting.
This is how MS SQL 7 handles it. When a query is received, it is parsed and
compiled by a query processor engine, which talks to a separate layer (OLE
DB layer, an internal one in the case of MSSQL7 itself), and queries it for
information about metadata, available indexes, etc.
The query engine is responsible for executing the compiled instructions by
calling lower-level functions on an OLE DB interface. MSSQL7 uses, I
suspect, a lot of undocumented, highly-optimized internal interfaces to
report statistics.
This is how MS SQL 7 uses "linked servers", which allows the mixing of SQL
and other databases, such as Oracle or Jet (Access). Or just about anything
else, really. The point is that each OLE DB provider may implement a lot of
interfaces (which provide optimization hints, like the internal OLE DB
provider does) or just a few (enough to perform some rough operations on
plain text files, for example).
Through the use of well-designed interfaces, transaction contexts can also
be extended to encompass other databases.
I don't know how well IB separates query compilation, optimization and
execution, but I'm sure it will require major changes. However, look at the
bright side - if interfaces (structs of function pointes will do, thanks)
are used, the changes can be done in smaller increments - an index reporting
interface on this provider, an interface to help the optimizer on that one.
> How do we handle the case of access to one database when the otherRollback the transaction, whine to the user. What else is there to do?
> is not available? What happens if one database is moved or renamed?
> Yes, aliases help here, but I'm so used to the idea that a databaseThe aliases are part of the database holding the reference metadata. Abstrac
> is a single entity that putting permanent links between databases
> really bothers me.
ting the local/remote logic to its own layer makes it a bit less
troublesome - the connected database is just a special case of "stuff that
exposeds interfaces/behaviour I can work with".
Having said all that, feel free to spank me into reality - the only time I
have ever looked at the code was on the security incident.
Marcelo Lopez Ruiz
----- Original Message -----
From: Ann W. Harrison <aharrison@...>
To: <IB-Architect@yahoogroups.com>
Sent: Tuesday, March 06, 2001 9:01 PM
Subject: Re: [IB-Architect] Proxy Query ?
> At 12:49 AM 3/7/2001 +0100, Darko Prenosil wrote:
>
> >I got only few more question :
> > How high on the Firebird to do list is this subject ? (Unfortunately
I'm
> >not programmer good enough to do it my self.).
>
> Cross database access in SQL is an important feature, but also a
> major effort. First, we'll need to agree on language extensions
> and direction. One alternative is to allow access to several
> databases but restrict any specific query to one database. Another
> is to allow databases to be mixed in a query, but keep each select
> expression restricted to one database. The best, and hardest, is
> to allow free reference to multiple databases.
>
> The hard part ... well, there are several ... but the hardest part
> is cross database optimization. We have a lot of work to do on
> the single database optimization - and the complaints there demonstrate
> how painful bad optimization is. For that reason, I would tend to
> make the first implementation follow the first or second model.
>
> Even if we go to the third model, cross-database constraints (and
> procedures, and triggers) is a problem that merits much discussion.
> How do we handle the case of access to one database when the other
> is not available? What happens if one database is moved or renamed?
> Yes, aliases help here, but I'm so used to the idea that a database
> is a single entity that putting permanent links between databases
> really bothers me.
>
> Regards,
>
> Ann
> www.ibphoenix.com
> We have answers.
>
>
> To unsubscribe from this group, send an email to:
> IB-Architect-unsubscribe@onelist.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>