DBD::ODBC connections are very slow

Hi

I have been asked to add DBD::ODBC to an Solaris server so that I can copy
data from a number of Windows Solid databases to Oracle.

Everything works, but the

my $Dbh = DBI->connect("DBI:ODBC:","","");

is very slow, typically 20s, whilst the rest of the script with
(prepare,execute,fetch loop) is much faster.

I have checked the unixODBC isql command, and the connection is much
faster(<<1s), and the query takes approximately the same time as using
DBD::ODBC.

Any ideas on the best way to investigate the cause of the slow connection?

All the best

Simon

Re: DBD::ODBC connections are very slow

Oracles Login Process is relatively slow, there is nothing you can do.
But 20 seconds are too slow, it seems you have problems with the Oracle
client setup. Check DNS and TNSNAMES.ORA. Check if
$ORACLE_HOME/NETWORK/ADMIN/TNSNAMES.ORA (or
$ORACLE_HOME/NET80/ADMIN/TNSNAMES.ORA) contains the line
"SQLNET.AUTHENTICATION_SERVICES= (NTS)". If it does, comment it out
(change the line to "# SQLNET.AUTHENTICATION_SERVICES= (NTS)"). This may
accelerate your login, unless your Oracle setup uses NTS, in that case,
you cannot login any more.

You should also think about using DBD::Oracle instead of DBD::ODBC,
which removes the overhead introduced by the ODBC layer.

Alexander

On 04.05.2006 11:45, simonw@cornfield.org.uk wrote:

>Hi
>
>I have been asked to add DBD::ODBC to an Solaris server so that I can copy
>data from a number of Windows Solid databases to Oracle.
>
>Everything works, but the
>
>my $Dbh = DBI->connect("DBI:ODBC:","","");
>
>is very slow, typically 20s, whilst the rest of the script with
>(prepare,execute,fetch loop) is much faster.
>
>I have checked the unixODBC isql command, and the connection is much
>faster(<<1s), and the query takes approximately the same time as using
>DBD::ODBC.
>
>Any ideas on the best way to investigate the cause of the slow connection?
>
>All the best
>
>Simon
>
>
>


--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/

Re: DBD::ODBC connections are very slow

Hi

I am sorry if my initial post was unclear.

I have a lot of data held in a number of SOLID instances (on Windows 2000
servers), and I have been asked to copy some of this data into Oracle (on
Solaris server). The only to access SOLID remotely is by ODBC, hence on
my Solaris server I have installed DBD::ODBC, UnixODBC and Solid SDK.

The UnixODBC isql programme connects to teh remote SOLID databases ok and
performs queries ok.

A Peer DBD::ODBC takes a long time to connect, but executes the query ok.

I hope this is clearer,

Simon

> Oracles Login Process is relatively slow, there is nothing you can do.
> But 20 seconds are too slow, it seems you have problems with the Oracle
> client setup. Check DNS and TNSNAMES.ORA. Check if
> $ORACLE_HOME/NETWORK/ADMIN/TNSNAMES.ORA (or
> $ORACLE_HOME/NET80/ADMIN/TNSNAMES.ORA) contains the line
> "SQLNET.AUTHENTICATION_SERVICES= (NTS)". If it does, comment it out
> (change the line to "# SQLNET.AUTHENTICATION_SERVICES= (NTS)"). This may
> accelerate your login, unless your Oracle setup uses NTS, in that case,
> you cannot login any more.
>
> You should also think about using DBD::Oracle instead of DBD::ODBC,
> which removes the overhead introduced by the ODBC layer.
>
> Alexander
>
> On 04.05.2006 11:45, simonw@cornfield.org.uk wrote:
>
>>Hi
>>
>>I have been asked to add DBD::ODBC to an Solaris server so that I can
>> copy
>>data from a number of Windows Solid databases to Oracle.
>>
>>Everything works, but the
>>
>>my $Dbh = DBI->connect("DBI:ODBC:","","");
>>
>>is very slow, typically 20s, whilst the rest of the script with
>>(prepare,execute,fetch loop) is much faster.
>>
>>I have checked the unixODBC isql command, and the connection is much
>>faster(<<1s), and the query takes approximately the same time as using
>>DBD::ODBC.
>>
>>Any ideas on the best way to investigate the cause of the slow
>> connection?
>>
>>All the best
>>
>>Simon
>>
>>
>>
>
>
> --
> Alexander Foken
> mailto:alexander@foken.de http://www.foken.de/alexander/
>
>

Re: DBD::ODBC connections are very slow

Hi

I have used truss on a test programme, and for approximately one minute
the following code loop is being repeated

nanosleep(0xFFBED918, 0xFFBED910) (sleeping...)
nanosleep(0xFFBED918, 0xFFBED910) = 0
sigaction(SIGALRM, 0xFFBED7F8, 0xFFBED878) = 0
stat("/opt/utran/apps/unixODBC/SolidFe3.7/solid.ini", 0xFFBED268) = 0
open("/opt/utran/apps/unixODBC/SolidFe3.7/solid.ini", O_RDONLY) = 3
fstat64(3, 0xFFBED5B8) = 0
ioctl(3, TCGETA, 0xFFBED544) Err#25 ENOTTY
read(3, " [ D a t a S o u r c e".., 8192) = 2531
read(3, 0x00281A14, 8192) = 0
llseek(3, 0, SEEK_CUR) = 2531
close(3) = 0
so_socket(1, 2, 0, "", 1) = 3
setsockopt(3, 65535, 4, 0xFFBED514, 4, 1) = 0
setsockopt(3, 65535, 64, 0xFFBED514, 4, 1) = 0
setsockopt(3, 65535, 4097, 0xFFBED4B4, 4, 1) = 0
setsockopt(3, 65535, 4098, 0xFFBED4B4, 4, 1) = 0
connect(3, 0x0027AE84, 110, 1) Err#2 ENOENT
close(3) = 0

After 12 loops, it finds the write entry and completes the connection, no
matter how many entries are in the solid.ini file. Any ideas why this may
be happending?

Thanks

Simon

> Hi
>
> I am sorry if my initial post was unclear.
>
> I have a lot of data held in a number of SOLID instances (on Windows 2000
> servers), and I have been asked to copy some of this data into Oracle (on
> Solaris server). The only to access SOLID remotely is by ODBC, hence on
> my Solaris server I have installed DBD::ODBC, UnixODBC and Solid SDK.
>
> The UnixODBC isql programme connects to teh remote SOLID databases ok and
> performs queries ok.
>
> A Peer DBD::ODBC takes a long time to connect, but executes the query ok.
>
> I hope this is clearer,
>
> Simon
>
>> Oracles Login Process is relatively slow, there is nothing you can do.
>> But 20 seconds are too slow, it seems you have problems with the Oracle
>> client setup. Check DNS and TNSNAMES.ORA. Check if
>> $ORACLE_HOME/NETWORK/ADMIN/TNSNAMES.ORA (or
>> $ORACLE_HOME/NET80/ADMIN/TNSNAMES.ORA) contains the line
>> "SQLNET.AUTHENTICATION_SERVICES= (NTS)". If it does, comment it out
>> (change the line to "# SQLNET.AUTHENTICATION_SERVICES= (NTS)"). This may
>> accelerate your login, unless your Oracle setup uses NTS, in that case,
>> you cannot login any more.
>>
>> You should also think about using DBD::Oracle instead of DBD::ODBC,
>> which removes the overhead introduced by the ODBC layer.
>>
>> Alexander
>>
>> On 04.05.2006 11:45, simonw@cornfield.org.uk wrote:
>>
>>>Hi
>>>
>>>I have been asked to add DBD::ODBC to an Solaris server so that I can
>>> copy
>>>data from a number of Windows Solid databases to Oracle.
>>>
>>>Everything works, but the
>>>
>>>my $Dbh = DBI->connect("DBI:ODBC:","","");
>>>
>>>is very slow, typically 20s, whilst the rest of the script with
>>>(prepare,execute,fetch loop) is much faster.
>>>
>>>I have checked the unixODBC isql command, and the connection is much
>>>faster(<<1s), and the query takes approximately the same time as using
>>>DBD::ODBC.
>>>
>>>Any ideas on the best way to investigate the cause of the slow
>>> connection?
>>>
>>>All the best
>>>
>>>Simon
>>>
>>>
>>>
>>
>>
>> --
>> Alexander Foken
>> mailto:alexander@foken.de http://www.foken.de/alexander/
>>
>>
>
>

RE: DBD::ODBC connections are very slow

Hi

Thanks for your reply. I have already checked DBD::Solid and it does not
support the current architecture of Solid.

Solid currently uses ODBC to communicate between databases and client
processes, and this works well on windows, and with unixODBC with iSQL.

I would rather get perl DBD::ODBC to connect more quickly. Using a windows
box with DBD::ODBC and DBD::Proxy introduces an unwanted extra dependency.

All the best

Simon

Simon Windsor
Eml: simon.windsor@cornfield.org.uk
Tel: 01454 617689
Mob: 07960 321599
-----Original Message-----
From: Alexander Foken [mailto:alexander@foken.de]
Sent: 04 May 2006 16:58
To: simonw@cornfield.org.uk
Subject: Re: DBD::ODBC connections are very slow

On 04.05.2006 14:44, simonw@cornfield.org.uk wrote:

>Hi
>
>I am sorry if my initial post was unclear.
>
>
No problem, I thought it was a standard problem.

>I have a lot of data held in a number of SOLID instances (on Windows 2000
>servers), and I have been asked to copy some of this data into Oracle (on
>Solaris server). The only to access SOLID remotely is by ODBC, hence on
>my Solaris server I have installed DBD::ODBC, UnixODBC and Solid SDK.
>
>
Sorry, but SOLID and ODBC on Unix are bejond my current knowledge.

There is a DBD::Solid on CPAN, but it's v0.20 from 2001. Perhaps it
works with your setup. If it does not work on Solaris, maybe it works on
Windows together with the DBI Proxy. And perhaps, it is faster than your
current ODBC setup. On the other hand, it may take some time to get it
working.

Alexander

--
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.3/331 - Release Date: 03/05/2006


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.3/331 - Release Date: 03/05/2006