Doing a query with string vs bind variables.

We have a PowerBuilder application where we started noticing the performance of a query is worse if you do it with bind variables then if you do the same query as a string. We made a small example and sent to Sybase (makers of PB). We noticed that the bind variables query will not use the same index as the string query, even though the two queries are exactly the same.

Example

String query:
  SELECT DISTINCT "ORDER_HEADER"."ORDER_TYPE" ,
"ORDER_HEADER"."ORDER_NUMBER" ,
"RELATION_MASTER"."NAME" ,
"ORDER_HEADER"."RELATION_CODE" ,
"ORDER_HEADER"."WO" ,
"ORDER_HEADER"."SHIPPED_FROM_LOCATION" ,
"ORDER_HEADER"."AUTHORIZATION" ,
"ORDER_HEADER"."ACCESS_CONTROL" ,
"ORDER_HEADER"."RELEASE_FOR_AUTHORIZATION" ,
"ORDER_HEADER"."TOTAL_COST" ,
"ORDER_HEADER"."CURRENCY" , "ORDER_HEADER"."PRIORITY"
FROM "ORDER_HEADER" , "RELATION_MASTER" WHERE ( "ORDER_HEADER"."RELATION_CODE" =
"RELATION_MASTER"."RELATION_CODE" (+)) and

( ( "ORDER_HEADER"."STATUS" = 'OPEN' ) and
("ORDER_HEADER"."ACCESS_CONTROL" = 'ADM' or
( "ORDER_HEADER"."ACCESS_CONTROL" = 'ALL') ) and
( "ORDER_HEADER"."ORDER_TYPE" = 'RO' ) )

Bind variable query:
  SELECT DISTINCT "ORDER_HEADER"."ORDER_TYPE" ,
"ORDER_HEADER"."ORDER_NUMBER" ,
"RELATION_MASTER"."NAME" ,
"ORDER_HEADER"."RELATION_CODE" ,
"ORDER_HEADER"."WO" ,
"ORDER_HEADER"."SHIPPED_FROM_LOCATION" ,
"ORDER_HEADER"."AUTHORIZATION" ,
"ORDER_HEADER"."ACCESS_CONTROL" ,
"ORDER_HEADER"."RELEASE_FOR_AUTHORIZATION" ,
"ORDER_HEADER"."TOTAL_COST" ,
"ORDER_HEADER"."CURRENCY" , "ORDER_HEADER"."PRIORITY"
FROM "ORDER_HEADER" , "RELATION_MASTER" WHERE ( "ORDER_HEADER"."RELATION_CODE" =
"RELATION_MASTER"."RELATION_CODE" (+)) and

( ( "ORDER_HEADER"."STATUS" = 'OPEN' ) and
("ORDER_HEADER"."ACCESS_CONTROL" = :s_user or
( "ORDER_HEADER"."ACCESS_CONTROL" = 'ALL') ) and
( "ORDER_HEADER"."ORDER_TYPE" = :s_order_type ) )

In the Bind-variable query, we pass the same parameters as we are hardcoding  in the equivalent string version.

Why would the bind variable query use a different index, and why it would it be a "worse" index then the one the string-query uses?

The puzzling part is that if I take the Bind-variable query and run it in Oracle SQL Developer (it will prompt me for the values when run) it will also perform well... It is only in running through Sybase PowerBuilder that the bind-variable query runs slow. Sybase stated they have no control over which index the database will use.

Another note... the above example is only one case... this same situation is happening with many queries throughout our application.

any thoughts? Received on Mon Sep 17 2007 - 15:42:15 CDT