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.
