[thelist] multiple simultaneous db connections with classic ASP

[Matt Warden]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Canfield, Joel wrote:
>>>Is using multiple simultaneous db connections as simple as creating
>>>multiple connections sorta like this
>>
>>yeah, it may not be the most efficient method, but its what I do.
> 
> 
> Okay; helpful info. But then, what *might* be the most efficient method?
> Part of the reason for the recoding effort I'm making is that for the
> first time ever we're seeing performance issues on the tools I've built,
> and I fear it's my self-taught-inefficient methods, at least in part.

If you're essentially doing a "join" over multiple databases, they
really aren't different databases. There's often confusion about what
exactly a "database" is, because database management systems offer a
developer aid they call a database. In reality, this is really *only* an
aid to help the developer navigate to the correct database (the formal
definition of database, that is).

A database itself is simply a fully connected network of tables
(formally: relations). If table A, table B, and table C are in what a
DBMS calls a database, but only table A and table B are connected, with
table C connected to neither, then there are actually two databases there.

Likewise, if table A and table B are in DBMS-database 1 and table C is
in DBMS-database 2, but table C is connected to table A and/or table B,
then there is only one database.

This may sound like a tangent, but it really isn't. If you are doing a
join over multiple "databases", you only have one database. You should
then ask yourself: why are they in separate DBMS-databases? There are
reasons to do this, but 99.9% of the time they should be in the same
database (however, distributing a single database across multiple
servers is something different; just to head off potential criticisms to
my last statement).

So, I ask you: why are they in different DBMS-databases?

It pains me to even convey this information, but you might be able to
get away with syntax like this:

select foo, bar
from database1.table1 t1, database2.table2 t2
where t1.foobar = t2.barfoo

*if* the two databases reside on the same server and are managed by the
same DBMS.

Putting the database name in your SQL is a horrible idea; but, it's one
way you could improve performance.


- --
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDZrMmrI3LObhzHRMRAvWaAJ9UjfUpQJju+gda727o3wg/c2UFAwCdFMaD
dbJg0MOyjGesTfylykSNOr8=
=Wfxr
-----END PGP SIGNATURE-----