×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Large amount of database synchronize Large amount of database synchron
2

Large amount of database synchronize Large amount of database synchron

Large amount of database synchronize Large amount of database synchron

(OP)
If I have two tables that both contain same 1million rows of data, and if data from one has been changed (a little changes, some deletes, updates, and inserts). How would I do the synchronize between these two tables (using PHP)? any suggest algorithm?

RE: Large amount of database synchronize Large amount of database synchron

(OP)
Hmmm...Can the replication be done between different databases? e.g. MySQL and MSSQL? and the master database which I cannot control. For example, I am using MySQL as my backend, and I am importing data from more than one tables of another database (MSSQL), and merge everything into one table in my MySQL. (use select...join to get all data i need, and insert into one table in mysql). Now, the original data has been changed, and what is the best way to synchronize changes to mysql?

RE: Large amount of database synchronize Large amount of database synchron

is there a timestamp column or dateupdated column inthe SQL Server db? Those could be used to determine what records were changed.

or you could creatively use checksum and create a column storing that inthe mysql database (since I don't know if mysql has this or if it would compute the same value that SQLServer does) and then compare that value to the checksum of the orginal. Might get a bit complicated with denormalizing the data but it should be possible.

"NOTHING is more important in a database than integrity." ESquared
 

RE: Large amount of database synchronize Large amount of database synchron

Hi Jacky,

okay, if you don't have control of the master database other than read access it's hard to keep in sync without a full table scan for changes.

From MSSQL you can

CODE

Select GUID, Checksum(*) as CheckSum FROM TABLE

Where GUID represents a field which is the primary key of the table. Store the Checksum with the records in your mYSQL data and when you need to update you need to query all checksums again and update records, for which the checksum changed.

This still would be a million rows, just simpler rows with just the primary key field and a checksum field.

You're much better off, if there is some kind of LastUpdate field in the table you could query to be higher than the max value you have.

Bye, Olaf.

RE: Large amount of database synchronize Large amount of database synchron

(OP)
Thanks to SQLSister and OlafDoschke. I will try to use the checksum.  

RE: Large amount of database synchronize Large amount of database synchron

If you'd find a way to compute the checksum on chunks of records you could narrow down tha parts of the table that changed.

A totally different approach would be system data, transaction log and such things, but you'd need administrative rights to get at those things.

Bye, Olaf.

RE: Large amount of database synchronize Large amount of database synchron

I know in SQL Server you can do a checksum on just the fields you are interested in.

"NOTHING is more important in a database than integrity." ESquared
 

RE: Large amount of database synchronize Large amount of database synchron

Hi SQLSister,

Seems so. It would be nice if you could have the checksum on say groups of 256 records, to narrow down where changes have been made. then break down the chunks into smaller pieces until you're at the row level.

But that would mean processing the table even more often on the server side, it would just reduce the traffic.

Bye, Olaf.

RE: Large amount of database synchronize Large amount of database synchron

One more though, Jacky,

if you have read access to the MSSQL Server database, I suppose you either have a hoster that provides this server or a contact with the webmaster of that database.

If you pull out all checksums just to find out the lower than 1% changes in the table you're interested in, you're still causing more traffic on the server than if the DBA would simply replicate data of the table you're interested in into your database as the slave. It simply is by far the best method both in performance and costs to sync tables both for you and the database owner.

Bye, Olaf.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close