Connect to Oracle extremely slow

General discussion forum about all databases other than SqlBase.
mina
Finland
Posts: 22
Joined: 21 Apr 2018, 07:36
Location: Finland

Connect to Oracle extremely slow

Post by mina » 26 Jan 2012, 16:29

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

Re: Connect to Oracle extremely slow

Post by Jeff Luther » 07 Feb 2012, 01:37

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
Finland
Posts: 22
Joined: 21 Apr 2018, 07:36
Location: Finland

Re: Connect to Oracle extremely slow

Post by mina » 10 Feb 2012, 19:04

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

Re: Connect to Oracle extremely slow

Post by Jeff Luther » 14 Feb 2012, 22:39

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

Re: Connect to Oracle extremely slow

Post by SHA » 12 Sep 2012, 14:28

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

Re: Connect to Oracle extremely slow

Post by DJohn » 05 Jun 2013, 08:58

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

Re: Connect to Oracle extremely slow

Post by jmcnamara » 05 Jun 2013, 16:39

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(...)

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests