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

On Thu, Mar 20, 2008 at 1:41 PM, Velen <velen@biz-mu.com> 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

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 <parasane@gmail.com> wrote:

> On Thu, Mar 20, 2008 at 1:41 PM, Velen <velen@biz-mu.com> 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/mysql?unsub=pchapman@nc.rr.com
>



--
Help build our city at http://free-dc.myminicity.com !

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" <parasane@gmail.com>
To: "Velen" <velen@biz-mu.com>
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 <velen@biz-mu.com> 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/mysql?unsub=velen@biz-mu.com
>
>

On Fri, Mar 21, 2008 at 9:14 AM, Velen <velen@biz-mu.com> 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

This one is alright but is there an alternative that can be run with the
mysql> prompt?

Thanks.

Velen


----- Original Message -----
From: "Daniel Brown" <parasane@gmail.com>
To: "Velen" <velen@biz-mu.com>
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 <velen@biz-mu.com> 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
>

On Fri, Mar 21, 2008 at 10:04 AM, Velen <velen@biz-mu.com> 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

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" <parasane@gmail.com>
To: "Velen" <velen@biz-mu.com>
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 <velen@biz-mu.com> 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/mysql?unsub=velen@biz-mu.com
>
>