#!/usr/bin/perl -w
use strict;
use DBI;
my $dbh;

my $time = time;
for(1..999) {
  $dbh = DBI->connect('DBI:ODBC:sqlkranon', 'sa', '') || die $dbh->err
+str;
  $dbh->disconnect;
}

$dbh = DBI->connect('DBI:ODBC:sqlkranon', 'sa', '') || die $dbh->errst
+r;

print "1000 connects in ", time - $time, " seconds\n";

$time = time;
my $rows;
for(1..10000) {
  my $sth = $dbh->prepare("select CveAsegSeguro,CveSeguro from C_Asegu
+radorasSeguros");

  $sth->execute();
  while (my @row = $sth->fetchrow_array ) {
    $rows++;
  }
}
$dbh->disconnect;

print "10000 selects fetching $rows rows in ", time - $time, " seconds
+\n";

__END__

Results:

1000 connects in 15 seconds
10000 selects fetching 30000 rows in 17 seconds
[download]

You said you want to use DBI, but if nothing else works, there's a set of non-CPANed modules called MSSQL::DBlib and MSSQL::Sqllib available here. These don't use DBI and don't claim support for MS-SQL 7, so I'd shy away from those unless really necessary.

my $DSN = 'driver={SQL Server};Server=212.233.1.70; database=db;uid=us
+r;pwd=pwusr;'; 
my $dbh = DBI->connect("dbi:ODBC:$DSN") or die "$DBI::errstr\n";
[download]

use DBI;
my $dbh = DBI->connect("dbi:ODBC:MQIS","USER","PASSWORD");
[download]

If your client is gonna run on UNIX (or any OS for which MS does not provide client-side ODBC libraries) you have 2 choices:

• DBD::Proxy - run a proxy DBD on an NT box and have your perl programs use DBD::Proxy to connect to the proxy.
• 3rd party ODBC driver - there are some 3rd party ODBC drivers for MS Sql server (some commercial, some not) that you could use. I've never used these (don't even know where to get them), but I have heard that they exist :)

Openlink Software is a company that has a very good generic type
of ODBC driver, that will connect to just about any type of database,
whether it is Oracle, MySQL, Informix, MS SQL Server, Solid, etc.

TStanley
There can be only one!

An update to this post..The Openlink drivers work with pretty much any database at the moment, except MS SQL Server. It seems their code has a bug in it, of which they are aware, and it will be fixed in their next release, which is due out within (HOPEFULLY) the next month. The drivers for Oracle work quite well, and the only problem that has been noted, is that you need to use a SQLDRIVERCONNECT to connect to the database, instead of SQLCONNECT. This has been reported to Openlink, and will be addressed in the next release.

TStanley


UPDATE (13 NOV 00): The new drivers are out and unfortunately, the same bug is still there.

Although your boss may not desire you use Win32::ODBC for performance reasons, I think that you'll find the performance adequate.

I recently used Perl/ODBC heavily in an NT/Netscape Enterprise/MSSQL 7 environment and was quite pleased with the performance. I also had a direct connect to Oracle on Sun. To the end user, the differences were not noticable.

If you go with ODBC I would strongly suggest avoiding using DSN's on the boxes, create your connect strings in your programs, then you can move them around without worrying.

If you find a good direct-connect solution, please provide the information! While ODBC was sufficient, it still requires MDAC and all that being installed in addition to the perl libs.

good luck!

It is very likely for a small performance hit of using the ODBC. Due to the overhead function calls of DBI -> DBD::ODBC -> ODBC -> MSSQL. But I don't see the reason for a large performance hit.

Actually I don't see any reason not to use ODBC. Even if it is Microsoft technology but MSSQL and ODBC agree with each other, why shouldn't they... both are Microsoft.

JanneVee

I think there IS a real performance problem when you use ODBC instead of a native driver. ODBC is slow. I've never seen a fast ODBC connection.
Unfortunately I've never seen a native SQL Server driver for DBI. I once had to write some Perl to SQL Server, and we used ADO.pm (I believe Matt Sergeant wrote that one). It worked very well (and faster than DBI::ODBC), but that wasn't what you would want, since it wasn't a DBD-driver.
There IS a DBI::ADO driver available though, but I've never used it. It's at least faster than ODBC.

Jouke

I always have considered ODBC as a low level API to databases. The slowness that I experienced was by the "blocking" API calls. i.e. poorly written code....

By using ADO you get some performance enhancements, even if the ADO connection goes through OLE DB and that goes through ODBC.

One performance enhancement I can think of is Persistant connections. That is a thing you've wont get with a native MSSQL driver!

Janne