Re: Optimize db update
Thanks for your advice. I found another way of doing it. from DatabaseB: Select * into outfile 'data.txt' from TableB then on DatabaseA: Load data infile 'data.txt into TableB then on DatabaseA, I can run any validation on TableB before inserting it in TableA. Velen - Original Message - From: Daniel Brown [EMAIL PROTECTED] To: Velen [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, March 21, 2008 6:10 PM Subject: Re: Optimize db update On Fri, Mar 21, 2008 at 10:04 AM, Velen [EMAIL PROTECTED] wrote: This one is alright but is there an alternative that can be run with the mysql prompt? Not to dump a file, but to import, yes. Check out the LOAD DATA INFILE command: http://dev.mysql.com/doc/refman/5.0/en/load-data.html -- /Daniel P. Brown Forensic Services, Senior Unix Engineer 1+ (570-) 362-0283 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimize db update
This does not solve my problem. DatabaseA and DatabaseB are on 2 seperate
pc and the only communication that can be use between the two PC is a USB
pendrive.
Is there another way of copying a table from one Database to another?
I don't want to update TableA directly as I want to validate the data before
updating so I'll be using a temp table in DatabaseA which will contain the
data from TableB.
Please advise.
Thanks.
Velen
- Original Message -
From: Daniel Brown [EMAIL PROTECTED]
To: Velen [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Thursday, March 20, 2008 10:20 PM
Subject: Re: Optimize db update
On Thu, Mar 20, 2008 at 1:41 PM, Velen [EMAIL PROTECTED] wrote:
Actually I am updating TableA in DatabaseA with values from TableB in
DatabaseB. Database B is on a stand alone PC. I'm using VB6 to create a
.txt file containing data from TableB then using VB6 once more to
recronstruct the table in DatabaseA then remove all data which are already
in TableA and insert the remaining.
[snip!]
How can I optimise this process? and What are the alternatives
available ?
If you don't absolutely need to use VB6, why not use something
with native support like PHP?
?
function dba_query($sql) { // Simply return the connection resource ID
// Select the primary database
$dba_conn =
mysql_connect('hostname_a','username_a','password_a') or
die(mysql_error());
$dba_db = mysql_select_db('database_a',$dba_conn);
$r = mysql_query($sql,$dba_conn) or die(mysql_error());
return $r;
}
function dbb_query($sql) { // Simply return the connection resource ID
// Select the secondary database
$dbb_conn =
mysql_connect('hostname_b','username_b','password_b') or
die(mysql_error());
$dbb_db = mysql_select_db('database_b',$dbb_conn);
$r = mysql_query($sql,$dbb_conn) or die(mysql_error());
return $r;
}
$sql = SELECT field1,field2,field3,field4 FROM table_a;
$result = dba_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($result)) {
$ssql = INSERT INTO table_b(field1,field2,field3,field4)
VALUES(
'.$row['field1'].',
'.$row['field2'].',
'.$row['field3'].',
'.$row['field4'].'
};
dbb_query($ssql) or die(mysql_error());
}
?
If you decide to go that route, I recommend subscribing to the
PHP-DB list at http://php.net/mailinglists (referred to there as
Databases and PHP). You should see a significant gain in
performance using a native client as opposed to what you're now using
(probably an ODBC DSN, MyODBC, or a JDBC hack).
--
/Daniel P. Brown
Forensic Services, Senior Unix Engineer
1+ (570-) 362-0283
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimize db update
On Fri, Mar 21, 2008 at 9:14 AM, Velen [EMAIL PROTECTED] wrote: This does not solve my problem. DatabaseA and DatabaseB are on 2 seperate pc and the only communication that can be use between the two PC is a USB pendrive. Is there another way of copying a table from one Database to another? Yes, but it won't validate the data. You'd have to find a way of doing that based on what you need to validate. PC 1: mysqldump -u username -p database_name table_name table_name.sql L Copy .sql file to pen drive. PC 2: mysql -u username -p database_name table_name.sql -- /Daniel P. Brown Forensic Services, Senior Unix Engineer 1+ (570-) 362-0283 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimize db update
This one is alright but is there an alternative that can be run with the mysql prompt? Thanks. Velen - Original Message - From: Daniel Brown [EMAIL PROTECTED] To: Velen [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, March 21, 2008 6:00 PM Subject: Re: Optimize db update On Fri, Mar 21, 2008 at 9:14 AM, Velen [EMAIL PROTECTED] wrote: This does not solve my problem. DatabaseA and DatabaseB are on 2 seperate pc and the only communication that can be use between the two PC is a USB pendrive. Is there another way of copying a table from one Database to another? Yes, but it won't validate the data. You'd have to find a way of doing that based on what you need to validate. PC 1: mysqldump -u username -p database_name table_name table_name.sql L Copy .sql file to pen drive. PC 2: mysql -u username -p database_name table_name.sql -- /Daniel P. Brown Forensic Services, Senior Unix Engineer 1+ (570-) 362-0283 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimize db update
On Fri, Mar 21, 2008 at 10:04 AM, Velen [EMAIL PROTECTED] wrote: This one is alright but is there an alternative that can be run with the mysql prompt? Not to dump a file, but to import, yes. Check out the LOAD DATA INFILE command: http://dev.mysql.com/doc/refman/5.0/en/load-data.html -- /Daniel P. Brown Forensic Services, Senior Unix Engineer 1+ (570-) 362-0283 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimize db update
Hi, Actually I am updating TableA in DatabaseA with values from TableB in DatabaseB. Database B is on a stand alone PC. I'm using VB6 to create a .txt file containing data from TableB then using VB6 once more to recronstruct the table in DatabaseA then remove all data which are already in TableA and insert the remaining. This is working fine but as my table is growing bigger the process is taking more time (about 5 mins for 250,000 records) How can I optimise this process? and What are the alternatives available ? Thanks Regards, Velen
Re: Optimize db update
On Thu, Mar 20, 2008 at 1:41 PM, Velen [EMAIL PROTECTED] wrote:
Actually I am updating TableA in DatabaseA with values from TableB in
DatabaseB. Database B is on a stand alone PC. I'm using VB6 to create a
.txt file containing data from TableB then using VB6 once more to
recronstruct the table in DatabaseA then remove all data which are already in
TableA and insert the remaining.
[snip!]
How can I optimise this process? and What are the alternatives available ?
If you don't absolutely need to use VB6, why not use something
with native support like PHP?
?
function dba_query($sql) { // Simply return the connection resource ID
// Select the primary database
$dba_conn =
mysql_connect('hostname_a','username_a','password_a') or
die(mysql_error());
$dba_db = mysql_select_db('database_a',$dba_conn);
$r = mysql_query($sql,$dba_conn) or die(mysql_error());
return $r;
}
function dbb_query($sql) { // Simply return the connection resource ID
// Select the secondary database
$dbb_conn =
mysql_connect('hostname_b','username_b','password_b') or
die(mysql_error());
$dbb_db = mysql_select_db('database_b',$dbb_conn);
$r = mysql_query($sql,$dbb_conn) or die(mysql_error());
return $r;
}
$sql = SELECT field1,field2,field3,field4 FROM table_a;
$result = dba_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($result)) {
$ssql = INSERT INTO table_b(field1,field2,field3,field4)
VALUES(
'.$row['field1'].',
'.$row['field2'].',
'.$row['field3'].',
'.$row['field4'].'
};
dbb_query($ssql) or die(mysql_error());
}
?
If you decide to go that route, I recommend subscribing to the
PHP-DB list at http://php.net/mailinglists (referred to there as
Databases and PHP). You should see a significant gain in
performance using a native client as opposed to what you're now using
(probably an ODBC DSN, MyODBC, or a JDBC hack).
--
/Daniel P. Brown
Forensic Services, Senior Unix Engineer
1+ (570-) 362-0283
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimize db update
Are the table structures identical ?
If so, you could just move the data files themselves.
Otherwise consider using
unload from table B into TAB seperated format (mysql load format)
truncate table A
load data infile into table A
On Thu, Mar 20, 2008 at 2:20 PM, Daniel Brown [EMAIL PROTECTED] wrote:
On Thu, Mar 20, 2008 at 1:41 PM, Velen [EMAIL PROTECTED] wrote:
Actually I am updating TableA in DatabaseA with values from TableB in
DatabaseB. Database B is on a stand alone PC. I'm using VB6 to create a
.txt file containing data from TableB then using VB6 once more to
recronstruct the table in DatabaseA then remove all data which are already
in TableA and insert the remaining.
[snip!]
How can I optimise this process? and What are the alternatives
available ?
If you don't absolutely need to use VB6, why not use something
with native support like PHP?
?
function dba_query($sql) { // Simply return the connection resource ID
// Select the primary database
$dba_conn =
mysql_connect('hostname_a','username_a','password_a') or
die(mysql_error());
$dba_db = mysql_select_db('database_a',$dba_conn);
$r = mysql_query($sql,$dba_conn) or die(mysql_error());
return $r;
}
function dbb_query($sql) { // Simply return the connection resource ID
// Select the secondary database
$dbb_conn =
mysql_connect('hostname_b','username_b','password_b') or
die(mysql_error());
$dbb_db = mysql_select_db('database_b',$dbb_conn);
$r = mysql_query($sql,$dbb_conn) or die(mysql_error());
return $r;
}
$sql = SELECT field1,field2,field3,field4 FROM table_a;
$result = dba_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($result)) {
$ssql = INSERT INTO table_b(field1,field2,field3,field4)
VALUES(
'.$row['field1'].',
'.$row['field2'].',
'.$row['field3'].',
'.$row['field4'].'
};
dbb_query($ssql) or die(mysql_error());
}
?
If you decide to go that route, I recommend subscribing to the
PHP-DB list at http://php.net/mailinglists (referred to there as
Databases and PHP). You should see a significant gain in
performance using a native client as opposed to what you're now using
(probably an ODBC DSN, MyODBC, or a JDBC hack).
--
/Daniel P. Brown
Forensic Services, Senior Unix Engineer
1+ (570-) 362-0283
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Help build our city at http://free-dc.myminicity.com !
