Support Questions

Find answers, ask questions, and share your expertise

Phoenix ODBC driver strips out "hint" part of "Select" statements

Explorer

It is very important for us to use "hint" in "Select" statements to improve the query performance, like using the secondary indexes. For large tables, it is not possibles/practical to always create fully covered secondary indexes. Thus, we rely on using "hint" to make sure secondary indexes are used.

However, our test showed that the performance through ODBC is much worse than through JDBC thin driver, for the same "select" statements with "hint" to use the secondary indexes. Since they both go through the Phoenix Query Server (PQS), the PQS log was analyzed and it turned out that the "hint" part does not exist when the "select" requests come from ODBC driver. Requests from JDBC thin driver keep the "hint" part.

After turning on the ODBC driver log, it is confirmed that the "hint" part is stripped out by the ODBC driver. Here is the ODBC driver log. JSON serialization format is used for readability, using PROTOBUF behaves the same.

From log, it can be seen that, a "select" statement like this:

select /*+ INDEX(c.more_xref_cad more_xref_acct_idx) */ * from c.more_xref_cad where cad_acct_id = 219980018

is stripped out of "hint" part when ODBC sends it out:

SELECT * FROM c.more_xref_cad WHERE cad_acct_id = 219980018

====ODBC Log ===========

Mar 20 16:15:00.601 INFO 6380 Statement::SQLSetStmtAttrW: Attribute: SQL_ATTR_MAX_ROWS (1) ----Commnents: the original query passed in has "hint"

Mar 20 16:15:00.602 INFO 6380 StatementState::InternalPrepare: Preparing query: select /*+ INDEX(c.more_xref_cad more_xref_acct_idx) */ * from c.more_xref_cad where cad_acct_id = 219980018

Mar 20 16:15:00.602 DEBUG 6380 RESTAction::HMDebugCallback: Infor type: CURLINFO_TEXT

.....

Mar 20 16:15:00.802 DEBUG 6380 RESTAction::HMDebugCallback: Info data:

Connected to localhost (127.0.0.1) port 8765 (#1)

Mar 20 16:15:00.802 DEBUG 6380 RESTAction::HMDebugCallback: Infor type: CURLINFO_HEADER_OUT

Mar 20 16:15:00.802 DEBUG 6380 RESTAction::HMDebugCallback: Info data:

POST / HTTP/1.1

Host: localhost:8765

Content-Type: application/octet-stream

Accept: text/html, image/gif, image/jpeg, *; q=.2, */*; q=.2

User-Agent: Phoenix ODBC

Connection: keep-alive

Content-Length: 160

Mar 20 16:15:00.803 DEBUG 6380 RESTAction::HMDebugCallback: Infor type: CURLINFO_DATA_OUT ----Comments: the query generated and submited to PQS has no "hint" part.

Mar 20 16:15:00.803 DEBUG 6380 RESTAction::HMDebugCallback: Info data:

{"request":"prepare","connectionId":"2166b30f-1bf8-1f9d-309e-4009877a1a62","sql":"SELECT * FROM c.more_xref_cad WHERE cad_acct_id = 219980018","maxRowCount":-1}

Mar 20 16:15:00.803 DEBUG 6380 RESTAction::HMDebugCallback: Infor type: CURLINFO_TEXT

Mar 20 16:15:00.803 DEBUG 6380 RESTAction::HMDebugCallback: Info data:

upload completely sent off: 160 out of 160 bytes

=====End of ODBC log=====

Does anyone know how to make ODBC driver keep the "hint" in the "select" statements? @Josh Elser, please help!

Thanks.

1 ACCEPTED SOLUTION

Sounds like a bug to me. Good debugging. Please reach out to support for assistance on getting this one fixed.

View solution in original post

2 REPLIES 2

Sounds like a bug to me. Good debugging. Please reach out to support for assistance on getting this one fixed.

Explorer

Thanks, Josh. I opened a case with the Hortonworks Support.

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.