Connect to Oracle extremely slow

[mina]

Hello,
We plan to port our databases from SqlBase to Oracle 11.x
and tried the connect and select performance of TD and SQLTalk.
Through SQLPlus, connect and query takes about 0.1-0.2 sec.
Both SQLTalk and TD need the same time as SQLPlus for queries
but it takes over 15sec to connect !!!

sql.ini entries:
[win32client.dll]
comdll=sqlora32
[oragtwy]
remotedbname=ora_test,@TNS:ora_test
fetchrow=20
substitute=SYSSQL.,
substitute=",
longbuffer=32000

sqlnet.ora:
NAMES.DIRECTORY_PATH= TNSNAMES

Anybody who solved similiar connect delays?
Thanks in advance

[Jeff Luther]

So, just to verify: SqlConnect( hSql ) is taking ~15 seconds to return in a simple test ? if so, how long does the second connect take:

Simple test:
Set ... vars
Call SqlConnect()

Call SqlConnect()

In general, the first connect does take longer -- EXE + DLLs etc. to load, lookup to Oracle client, router buffers to allocate, some internal housekeeping in other words -- but subsequent connects to same DB should be fast(er). So I'd be curious what that 2nd connect takes for you.

Point of comparison: I see to connect to Unify's Oracle11g server at the office from my WinXP machine with 4 gig and lots running, connecting over a VPN to our internal network (office is some ~500 miles away) and using TD v5.2 SQLTalk, takes 12 seconds. I just Ctrl+Enter'd that connect string again in SQLTalk for a "connect 2" and it was almost instantaneous. (I didn't have time to pick up my stopwatch!)

[mina]

No significant differences measured between 1st and 2nd connect
but the results of our tests are interesting:

2 WinXP machines with the latest OraXE 11g installed local,
different TD/SQLTalk Versions and always the same sql.ini as described above.
Time to connect 11-15 seconds.

Same machines, same software with modified sql.ini
without the TNS entry (remotedbname=ora_test,@ora_test)
Time to connect 2-3 seconds !!

Same test environment, Ora client installed and OraEnterprise 11g Win-Server.
Time to connect with TNS entry 1-2 seconds and without TNS 0.9-1sec.

[Jeff Luther]

without the TNS entry (remotedbname=ora_test,@ora_test)
Time to connect 2-3 seconds !!

That is interesting! Historically -- so I've heard -- using the @tns: like:
remotedbname=ora10g,@tns:ora10db

was the correct syntax. I just tested and find with or without the "tns:" part after the @ remote conn. with SQLTalk to Oracle 11g ~7.5 seconds either way.

At least you've shown in your testing how to reduce the connect time.

[SHA]

Hello,

i had the same problem.

The solution was to add the domain name.

before (slow connect)

Code: Select all

[oragtwy]
remotedbname=testdb,@testdb

after (faster connect)

Code: Select all

[oragtwy]
remotedbname=testdb,@testdb.domain.tld

SHA

[DJohn]

I have the same problem :

Gupta Connection first time ca 20 sec. , second time immediatly

I have tested the solution with the Domainname in the connectstring ( @test.domainname.de ) .... but no improvement

Can anyone Help ?

[jmcnamara]

We use OLEDB (does not use sql.ini). The first SqlCreateSession(...) call takes 3-5 seconds. Subsequent SqlCreateStatement(...) calls are instantaneous. The downside for you is changing all your SqlConnect() calls to SqlCreateStatement(...)