optimise it or speed up execution time perl script

crmpicco · November 21st, 2006, 10:53 AM

i have the top while loop in my code, then three more of the foreach loop. is there any way to optimise it or speed up execution time? thanks.

Code:

# get a list of the current tables on tiger
open(IN, "/usr/local/piccosearch/crmpicco_rangers");
while(<IN>) {
    chomp;
    (undef, $table) = split(/:/);
    if ($table =~ /^package/) {
        push(@tables, $table);
    }
}
close(IN);

# loop through all the tables
foreach $table (@tables) {
    @thecursor = $sql->execSql("SELECT DISTINCT(stadium),city,country FROM football.$table", 1);
    while ($result = $thecursor[0]->fetchrow_hashref) {
        if ($result->{"stadium"} ne "") {
            $stadium = $result->{"stadium"};
            $city = $result->{"city"};
            $country = $result->{"country"};            

            if ($stadium eq "") {
                $filestr .= "park: $stadium - cty: $city - ctry: $country\n";
            }
        }
    }
}

www.crmpicco.co.uk
www.ie7.com

ciderpunx · November 22nd, 2006, 07:55 AM

Well, the obvious thing would be to have all your stadiums (stadia?) in the same table in your database. That'd get rid of the need to store them in a text file and let you get rid of your foreach loop. I guess your table would be something like:

<code>
SELECT DISTINCT (stadium), city, country
           FROM stadiums;

|id |stadium |city |country |
---------------------------------------
|1 |wembley |london |uk |
...etc.
</code>

You could also drop the embedded if statement, because it will always be false - the stadium field can't be both blank and non-blank at the same time i.e.

<code>
if ($result->{"stadium"} ne "") { # if stadium field IS NOT blank...
  # ...
  $stadium = $result->{"stadium"};
  # ...
  if ($stadium eq "") { # ...and if stadium field IS blank
  # ...
}
</code>

HTH
Charlie

--
Don't Stand on your head - you'll get footprints in your hair
                                           http://charlieharvey.org.uk
                                              http://charlieharvey.com

crmpicco · November 22nd, 2006, 08:31 AM

thanks again Charlie

www.crmpicco.co.uk
www.ie7.com