Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > improve the efficiency

Reply
Thread Tools

improve the efficiency

 
 
Johnson Lau
Guest
Posts: n/a
 
      10-06-2003
I wrote a code to compare the NAME1 in table1 an NAME2 in table2. If a NAME2 equals to any one of NAME1, delete the record
containing NAME2. Each of table1 and table2 is in Foxpro (*.dbf) format, containing around 6000 records and sorting according to
NAME1 or NAME2. It takes me around 2 hours to finish the job. Is it possible to improve the efficiency? Thanks very much.


#!/usr/bin/perl
use strict;
use DBI;
my $dbh = DBI->connect("DBI:XBase:") or die $DBI::errstr;
my $abc = $dbh->selectcol_arrayref("select NAME1 from table1");
my $def = $dbh->prepare("delete from table2 where NAME2 = ?", {'MaxRows' => 1});
foreach (@$abc) {
$lc->execute($_);
}


 
Reply With Quote
 
 
 
 
Gregory Toomey
Guest
Posts: n/a
 
      10-06-2003
It was a dark and stormy night, and Johnson Lau managed to scribble:

> I wrote a code to compare the NAME1 in table1 an NAME2 in table2. If a
> NAME2 equals to any one of NAME1, delete the record containing NAME2. Each
> of table1 and table2 is in Foxpro (*.dbf) format, containing around 6000
> records and sorting according to NAME1 or NAME2. It takes me around 2
> hours to finish the job. Is it possible to improve the efficiency? Thanks
> very much.
>
>
> #!/usr/bin/perl
> use strict;
> use DBI;
> my $dbh = DBI->connect("DBI:XBase:") or die $DBI::errstr;
> my $abc = $dbh->selectcol_arrayref("select NAME1 from table1");
> my $def = $dbh->prepare("delete from table2 where NAME2 = ?", {'MaxRows'
> => 1}); foreach (@$abc) {
> $lc->execute($_);
> }


Knowing nothing about foxpro, I suggest
delete from table2 where NAME2 in (select NAME1 from table1)

Make sure Name2 is indexed.

gtoomey
 
Reply With Quote
 
 
 
 
Johnson Lau
Guest
Posts: n/a
 
      10-06-2003

"Gregory Toomey" <(E-Mail Removed)> 在郵件 news:2311981.hecxWES94K@gregs-web-hosting-and-pickle-farming 中撰寫...
> It was a dark and stormy night, and Johnson Lau managed to scribble:
>
> > I wrote a code to compare the NAME1 in table1 an NAME2 in table2. If a
> > NAME2 equals to any one of NAME1, delete the record containing NAME2. Each
> > of table1 and table2 is in Foxpro (*.dbf) format, containing around 6000
> > records and sorting according to NAME1 or NAME2. It takes me around 2
> > hours to finish the job. Is it possible to improve the efficiency? Thanks
> > very much.
> >
> >
> > #!/usr/bin/perl
> > use strict;
> > use DBI;
> > my $dbh = DBI->connect("DBI:XBase:") or die $DBI::errstr;
> > my $abc = $dbh->selectcol_arrayref("select NAME1 from table1");
> > my $def = $dbh->prepare("delete from table2 where NAME2 = ?", {'MaxRows'
> > => 1}); foreach (@$abc) {
> > $lc->execute($_);
> > }

>
> Knowing nothing about foxpro, I suggest
> delete from table2 where NAME2 in (select NAME1 from table1)
>


i got such error

DBD::XBase::db prepare failed: Relation expected near ` IN (select NAME1 from table1)'


> Make sure Name2 is indexed.
>
> gtoomey



 
Reply With Quote
 
Gregory Toomey
Guest
Posts: n/a
 
      10-06-2003
It was a dark and stormy night, and Johnson Lau managed to scribble:

>
> "Gregory Toomey" <(E-Mail Removed)> 在郵件
> news:2311981.hecxWES94K@gregs-web-hosting-and-pickle-farming 中撰寫...
>> It was a dark and stormy night, and Johnson Lau managed to scribble:
>>
>> > I wrote a code to compare the NAME1 in table1 an NAME2 in table2. If a
>> > NAME2 equals to any one of NAME1, delete the record containing NAME2.
>> > Each of table1 and table2 is in Foxpro (*.dbf) format, containing
>> > around 6000 records and sorting according to NAME1 or NAME2. It takes
>> > me around 2 hours to finish the job. Is it possible to improve the
>> > efficiency? Thanks very much.
>> >
>> >
>> > #!/usr/bin/perl
>> > use strict;
>> > use DBI;
>> > my $dbh = DBI->connect("DBI:XBase:") or die $DBI::errstr;
>> > my $abc = $dbh->selectcol_arrayref("select NAME1 from table1");
>> > my $def = $dbh->prepare("delete from table2 where NAME2 = ?",
>> > {'MaxRows' => 1}); foreach (@$abc) {
>> > $lc->execute($_);
>> > }

>>
>> Knowing nothing about foxpro, I suggest
>> delete from table2 where NAME2 in (select NAME1 from table1)
>>

>
> i got such error
>
> DBD::XBase::db prepare failed: Relation expected near ` IN (select NAME1
> from table1)'
>
>
>> Make sure Name2 is indexed.
>>
>> gtoomey


Then your SQL probably hoes not have sub-selects. Just make sure Name2 is indexed ie has a secondary/non-unique index.

A database like Oracle or even mysql would be ables to do this in a few seconds so I dont know why it takes you hours.

gtoomey
 
Reply With Quote
 
Johnson Lau
Guest
Posts: n/a
 
      10-06-2003

"Gregory Toomey" <(E-Mail Removed)> 在郵件 news:1224330.2GIZYlYOqS@gregs-web-hosting-and-pickle-farming 中撰寫...
> It was a dark and stormy night, and Johnson Lau managed to scribble:
>
> >
> > "Gregory Toomey" <(E-Mail Removed)> 在郵件
> > news:2311981.hecxWES94K@gregs-web-hosting-and-pickle-farming 中撰寫...
> >> It was a dark and stormy night, and Johnson Lau managed to scribble:
> >>
> >> > I wrote a code to compare the NAME1 in table1 an NAME2 in table2. If a
> >> > NAME2 equals to any one of NAME1, delete the record containing NAME2.
> >> > Each of table1 and table2 is in Foxpro (*.dbf) format, containing
> >> > around 6000 records and sorting according to NAME1 or NAME2. It takes
> >> > me around 2 hours to finish the job. Is it possible to improve the
> >> > efficiency? Thanks very much.
> >> >
> >> >
> >> > #!/usr/bin/perl
> >> > use strict;
> >> > use DBI;
> >> > my $dbh = DBI->connect("DBI:XBase:") or die $DBI::errstr;
> >> > my $abc = $dbh->selectcol_arrayref("select NAME1 from table1");
> >> > my $def = $dbh->prepare("delete from table2 where NAME2 = ?",
> >> > {'MaxRows' => 1}); foreach (@$abc) {
> >> > $lc->execute($_);
> >> > }
> >>
> >> Knowing nothing about foxpro, I suggest
> >> delete from table2 where NAME2 in (select NAME1 from table1)
> >>

> >
> > i got such error
> >
> > DBD::XBase::db prepare failed: Relation expected near ` IN (select NAME1
> > from table1)'
> >
> >
> >> Make sure Name2 is indexed.
> >>
> >> gtoomey

>
> Then your SQL probably hoes not have sub-selects. Just make sure Name2 is indexed ie has a secondary/non-unique index.
>
> A database like Oracle or even mysql would be ables to do this in a few seconds so I dont know why it takes you hours.


then i will try to convert the database to mysql, thanks very much

>
> gtoomey



 
Reply With Quote
 
Tore Aursand
Guest
Posts: n/a
 
      10-06-2003
On Mon, 06 Oct 2003 13:19:29 +0800, Johnson Lau wrote:
> #!/usr/bin/perl
> use strict;
> use DBI;
> my $dbh = DBI->connect("DBI:XBase:") or die $DBI::errstr;
> my $abc = $dbh->selectcol_arrayref("select NAME1 from table1");
> my $def = $dbh->prepare("delete from table2 where NAME2 = ?", {'MaxRows' => 1});
> foreach (@$abc) {
> $lc->execute($_);
> }


Does this really take _two hours_ to finish? I've never used FoxPro
actually, but I didn't think that it was so slow? Doing the same in MySQL
would have taken just a few seconds, I guess.

You are completely sure that it's only this part of your script that takes
so long to execute? There aren't anything else we should know about? And
you've tried to print out some debugging info in that 'foreach' loop?


--
Tore Aursand <(E-Mail Removed)>
 
Reply With Quote
 
Eric J. Roode
Guest
Posts: n/a
 
      10-06-2003
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

"Johnson Lau" <(E-Mail Removed)> wrote in
news:blqu12$heq$(E-Mail Removed):

> I wrote a code to compare the NAME1 in table1 an NAME2 in table2. If a
> NAME2 equals to any one of NAME1, delete the record containing NAME2.
> Each of table1 and table2 is in Foxpro (*.dbf) format, containing
> around 6000 records and sorting according to NAME1 or NAME2. It takes
> me around 2 hours to finish the job. Is it possible to improve the
> efficiency? Thanks very much.


The perl code seems reasonably efficient. Perhaps you could improve the
database selects, but you'll get a better response for that if you ask in a
database newsgroup, I'll bet.

- --
Eric
$_ = reverse sort $ /. r , qw p ekca lre uJ reh
ts p , map $ _. $ " , qw e p h tona e and print

-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 7.0.3 for non-commercial use <http://www.pgp.com>

iQA/AwUBP4FY4GPeouIeTNHoEQISzACg0GM9ilRlkUN4lBbMjVEhet eDc5sAn0qC
A9QbuAw7S73xHKkx7Yc1VTau
=j0A/
-----END PGP SIGNATURE-----
 
Reply With Quote
 
Eric Bohlman
Guest
Posts: n/a
 
      10-06-2003
Tore Aursand <(E-Mail Removed)> wrote in
news(E-Mail Removed):

> On Mon, 06 Oct 2003 13:19:29 +0800, Johnson Lau wrote:
>> #!/usr/bin/perl
>> use strict;
>> use DBI;
>> my $dbh = DBI->connect("DBI:XBase:") or die $DBI::errstr;
>> my $abc = $dbh->selectcol_arrayref("select NAME1 from table1");
>> my $def = $dbh->prepare("delete from table2 where NAME2 = ?",
>> {'MaxRows' => 1}); foreach (@$abc) {
>> $lc->execute($_);
>> }

>
> Does this really take _two hours_ to finish? I've never used FoxPro
> actually, but I didn't think that it was so slow? Doing the same in
> MySQL would have taken just a few seconds, I guess.


FoxPro doesn't really have native SQL support; DBD::XBase is providing it
through flat-file operations. It's not actually using an optimized
database engine.

If table2 isn't indexed (or DBD::XBase can't take advantage of an index)
then the algorithm reduces to a linear search of table2 for each entry in
table1, giving quadratic behavior. In that case, short of switching to a
true database engine, the OP would be best off reading in all the entries
in both tables, using standard hash methods to find their intersection, and
then doing deletes on only those keys in the intersection.
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use template to improve efficiency? shuisheng C++ 3 04-05-2010 08:26 AM
how to improve transfering file's efficiency JTL.zheng Java 7 12-21-2006 07:48 AM
how to improve transfering file's efficiency JTL.zheng Java 0 11-19-2006 10:20 AM
Can I improve efficiency of 50.000 "new"'ed objects? David Hilsee C++ 4 09-01-2004 05:39 AM
Can static variable improve efficiency? Peng Jian C Programming 9 07-13-2004 08:31 AM



Advertisments