0 Replies Latest reply on May 25, 2016 9:40 AM by llicht RSS
    llicht Newbie

    Controlling fetch size of oracle sql connection

    Hi,

     

    We have a database Thing that connects to an oracle database via:

    JDBC Driver Class Name: oracle.jdbc.driver.OracleDriver

    JDBC Connection String: jdbc:oracle:thin:<....>

     

    By default, the fetch size used for this connection is very low, causing some queries that are meant to allow the user to export a large volume of data (e.g. 5000 rows) take 15 seconds (only 0.3 seconds of which are spent doing the query in the database).

    If I set a larger fetch size (e.g. 5000), the query can complete in as little as 0.7 seconds.

     

    However, I searched and haven't found a way to set the fetch size from Thingworx.

    Does anyone know how this can be done?

     

    (I found some connection strings online (in jdbc-fetch-size and extremely large dataset (35... | Oracle Community ) that would suggest you could add ";jdbc-fetch-size=<value>;" to the connection string in order to set the fetch size (at least for a jdbc:sqlserver connection string, so it may not be applicable here), but when I tried it on my jdbc:oracle:thin string in Thingworx, I got the following error:

    IO Error: Invalid connection string format, a valid format is: "host:port:sid")

     

    (JDBC documentation about fetch size: Result Set)

     

    Thanks.