JDBC Adapter Service taking too long to retrieve data

, ,
1

Hi,
I have created a JDBC connection to a SQL database, using the typical connection settings:
Transaction Type NO_TRANSACTION
DataSource Class com.microsoft.jdbcx.sqlserver.SQLServerDataSource
portNumber 1433
networkProtocol
Other Properties selectedMethod=Cursor
Enable Connection Pooling true
Minimum Pool Size 0
Maximum Pool Size 10
Pool Increment Size 1
Block Timeout (msec) 30000
Expire Timeout (msec) 30000
Startup Retry Count 2
Startup Backoff Timeout (sec) 10

I then have an adapter service which has 1 input parameter and should retrieve anywhere from zero to 10 (max) records. It’s taking almost 20 seconds to retrieve the data.

If I run the same Select statement in MS Query Analyzer, it barely takes a second.

Any thoughts?

Thanks,
Francine

2

Francine,

Dont know the reason why it is taking too long but try changing Minimum Pool size from 0 to 1 and leave the Other Properties field blank.

ramesh.

3

Francine,

couple of suggestions:

  1. Set SendStringParametersAsUnicode=false in other properties.
    MS driver is an OEM of DataDirect Driver. You can find the reason for using the above mentioned parameter at the following link:
    [url]http://knowledgebase2.datadirect.com/kbase.nsf/26536a530e20a22b85256e550079afc2/9a81ecba03d1528a85256c2100557987?OpenDocument&Highlight=0,_eedimsp3jehp6irj7e1gn4obdclq6asjjc5pnarj9cdnm8p9tcpgmosr5_[/url]

  2. Try the IP Address of the Database instead of the hostname. Perhaps the hostname resolution is slow.

If the problem still remains, get thread dumps, which will show where your service is waiting. If it’s waiting for response from the database, then check with your DBA.

Hope this helps.

Regards