a time difference between JDBC and ODBC.
separk
Community Edition User
Hello,
While supplying our solution to a company using Vertica DB,
The following problems have occurred and we contact you.
== Base information ==
DB Server Version: Vertica Analytic Database v8.1.1-8
Vertica ODBC Client Version: 8.1.1.16, 9.2.1.2
Our solution uses ODBC to connect to Vertica DB.
By the way, when using 8.1.1.16 ODBC, the following error occurs about 3 out of 10 queries.
Error: # 20, [Vertica][VerticaDSII] (20) An error occurred during query execution: message do not agree with length in message type "T"
Error: # 10, [Vertica][VerticaDSII] (10) An error occurred during query preparation:
The above error may also appear upon initial connection,
It may also appear in the normal Select statement.
So, we change the ODBC version to 9.0.1, 8.0.1, etc..
And, when using 9.2.1.2, The above errors appear less.
but in the official Vertica compatibility table It is recommended to use version 8.1 or lower when connecting to 8.1 Server.
So, I would like to check if the 8.1.1 server can use 9.2.1 ODBC.
Or, Please tell me exactly what ODBC is right for the Vertica Analytic Database v8.1.1-8.
And, for a general query, (SELECT A INNER JOIN B ON A.a=B.b ... )
When executing the same SQL statement with Aqua Data Studio using JDBC, it takes 2 seconds,
Please tell us if there is a reason why it takes 80 seconds to use 8.1.1 ODBC or 9.2.1 ODBC or etc. .
The number of data in the table is 400 million for A and 30 million for B.
On our solution source, when I try to log before calling ODBC function SQLExecute (stmt) and log after call, the time difference is more than 80 seconds.
Please let me know why there is a time difference between JDBC and ODBC.
Thank you.
Answers
Hi, Vertica JDBC and ODBC drivers are backward compatible to server version 7.1.x, so it is recommended to use the latest available client driver. Please see the backward compatibility matrix at https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/ConnectingToVertica/ClientDriverMisc/ClientDriverAndServerVersionCompatibility.htm
It is strongly recommended to test your application using the latest driver so we can troubleshoot and offer a patch as needed. If possible, please open (or have the user open) a ticket with Vertica support so we can collect logs and configurations.
Hi Bryan_H,
Thanks for the comment.
However, as shown in the compatibility table you sent me,
The latest ODBC driver that supports Server 8.1.x is 8.1.x Client version. ( Forward Compatibility table)
Nevertheless, on Vertica Analytic Database v8.1.1-8 server, I tried all ODBC versions from 7.1.x to 9.3.x, but the time is different.
ODBC takes longer.
some ODBC client is repeating the error shown above. ( Error #10, Error #20)
In addition to the general ones in ConnectionString,is there any value to add or change?
I'll need more info, since I will need to replicate a query close to yours. Beyond the driver, are there any other changes in the connection - is the same user used each time from the same machine? How many rows does the query return? A quick way to check whether the issue is how fast the rows are returned to the client is to wrap the query in a count, e.g. SELECT COUNT(*) FROM (query) q1; and see whether the timing is similar when returning only the count.
Beyond the driver, are there any other changes in the connection - is the same user used each time from the same machine?
- Tested with the same users and privileges on the same computer, the speed appears the same as other PCs.
How many rows does the query return?
- The total count is 7288695 rows.
A quick way to check whether the issue is how fast the rows are returned to the client is to wrap the query in a count, e.g. SELECT COUNT(*) FROM (query) q1;
and see whether the timing is similar when returning only the count.
- 76 seconds when running only q1, It took 1.8 seconds to execute "select count(*) from (q1)" .
Thanks for the answer.