webMethods

webMethods

Join this online group to communicate across IBM product users and experts by sharing advice and best practices with peers and staying up to date regarding product enhancements.

 View Only
Expand all | Collapse all

JDBC Adapter Service taking too long to retrieve data

  • 1.  JDBC Adapter Service taking too long to retrieve data

    Posted Wed September 21, 2005 09:13 PM

    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


    #Integration-Server-and-ESB
    #Adapters-and-E-Standards
    #webMethods


  • 2.  RE: JDBC Adapter Service taking too long to retrieve data

    Posted Wed September 21, 2005 09:56 PM

    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.


    #webMethods
    #Adapters-and-E-Standards
    #Integration-Server-and-ESB


  • 3.  RE: JDBC Adapter Service taking too long to retrieve data

    Posted Thu September 22, 2005 12:02 AM

    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


    #Adapters-and-E-Standards
    #Integration-Server-and-ESB
    #webMethods