[DUG] Use of ODBC drivers

Thu Jun 16 16:01:58 NZST 2005

Hi Mark,

I think it is the talking time between the two that makes it slower. By
slow I mean there is a several second delay, when the query natively
against either DB would run really fast. It also seems to be constant.
But it is hard tell for sure, because I am just going off gut feel, and
it could also be that one DB is considerably slower than the other
anyway.

We have other reasons (which are quite boring and completely
non-technical) why we do not write queries in our application that join
multiple DBs as well. So no-one ever tried to really do it.

Our DB is probably millions of records, but obviously some tables have
more records in them than others. I can't say if table size makes a
difference. It's been a while since I have needed to do one.

Come to think of it, there are a bunch of triggers that keep the two DBs
sychronised that must use this facility, so maybe it is really quite
fast, and the delay is in my head.

Samuel

-----Original Message-----
From: delphi-bounces at ns3.123.co.nz [mailto:delphi-bounces at ns3.123.co.nz]
On Behalf Of Mark Howard
Sent: Thursday, June 16 2005 3:07 p.m.
To: delphi at ns3.123.co.nz
Subject: Re: [DUG] Use of ODBC drivers

Hi Allan

Yes, I've just read about that facility on

http://www.microsoft.com/technet/prodtechnol/sql/70/maintain/hetdata.msp
x

Interesting that you found it slow.  Our app is really quite small with
a couple of hundred thousand records on one side of the join and less
than 100 records on the other.  How would this compare with your
situation?

Mark
On Thu, 16 Jun 2005 14:55:04 +1200, Allan, Samuel
<S.A.Allan at massey.ac.nz> wrote:

> SQL Server will let you do your cross-database platform join. However,
> it is quite a lot slower. I don't know how to set this up as our DBA
it.
> I understand he set up a reference to the other DB platform's ODBC
> connection in the SQL Server DB. You can then refer to this database
as
> you would to another SQL Server DB in your SQL.
>
> eg SQL.
>
> select
>    a.actual_volume,
>    p.planned_volume,
>    ...
> from
>    plan p,
>    ACTUALDB..OWNER.actual a
> where
>    a.id = p.id
>
> I have done SQL like this in Query Analyzer, but because it is so
slow,
> we have not done this in our applications.
>
> -----Original Message-----
> From: delphi-bounces at ns3.123.co.nz
[mailto:delphi-bounces at ns3.123.co.nz]
> On Behalf Of Mark Howard
> Sent: Thursday, June 16 2005 2:12 p.m.
> To: delphi at ns3.123.co.nz
> Subject: Re: [DUG] Use of ODBC drivers
>
>
> On Thu, 16 Jun 2005 13:41:04 +1200, Kyley Harris
<kyleyharris at gmail.com>
> wrote:
>
>> ODBC is just a generic way of communicating with a database using
SQL.
>> If you can provide a ODBC driver for your dbisam then they will be
>> able to access your data, but I don't believe you can do Cross joins
>> over databases using it. (I could be wrong) Generally, if they are
>> referring to your data they will store referential keys from your DB
>> in their DB. and run 2 DB connections from their middleware etc.
>
> So if you had :
> DBISAM table "Actual" with fields Forest and ActualVolume
> and
> MS-SQL table "Plan" with fields Forest and PlannedVolume
> you couldn't, for example do:
>
> SELECT A.ActualVolume, P.PlannedVolume
>  FROM Actual AS a
> JOIN Plan AS p
> ON a.Forest = p.Forest
> ?
>
> But presumably you could read the DBISAM data into a TEMP MS-SQL table
> and then do a join on it within MS-SQL?
>
> Not quite sure what you mean by your last sentence, above, Kyley.
>
> BTW there IS a supported DBISAM ODBC driver.
>
> Thanks for the reply.
>
> Mark
>
>
>
>> As long as it is readonly, and there is a viable DBISAM SQL driver
for
>> ODBC you should have no problems at all, as long as you provide them
>> the meta data.
>>
>> R.
>> Kyley
>>
>> On 6/16/05, Mark Howard <mhoward at pslog.co.nz> wrote:
>>> Hi All
>>>
>>> At one client site I have my application (written in D7) is
connected
> to a DBISAM database behind a DBISAM server.
>>> This site wants to develop an in-house (Net/MS-SQL) application
which
> accesses other MS-SQL tables as well as my DBISAM tables (read-only).
>>>
>>> Rather than convert my app to MS-SQL it seems that a low cost
> alternative would be for them to gain access to my data through an
ODBC
> driver.
>>>
>>> I have had no experience with ODBC and so I would be very interested
> in hearing the experiences of others who do have direct experience in
> reading data and presumably joining tables between MS-SQL databases
and
> other databases accessed through ODBC (not necessarily DBISAM).
>>>
>>> What IS possible?  What is not?
>>> Is it robust?
>>> Are there any gotchas?
>>>
>>> Are there any good resources that I can read up on?
>>>
>>> Thanks
>>>
>>> Mark
>>>
>>>
>>> --
>>> Forest Production Systems Ltd
>>> Creators of PSLog - A harvesting information system
>>> www.pslog.co.nz
>>>
>>>
>>> --
>>> No virus found in this outgoing message.
>>> Checked by AVG Anti-Virus.
>>> Version: 7.0.323 / Virus Database: 267.7.4/16 - Release Date:
> 15/06/2005
>>>
>>> _______________________________________________
>>> Delphi mailing list
>>> Delphi at ns3.123.co.nz
>>> http://ns3.123.co.nz/mailman/listinfo/delphi
>>>
>>
>>
>
>
>

-- 
Forest Production Systems Ltd
Creators of PSLog - A harvesting information system
www.pslog.co.nz

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.7.4/16 - Release Date: 15/06/2005

_______________________________________________
Delphi mailing list
Delphi at ns3.123.co.nz
http://ns3.123.co.nz/mailman/listinfo/delphi