AnsweredAssumed Answered

JDBC query is very slow - why?

Question asked by cymerman on Nov 7, 2017
Latest reply on Nov 7, 2017 by pmartin

Hi, i have a question, why queries by JDBC are so slow?

 

i have simple query:

 

SELECT TOP 10 
FROM [TestDev].[EventFrame].[EventFrame]
WHERE Name LIKE 'Alarm 1453%'
ORDER BY StartTime DESC

do this simple query takes over 3 minutes, when same query through PI SQL Commander gives me result in 1 sec...

 

my JDBC url link

 

jdbc:pi://my-osi-server/Data Source=my-data-source;Integrated Security=SSPI;

 

properties of connection:

 

plist.put("ProtocolOrder", "Https/Soap:5461,NetTcp:5462");
plist.put("LogConsole", "True");
plist.put("user", LOGIN);
plist.put("password", PASSWORD);
plist.put("TrustedConnection","True");
plist.put("LogLevel", "0");

 

i tried to use different protocol, but it didnt help

 

when i would turn on logs i see that most time is spent on connection and disconnection:

 

lis 07, 2017 11:24:03 AM com.osisoft.jdbc.DriverExtension <init>

...

lis 07, 2017 11:24:04 AM com.osisoft.rdsa.NativeRDSA create

INFO: Trying to connect to https://my-osi-server:5461/DataAccessServer/Query

lis 07, 2017 11:25:29 AM com.osisoft.jdbc.ConnectionImpl <init>

FINEST: Successfully connected to https://my-osi-server:5461/DataAccessServer/Query

lis 07, 2017 11:26:03 AM com.osisoft.jdbc.ConnectionImpl <init>

FINEST: Successfully connected to the data source using "Provider=PIOLEDBENT; Session ID=-1;Data Source=MySource;Integrated Security=SSPI;Log File= ; Time Zone=local; Always Return Rowset=false;"

lis 07, 2017 11:26:03 AM com.osisoft.jdbc.PreparedStatementImpl create

FINER: Created PreparedStatement

SELECT TOP 10 *    FROM [TestDev].[EventFrame].[EventFrame]    WHERE Name LIKE 'Alarm 1453%'    ORDER BY StartTime DESC

lis 07, 2017 11:26:03 AM com.osisoft.jdbc.PreparedStatementImpl executeQuery

...

lis 07, 2017 11:26:20 AM com.osisoft.jdbc.ResultSetImpl <init>

FINEST: com.osisoft.jdbc.ResultSetImpl@123a213 created

lis 07, 2017 11:26:20 AM com.osisoft.jdbc.ResultSetImpl close

FINEST: com.osisoft.jdbc.ResultSetImpl@123a213 closed

lis 07, 2017 11:27:15 AM com.osisoft.jdbc.DriverExtension closeLogging

 

do you have some ideas what i could do to make response time same as in PI SQL Commander?

Outcomes