Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM Client/Server » View Thread |
Messages 1 to 7 of 7 total |
Collecting information from multiple servers |
Wed, Feb 13 2013 8:37 PM | Permanent 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 PM | Permanent Link |
Raul 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 PM | Permanent 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 PM | Permanent 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 PM | Permanent 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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. Thanks, Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Wednesday, April 24, 2024 at 11:07 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |