I have one table on my users local machine and the same table on my server.  I am updating the information on the server table and need a way for my user to pull down only the new records into the local table.  I am currently pulling down my entire table into a memory table which is then used to insert the new data that is not currently there.  I am wondering if there is a way to connect to what is two different databases in two different locations in one statement so that I can do this with a single SQL statement and speed up the process significantly.  Or maybe a way for me to only select those records on my server that are not in the local table.  The thing that I believe is stopping me from doing this is that I have to use two unique sessions to connect to those two separate database locations.  Anyone have any ideas?

philb


Have you had a look at ElevateDB's replication facilities ie publishing?

If publishing isn't suitable then a few more questions:

1. what's the frequency
2. are you using f/s or c/s
3. is there only your site and one client or are there many clients
4. are you allowed to modify database structures

Roy Lambert [Team Elevate]

1. There will be updates to the table on the server about once a month.
2. client server
3. only our site and many clients that will pull the data changes
4. I can do anything to the tables if needed to make this as efficient as possible

Roy Lambert wrote:

philb

Have you had a look at ElevateDB's replication facilities ie publishing?

If publishing isn't suitable then a few more questions:

1. what's the frequency
2. are you using f/s or c/s
3. is there only your site and one client or are there many clients
4. are you allowed to modify database structures

Roy Lambert [Team Elevate]

I considered the replication stuff for this, but I am unsure how that would work with 1000s of clients pulling those changes.

philb wrote:

1. There will be updates to the table on the server about once a month.
2. client server
3. only our site and many clients that will pull the data changes
4. I can do anything to the tables if needed to make this as efficient as possible

Roy Lambert wrote:

philb

Have you had a look at ElevateDB's replication facilities ie publishing?

If publishing isn't suitable then a few more questions:

1. what's the frequency
2. are you using f/s or c/s
3. is there only your site and one client or are there many clients
4. are you allowed to modify database structures

Roy Lambert [Team Elevate]

philb

>I considered the replication stuff for this, but I am unsure how that would work with 1000s of clients pulling those changes.
>
>1. There will be updates to the table on the server about once a month.
>2. client server
>3. only our site and many clients that will pull the data changes
>4. I can do anything to the tables if needed to make this as efficient as possible

It should be better than the alternative. Publishing will create a file with just the changes in it so downloads should be easier with smaller file sizes. With thousands of clients you'll need a lot of bandwidth though.

There are a few people who've used the replication facilities so maybe they'll chip in.

Roy

Phil,

<< I considered the replication stuff for this, but I am unsure how that
would work with 1000s of clients pulling those changes. >>

The only real dilemma with thousands of clients is how to manage the update
files.  With small numbers of clients, you can set up separate stores for
each client to pull from, and just distribute the same update file to each
store by copying it.  And then the client can copy it down to a local store
and delete it from the remote store when it's done with it.

But, with larger numbers of clients, you'll want to use a single store on
the EDB Server, have the client keep track of the timestamp of the last
update file that it loaded, and then only try to copy to a local store and
load any update files that have a timestamp that is greater than the last
update file loaded.

You can interrogate the timestamps of update files in a store (remote or
local) via this system information table:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Updates_Table

What you want is the CreatedOn column.

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

Tim Young
Elevate Software
www.elevatesoft.com