Re: Cross-Database query engine?

On Tue, 2007-07-24 at 22:44 -0500, David Nicol wrote:

I'm no database expert but I believe I can answer your question.

On 7/24/07, Daniel Kasak <dkasak@xxxxxxxxxxxxxxxxxxxx> wrote:

Link some tables from one database server.
Link some tables from another database server.

Go to the query builder. Add tables from both database servers. Join
tables where appropriate. Execute query.

So you're saying that Access abstracts the handles to the multiple databases
in such a way that they appear to you as a single database, and you can use the
tools the way you would use on a single database on the combination of the
multiple databases.

That's it exactly. What's more, if you look at MySQL's query log as you
run one of these cross-database queries, you quickly get an idea of how
it works :)

I do not recall seeing such a tool discussed on this mailing list. If
I were tasked
with such a situation I would unfold the various queries, possibly using one of
the perly abstractions such as Tie::DBI or DBIx::SimplePerl and using keys or
each to iterate. At some level, Access must be doing that for you.

I'll look into these, thanks.

The closest thing a cursory CPAN search revealed was
http://search.cpan.org/~dwright/DBD-Multi-0.10/lib/DBD/Multi.pm
which appears to be concerned with load-balancing against data
sources containing identical data rather than abstracting multiple data sources
in a way that the database driver takes apart the queries and sends the various
pieces to the various databases.

That also sounds interesting.

Without knowing for certain that nobody has done what you are looking
for already,
it sounds to me like it would be a welcome addition to the DBI tool kit.

Go for it. I expect that the devil will be in the optimization.

Yes I already have some ideas here too.

Firstly I'll be doing some testing with using SQLite as a local storage
engine. I'll pull the query apart, grab bits of stuff from various DB
servers, and then do the final select against the SQLite DB.

Secondly, from some very limited testing, it looks like I might get a
decent performance boost by creating temporary tables on the DB servers,
inserting a list of keys, and then running queries against these tmp
tables ( as opposed to just sending a huge 'where' clause with all the
keys ).

Thirdly, I'm also investigating using a separate thread to do all
querying, which will make my apps feel much more responsive :)

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@xxxxxxxxxxxxxxxxxxxx
website: http://www.nusconsulting.com.au

.

Relevant Pages

  • Re: Query In BE database
    ... that would give the users "live" access to the underlying queries. ... When selected from the drop-down list box, I what the the query that is ... What Is A Remote Query? ... point your local query to a query that was in another Access database. ...
    (microsoft.public.access.queries)
  • Re: merge multiple databases
    ... You need to create a new Access datafile (mdb) to store all in. ... Open the newly created Access database. ... Click on the Queries icon. ... "Create new query", then select lnkDrive as the table to ...
    (microsoft.public.vb.database.dao)
  • Re: merge multiple databases
    ... You need to create a new Access datafile (mdb) to store all in. ... Open the newly created Access database. ... Click on the Queries icon. ... "Create new query", then select lnkDrive as the table to ...
    (microsoft.public.vb.general.discussion)
  • Re: merge multiple databases
    ... You need to create a new Access datafile (mdb) to store all in. ... Open the newly created Access database. ... Click on the Queries icon. ... "Create new query", then select lnkDrive as the table to ...
    (microsoft.public.access.queries)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)