improve the efficiency

Discussion in 'Perl Misc' started by Johnson Lau, Oct 6, 2003.

  1. Johnson Lau

    Johnson Lau Guest

    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($_);
    }
    Johnson Lau, Oct 6, 2003
    #1
    1. Advertising

  2. 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
    Gregory Toomey, Oct 6, 2003
    #2
    1. Advertising

  3. Johnson Lau

    Johnson Lau Guest

    "Gregory Toomey" <> ¦b¶l¥ó news:2311981.hecxWES94K@gregs-web-hosting-and-pickle-farming ¤¤¼¶¼g...
    > 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
    Johnson Lau, Oct 6, 2003
    #3
  4. It was a dark and stormy night, and Johnson Lau managed to scribble:

    >
    > "Gregory Toomey" <> ¦b¶l¥ó
    > news:2311981.hecxWES94K@gregs-web-hosting-and-pickle-farming ¤¤¼¶¼g...
    >> 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
    Gregory Toomey, Oct 6, 2003
    #4
  5. Johnson Lau

    Johnson Lau Guest

    "Gregory Toomey" <> ¦b¶l¥ó news:1224330.2GIZYlYOqS@gregs-web-hosting-and-pickle-farming ¤¤¼¶¼g...
    > It was a dark and stormy night, and Johnson Lau managed to scribble:
    >
    > >
    > > "Gregory Toomey" <> ¦b¶l¥ó
    > > news:2311981.hecxWES94K@gregs-web-hosting-and-pickle-farming ¤¤¼¶¼g...
    > >> 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
    Johnson Lau, Oct 6, 2003
    #5
  6. Johnson Lau

    Tore Aursand Guest

    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 <>
    Tore Aursand, Oct 6, 2003
    #6
  7. -----BEGIN PGP SIGNED MESSAGE-----
    Hash: SHA1

    "Johnson Lau" <> wrote in
    news:blqu12$heq$:

    > 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/AwUBP4FY4GPeouIeTNHoEQISzACg0GM9ilRlkUN4lBbMjVEheteDc5sAn0qC
    A9QbuAw7S73xHKkx7Yc1VTau
    =j0A/
    -----END PGP SIGNATURE-----
    Eric J. Roode, Oct 6, 2003
    #7
  8. Johnson Lau

    Eric Bohlman Guest

    Tore Aursand <> wrote in
    news:p:

    > 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.
    Eric Bohlman, Oct 6, 2003
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. David Hilsee
    Replies:
    4
    Views:
    416
    Vaclav Haisman
    Sep 1, 2004
  2. JTL.zheng
    Replies:
    0
    Views:
    272
    JTL.zheng
    Nov 19, 2006
  3. JTL.zheng
    Replies:
    7
    Views:
    454
    JTL.zheng
    Dec 21, 2006
  4. Peng Jian

    Can static variable improve efficiency?

    Peng Jian, Jul 12, 2004, in forum: C Programming
    Replies:
    9
    Views:
    616
  5. shuisheng
    Replies:
    3
    Views:
    361
    tonydee
    Apr 5, 2010
Loading...

Share This Page