Select/Substring Processing Question

, , , , ,
1

We have an sql that looks like this:

select * from TABLE_Col where TABLE_Col= ‘95’ and SUBSTRING (TABLE_Col.KEY_Col, 5, 8) = ‘ZZZZ’;.

In the jdbc server log we see the following trace lines:

Mar 08 08:42:10.424 2013@LM_DEBUG @ UPTIME:(00 09:09:54.491 ) IP:(172.27.58.208 ) TID:(4500 )==> 2012(W): SQL: select * from TABLE_Col where TABLE_Col= ‘95’ and SUBSTRING (TABLE_Col.KEY_Col, 5, 8) = ‘ZZZZ’;.
Mar 08 08:42:10.424 2013@LM_DEBUG @ UPTIME:(00 09:09:54.491 ) IP:(172.27.58.208 ) TID:(4500 )==> 2013(W): Begin Executing Query.
Mar 08 08:42:10.470 2013@LM_DEBUG @ UPTIME:(00 09:09:54.537 ) IP:(172.27.58.208 ) TID:(4500 )==> 2014(W): Query Execution Complete
Mar 08 08:42:10.502 2013@LM_DEBUG @ UPTIME:(00 09:09:54.569 ) IP:(172.27.58.208 ) TID:(4500 )==> 2018(W): Processing CDO: (StatementGetRows).
Mar 08 08:42:12.236 2013@LM_DEBUG @ UPTIME:(00 09:09:56.303 ) IP:(172.27.58.208 ) TID:(4500 )==> 2015(W): Row Fetch Completed - End of Recordset. Rows Retrieved (10).

Please advise as to which step does the substring function is processed:

begin query execution - query execution completed or

StetementsGetRows - Rows Fetch Completed.

We need to study the perfromance of our application.

Thanks

2

Hi Min,

I would recommend that you open a support issue - so we can collect the relevant metadata to answer your question.

The query does not seem to make sense unless your table called “TABLE_Col” also has a column called “TABLE_Col”.

In general, the way you are using SUBSTRING disqualifies that part of the expression for optimization.

Assuming the clause in the original query was : SUBSTRING (TABLE_Col.KEY_Col, 5, 4)
It looks like you are trying to filter on a portion of the key without specifying the starting segment of the key.

Its not really possible to take advantage of the descriptor in this way. Are the first 5 bytes of KEY_Col unknown?

3

Larry,

Yes, the table name is table_col and the column name is also called table_col. It is a legacy system that has been around a long time. Not the best at this point but It is what it is.

Yes, the 1st part of the key_col is unknown. I am not looking into using key_col as an index. The table_col column is an index and that is fine in this case to limit the number of rows. If I want to use key_col as an index then I can build a sub-index for it. No problem here.

My question is where the substring is executed: o the jdbc server or the sql gateway.

If jdbc server then from the log I gather, I like to know where the time spent on te substring is recorded: between begin query execution - excution completed or between statement getrows and fetch rows completed.

We are trying to explain what we see in the jdbc server log.

Thanks.

4

Min,

The substring filtering happens in the JDBC server. In the log, it would be in between Processing CDO: (StatementGetRows) and Row Fetch Completed - End of Recordset. If the criteria was a simple equal comparison, it would occur in the Data Server (what you are calling the SQL Gateway).

You can shift the processing from the JDBC server to the Data Server by using a like clause instead of substring - like this:
TABLE_Col like “_____ZZZZ%”

This should also give you better performance.

5

Larry,

One last question: the StatementGetRows checkpoint is written when the jdbc server is retrieving the rows from the data server rather than the application server fetching the rows from the jdbc server, right?

Thanks for your ifnormation.
Min

6

Min,

No, the StatementGetRows is written to the log when the application starts retrieving the rows from the JDBC server.