Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> 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
![]() |
![]() |