JDBC Driver Parameter Settings for faster performance

Ram_Prasad

Causing JDBC driver delay in reading database metadata or resulset etc. Query execution is fast when we check in database end but getting into app through drive is delaying. Any one help in setting any parameters to get faster response using JDBC Driver. (I'm using vertica-jdk5-6.1.3-0.jar)

[Deleted User]

I am not sure what do you mean by "delaying".

Can you provide more detail surrounding your issue? Like the query, result set size, code snippet to show how your application reads the result set? Thanks.

Ram_Prasad

Hi Ping Xuan,

Thank you for your response. I am trying to read metadata of physical table through JDBC. We are using tomcat connection pooling with parameters maxActive="500" maxIdle="20" maxWait="500" for more than 600 users. When we use the following any one of these two code snippet result is very slow (paste the following into some textpad). One more thing i.e which is faster JDBC/ODBC please suggest me then i will try on that also. we observed the issue on concurrency when we do load test for 50 users, 100 users it is giving very poor performance.

Tpye 1:DatabaseMetaData metaData = con.getMetaData();ResultSet rs = metaData.getColumns(con.getCatalog(), null, tableName, null);while (rs.next()){ columnName = rs.getString("COLUMN_NAME").toUpperCase(); columns.add(columnName);}
Type 2:PreparedStatement psmt = con.prepareStatement(viewSql.toString());ResultSetMetaData rsm = psmt.getMetaData();if(null != rsm){ for(int c=0;c<rsm.getColumnCount();c++){ columnName = rsm.getColumnName(c+1).toUpperCase(); columns.add(columnName); }}

-Thanks

Daniel_Leybovic

1. Why you are sure that delay comes from Vertica and not from TomCat or from connection configuration (pooling, connection life cycle, servlet life cycle)?

2. What is delay between when "statement executed in Java" and "Vertica got query" (start time when java executes a statement and start_time when query started in Vertica)?

3. Do you use in additional frameworks like Spring, Hibernate, dbcp?

4. Is it stateless or stateful implementation? (TomCat so I believe its stateful, but it also depends on web-framework - Wicket, Click).
If it stateless, so may be delay come from servlet initialization?

Ram_Prasad

In my conversation I haven't said its vertica or tomcat. I want to know what is making delay. I'm suspecting jar file not the vertica db because there is no other code in between. Why I have pasted my connection pooling parameters is it may be wrong or incorrect parameters performance wise so please suggest me in that angle also. Which will be best configuration for tomcat connection pooling. if statement executed in java is taking more time than what vertica got query execution then what should I do, what could be the reasons. Because no other code is there in between executing query and getting resultset. we are using JNDI for connection pooling. Here one point to highlight previously we used vertica-jdk5-6.1.1-0.jar but now we are using vertica-jdk5-6.1.3-0.jar which we got so much improvement in performance compare to older. New jar is faster than old jar. I want to give you one more input, if we start server it takes 4 to 5 min in night time which no one access you can say offline. but if we start the same server in busy time like morning time it takes 15 to 18min. What can I conclude on this?

Daniel_Leybovic

Im just trying to "trace" a path between client and Vertica.
First we need to understand where delay occurs. You need to profile a path: take time when query sent by client, take time when Vertica got it and backward. From Vertica's DC tables you can know query execution time, IO seeks, CPU load and Network metrics.

>> we observed the issue on concurrency when we do load test for 50 users, 100 users it is giving very poor performance.
Its too hard to answer - bottleneck can be in IO, Network, how data inserted (copy/insert) in the "intermediate java layer" problem.