Speeding up Perl access to a FoxPro database using XBase

Hello,

I posted a question in the databases area, but in pondering it, I think it is more of a perl problem.  Please see:

http://experts-exchange.com/Databases/FoxPro/Q_21479981.html

Thanks!
drtesterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kim RyanIT ConsultantCommented:
Firstly, I would measure how long critical sections of your code takes to see the bottlenecks. If most of the time os being spent in database select and insert, you can gain speed by optimizing the database and not the perl code. Possible areas include creating a table view on just the columns you need to access.

On the perl side, instead of a fetch, you could use $hash_ref = $sth->fetchrow_hashref;
Also, instead of  
$s{OWN_ADDR}=~tr/a-z/A-Z/;
you could use
$s{OWN_ADDR}= uc($s{OWN_ADDR});
but not sure if it is quicker


drtesterAuthor Commented:
Thanks for the reply, Teraplane.

I have already done database optimizations prior to this code.  And the strange part is that the MySQL part (and all the other little perl bits) are identical - the difference is in how I'm calling the XBase module, and how I'm extracting variables from it.

Is there a way to combine the quoting and uppercasing in one statement?

Does perl not do so well with $x{name} types?

Kim RyanIT ConsultantCommented:
from the dbi doco
---
Using placeholders and @bind_values with the do method can be useful because it avoids the need to correctly quote any variables in the $statement. But if you'll be executing the statement many times then it's more efficient to prepare it once and call execute many times instead.
---
So you shouldn't need to use $s{STAT}=~s/['\\]/\\'/g;

I'm only guessing but as FoxPro is not that widely used there may not be much work going inot the development of the fastest droivers for it. A dbase with hundreds of columns will take time to access.

If this is a one off exercise, I would also think of :
- exporting your foxpro tables to a flat file
- do your transformtion with perl on flat file
- load new file with MySQL loader and index if needed
the dbase utilities should be quicker for a whole table load

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.