There are two Tutorials that you should look at.

• DBI Recipes tells you what you can do, and gives you some interesting trick about usiong hashes efficiently.
• Speeding up the DBI tells you how to find which part of your program needs improvement, and several ways of doing it.

HTH

There's not enough information to properly fix your problem, but here are a few thoughts. I guess the most obvious question is about your use of "SELECT *". Do you really need to select all of those fields? That's 8 million fields that you are selecting. (Personally, I'm rather suspect about any table that requires 200 fields (it suggests to me that it's not designed properly.) And what are you printing to? Are you sending the data to a socket, to STDOUT, to a file? Those can all have significant performance issues. If you're printing to STDOUT, try printing to a file and see how much faster it is.

And yes, fetchrow_arrayref is faster. Perhaps you can use that and a hash slice? I haven't benchmarked that, so I can't comment on the performance aspect. However, you say that you need to have $result as a hashref. Why?

Last question: are you sure those indexes are useful? The SQL statements that you use can suggest indexes you might need, but the data is important, too. Not every field will benefit from an index. Also, have you used a profiler to find out where your SQL is really bottlenecking? (I assume MySQL will let show you query plans). The statement itself seems pretty simple, but whether you're IO bound or CPU bound could make a difference. Talk to a MySQL DBA about this issue. A good DBA is worth their weight in gold.

To build on what ovid said, you might want to look to see how much of the time taken was from mysql. Dump the SQL that perl is using for its query, and then run that in the mysql client. If that takes a significant time, you might take a look at EXPLAIN. It's also necessary to ANALYZE your tables when they change significantly, so that the database can determine if the indexes are going to be a benefit or not. (it can be faster to not use an index unless you're throwing away the majority of the records, as you require more table reads... for oracle, I've heard that number is between 5 to 20% of the records kept.. so if you're throwing away less than 4/5 of the records, a full table scan may be the way to go.

Memory sizing of the system can also be important -- out of the box, mysql uses very little memory (in my opinion). If you have a 2GB dedicated server, it does no good if mysql is told to only use 256MB. (it might be 384MB as the default...still rather small for some people).

Anyway, if you find that the perl process is taking 30 minutes, but it's only taking 5 minutes for mysql to do its work, then you'll want to look at the Perl side of things. If it's taking 25 minutes in MySQL, then you'll want to look at adjusting the SQL statement, or tuning the database. If it's somewhere in between, well, pick one, and if that doesn't get enough time out to be acceptable, tune the other side, and keep going back and forth 'till it meets your goal time.

my @fields = qw(foo bar baz);
my %results;
$sth->execute();
$sth->bind_columns(@results{@fields});
while( $sth->fetch() ) {
   #your results will be available in %results
}
[download]

Do you really need all 200 columns when you select?

Yes, I do. :(

Well, then you'll probably want to get the list of column names via a separate query, store those in an array (e.g. "@colnames", then use the array to define the data query and then load the hash:

my $sqlstr = "select " . join(",", @colnames) . " from sometable ...";
my $sth = $dbh->prepare( $sqlstr );

while ( $rowref = $sth->fetchrow_arrayref ) {
    @rowhash{@colnames} = @$rowref;
    ...
}
[download]

(untested, but it should give you a good-enough idea...; um, especially after the update to use "@$rowref" in the hash slice assignment instead of "$$rowref" which was wrong.)

Since you're using mysql, you could get the list of column names by a query like "describe sometable" -- the column name will be the first column in each returned row. (There are other ways, but this is pretty simple.)

Can you give a hint of how you can possibly come up with 200 columns in a design? I can't think of anything in life that has 200 aspects that can't be somehow factored out into some other subaspect hierarchy.

For example, I recall the word "campaign" earlier in this thread (or maybe hallucinated it). If that's the case, and some of these column names are perhaps the names of each state, then you can turn the table sideways and create 50 separate rows per event in a one-to-many relationship instead of having 50 sets of columns (maybe 4 per thing?) to record the same thing.

I dunno, but my gut tells me that ending up with 200 columns in an SQL table is a bit like ending up with 200 Perl scalar variables instead of an array. A symptom of that is a lot of variables with similar names differing only by some data-like aspect, like a serial number (1, 2, 3, 4) or a state (WA, OR, CA, ID, ...).

-- Randal L. Schwartz, Perl hacker
Be sure to read my standard disclaimer if this is a reply.

---

update: As I was chatting with a friend about this problem a bit later, I realized that the problem like this usually results from improperly taking "3 dimensional data" (values depend on 3 inputs) and mapping it to a 2 dimensional table naively: primary key column is one dimension, other columns represent a cross of the other two dimensions. The proper solution is to have two key columns represent two of the three dimensions, and the column names be the third (and hopefully the most diverse and consistent).

CREATE INDEX exp_update ON campaigns ( mw_export, last_update )
--or this 
CREATE clustered INDEX cl_exp_update ON campaigns ( mw_export, last_up
+date )
[download]

Thanks. I know that I should be using indexes and I was surprised that the ones I created didn't help out much.

print "It takes me over 30 minutes to print this!!!\n";

Are you printing 40_000 records of 200 fields each to your screen? Or are you doing something else to them? Perhaps that's where the long processing time is?

Do you save all the (processed) data in some variables/arrays/hashes? That could eat up your memory very fast and then your system starts swapping to disk and performance degrades.

But perhaps they fixed it in a newer Mysql version. Or maybe there's a setting to tweak Mysql, so that it doesn't do that.

MySQL fetched the entire resultset before returning the first row

That's what I suspect is happening, too.

$sth->execute(last_transfer{'campaigns'});
[download]

In case the problem is memory-consumption + thrashing, it may be worthwhile to batch-process your rows with a LIMIT-clause to your SQL-statement (and repeated execution, of course). Just get n rows at a time and process those, before going on with the next batch. I guess n should be 1000 or 2500 or so (try and err ;)

Or use fetchrow_arrayref() and get your row processing out of the way immediately.

(Btw, have you found out anything yet about where the bottleneck is?)

That's what I ended up doing. Just sending stuff a little at a time with the limit clause, but this is not ideal. Hopefully, under normal circumstances, my script would not handle tens of thousands of rows at once. For important testing purposes, we decided to send over all the rows (and that's when we see the huge slowdown). So far, the bottleneck appears to be what thor suggested: mysql fetches all of the data before returning the first row. The more I increase the limit, the longer it takes to process the first row. ...

Perhaps I'm missing something, but how will adding another layer of abstraction to the equation help his speed problems? He's as close to the database as he can be without dropping into XS and he's having the problems. Surely putting another module in between won't help...

thor

Feel the white light, the light within
Be your own disciple, fan the sparks of will
For all of us waiting, your kingdom will come

CDBI is great. CDBI also slows apps down a great deal, usually, imho.