Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 7 of 7 total
Thread Collecting information from multiple servers
Wed, Feb 13 2013 8:37 PMPermanent Link

Adam H.

Hi guys,

I have a request from one of my clients that are running my application
at various sites. They want me to develop a second small application
which queries each site, and combines the results in a result set.

Each site is running a DBISAM Database Server (DBSRVR).

The query itself will be pretty basic. Something along the lines of:

select I.Invoice, C.CompanyName, A.Amount
From Site1\Invoices I
inner join Site1\Companies C on (C.ID = I.CompanyID)

union all

select I.Invoice, C.CompanyName, A.Amount
From Site2\Invoices I
inner join Site1\Companies C on (C.ID = I.CompanyID)

union all

select I.Invoice, C.CompanyName, A.Amount
From Site3\Invoices I
inner join Site1\Companies C on (C.ID = I.CompanyID)

.... etc


The only thing I'm not sure of is how to go about collecting this data
from various servers and collating it.

Having a separate session component and query for each site still won't
allow me to select into memory and then join later, because the memory
resultset will still be on each individual computer, and not my local
machine.

I could save the results as a CSV file, and then look at re-importing
the multiple CSV back into a local DBISAM table - but I'm not sure if
there's a better / more optimum way.

Does anyone have any suggestions / advise, or thoughts?

Cheers

Adam.
Wed, Feb 13 2013 10:59 PMPermanent Link

Raul

Team Elevate Team Elevate

Adam,

On 2/13/2013 8:37 PM, Adam H. wrote:
> The only thing I'm not sure of is how to go about collecting this data
> from various servers and collating it.

My 2 cents

Create a central database and have the 2nd app populate it from the
remote sites. You can do either (1) push where 2nd app runs on all sites
and pushes the data out to central DB or (2) pull model where the 2nd
app running centrally connects to remote sites and pull data into
central DB.

Unless your data is already unique across all sites (like GUID for
primary key or each site has a range) you might need to do some data
manipulation before adding data to central DB - add prefix or site id or
such to data being pulled or have each site be in separate tables

> Having a separate session component and query for each site still won't
> allow me to select into memory and then join later, because the memory
> resultset will still be on each individual computer, and not my local

I would not recommend doing remote queries anyways even if you could do
it - unless your datasets are really small the latency  will be problem.

If you have all data in central DB then the queries you listed become
quite easily doable.

Raul
Thu, Feb 14 2013 10:41 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Adam,

<< The query itself will be pretty basic. Something along the lines of: >>

As long as the query result sets aren't huge, you can use the
SaveToStream/LoadFromStream methods to save down each result set to a local
table, and then do the UNION ALL queries on them instead:

http://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=delphi&version=7&topic=Loading_Saving_Streams

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com
Thu, Feb 14 2013 4:22 PMPermanent Link

Adam H.

Hi Raul,

Thanks for your reply...

>> Having a separate session component and query for each site still won't
>> allow me to select into memory and then join later, because the memory
>> resultset will still be on each individual computer, and not my local
>
> I would not recommend doing remote queries anyways even if you could do
> it - unless your datasets are really small the latency  will be problem.

Actually - that's the reason I was planning on doing it. The data in
some of the tables is quite significant. (The total database at some of
these sites approaching 1GB in size now), however the result set that
I'm looking for from each site will be very small. I'm expecting
something with maybe a result set of 50 (at the most) records from each
site.

As such, I figured that it would be more effective to run the query
using C/S so all the work is done on the server that's hosting the data,
and only the result set is returned, but now I'm concerned after what
you've said...

The data effectively needs to be live, so I don't have the luxury of
copying the data across each night.

Cheers

Adam.
Thu, Feb 14 2013 4:27 PMPermanent Link

Adam H.

Hi Tim,

> As long as the query result sets aren't huge, you can use the
> SaveToStream/LoadFromStream methods to save down each result set to a
> local table, and then do the UNION ALL queries on them instead:
>
> http://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=delphi&version=7&topic=Loading_Saving_Streams

Thanks for that. Thankfully the results won't be too large so I shall
give this a go.

Cheers

Adam.
Thu, Mar 14 2013 10:46 PMPermanent Link

Adam H.

Just some feedback as to how I went with this.

I used Tim's suggestion of doing a query (as the resultset's weren't
huge), saving them to a memory stream, and then creating a separate
memory table (locally) for each site's data, and loading the stream in.

Once done, a query with union all statements consolidated the data
locally for me. Worked an absolute treat!

Fri, Mar 15 2013 3:31 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Adam,

<< Once done, a query with union all statements consolidated the data
locally for me. Worked an absolute treat! >>

Cool, I'm glad that it worked well for you. Smile

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com
Image