Re: Creating a hashes from AoHs

Now that the basic syntax problem is solved, I can't resist harping on the old truism (which many have probably grown tired of seeing):

If you're processing a lot of rows, you'd be better off making you perl script a little simpler: have it just print out another suitable flat file that would be easy to feed into whatever data-importer tool is native to the particular database server you are using.

The speed differential between bulk-loading with such a tool vs. doing a series of inserts with DBI (even with a prepared statement and placeholders) can be dramatic if you're dealing with thousands of rows on a regular basis. And of course, you can set up your perl script so that once it's done writing the loadable flat file, it goes ahead and runs your database bulk-loader utility on the file. That way, the end result from the user's point of view is the same, except that it happens much faster.

(For smaller sets of rows, it's a toss-up -- whatever you're most comfortable with is fine -- but the bulk-load tool scales well, whereas DBI inserts do not.)

Comment on Re: Creating a hashes from AoHs

Replies are listed 'Best First'.
Re^2: Creating a hashes from AoHs by bradcathey (Prior) on Mar 03, 2006 at 12:49 UTC
Okay, I'm interested. Do you have sample code that show how this works? I'm especially interested in how it gets from the new flat file into the database (I use MySQL). Thanks. —Brad "The important work of moving the world forward does not wait to be done by perfect men." George Eliot	[reply]
Re^3: Creating a hashes from AoHs by graff (Chancellor) on Mar 04, 2006 at 00:45 UTC
In terms of how to get flat-file data into table, just read about "LOAD DATA INFILE" and "mysqlimport" in the mysql manual -- it's pretty clear and simple (a lot less hassle than the bulk-loader tools of some other RDB engines). As for getting your perl script to write a flat file instead of doing inserts directly to mysql, just open an output file instead of a connection to the db. (Well, if some of your input is coming from the db, you can either connect to do the query, or save the query output to a file before running this script.) When you get to the point in the script where you would have done an insert statement, just write those values to the output file as tab-delimited fields, terminated by a newliine. When done, close the file and use "system()" to run mysqlimport on it.	[reply]