The second thing, given a slow network connection, is to keep as much of the operations on the server. Avoid pulling data from the server for processing in perl and then reinserting the data on to the server - instead write SQL to do this so that the data stays on the server.

Michael

I would agree with mpeppler. In my experience, you can usually make the application perform more work on the database, and this can reduce the amount of data you're pushing around, especially over an ISDN line.

Also, I don't know how quickly you can connect to the database, but I've seen new connections to an oracle database take up to 20 seconds.

So if you have multiple connections in your program, it might be worthwhile to "cache" the database handle somewhere and then only connect once. Unfortunately, I don't know much about the application overall, so its hard to suggest architectural changes when one doesn't know the architecture to begin with :).

Finally, most of the time I find myself attempting to optimize something that's not a problem. Do you have mathmatical evidence (ie hard numbers) that show that the ODBC driver is solely the cause of your performance issues? I'm not saying that you're not correct, but I just want to make sure you're not trying to solve the wrong problem.


--
Ben

Also, I don't know how quickly you can connect to the database, but I've seen new connections to an oracle database take up to 20 seconds.

MS-SQL (and Sybase) is pretty good in that respect - creating a new connection is a relatively fast operation, though that doesn't invalidate your point - reconnecting over and over is not a good thing (especially over a slow link)

Michael

Hm, thank you for the SQL construction suggestions. I have tried to push as much as possible onto the server, but I'm not an SQL expert; perhaps I should find one to take a look at my statements. Unfortunately, I'm not sure there's a whole lot of optimization I can do in this manner, as a large portion of my work is to get DB records and compare them with text files or other data sources.

I don't ever open multiple connections, except when there are multiple instances of my application (which can't be helped), so that isn't the cause of my woes. One db handle is enough, thank you. ;-)

I can't prove that it's the ODBC driver that's the problem. I do know that performing an identical query using a third-party SQL tool is much faster (even when I account for connection times). I also know that native access methods are often faster as opposed to ODBC, so I'm hoping to find a DBD driver that uses MS-SQL natively. I can at least benchmark, then.

Finally, while my performance is "acceptable" to my users and my employer, I consider it borderline; I'm wanting to refactor before adding yet more DB things.

I wish I could just post the code, but NDA and all that... so please ask me anything you need to help you formulate a starting point for me to research.

---

radiantmatrix
require General::Disclaimer;
Perl is

---

$sth->execute;
my %row;
$sth->bind_columns( \( @row{ @{$sth->{NAME_lc} } } ));
while ($sth->fetch) {
    print "$row{region}: $row{sales}\n";
}
[download]

Hm... I will have to analyze this. I often work with 60k+ row counts. Thank you!

---

radiantmatrix
require General::Disclaimer;
Perl is

---

The number of rows won't matter, this loops over the rows individually, just as fetchrow_hashref() does. The difference is that bind_cols() takes away some of the hash-creation overhead. The DBI docs unambiguously state that it is faster than fetchrow_hashref().

If you're on Win32, one alternative is DBD::ADO. There are some different limitations versus ODBC, but overall it works well.

That said, I have to echo what others have also written. It's very unlikely that any slowness you're experiencing is from DBI or any of the DBD layers. The culprit is most often a data model that's unsuitable for what you're trying to do, or code that doesn't work well with a given model (doing row-by-row processing is often a problem, for instance).

It's hard to answer your question in general terms. You may want to post some code and/or design ideas, and ask for specifics.

well most of what I'm going to say is standard DBI fare. If you're going to be inserting multiple records into the same table a structure like this is preferred for speed and security.

my %items_to_insert = (item1=>1,
                       item2=>2);

$statement = "INSERT INTO myTbl (itemName,itemNumber) VALUES(?,?);
my $sth = $dbh->prepare($statement);

foreach my $key (keys(%items_to_insert))
{
   $sth->execute($key,$items_to_insert{$key});
}
[download]

I would say that one call to a fetch_all type statement in DBI rather than individual row fetching would be preferred as well. You minimize the total network traffic that way (correct me if wrong here)

So in summary, my big points of advice for DBI in this case are

• use placeholders so you only have to prepare the INSERT or DELETE statements once.
• Use fetchall methods in lieu of fetchrow

Of course I already use placeholders and such, but I typically only run a given SELECT statement once in this application, so it doesn't help much.

As to the fetchall recommendation, I would usually agree; but I occasionally recieve in excess of 60,000 records and do so with several instances. Resources considerations become important.

---

radiantmatrix
require General::Disclaimer;
Perl is

---

Give it a shot, you have nothing to lose. Wish I had some benchmarks for you to confirm my theories ;)

Good luck!
MrCromeDome