?
Solved

JDBC bind variables

Posted on 2006-06-06
11
Medium Priority
?
2,854 Views
Last Modified: 2012-05-05
I have a query that performs differently if i use a bind varible or if I hard code the value. Why does the bind variable query do a full table scan? Could it be related to the casting of the bind variable?


Prepared Statement with BIND VARIABLE

SELECT ROOTCONTEXTID
FROM WMSERVICE  
WHERE PARENTCONTEXTID  = ?  
ORDER BY AUDITTIMESTAMP  DESC

Execution Tree
--------------
Table Spool
  |--Sort(ORDER BY:([WMSERVICE].[AUDITTIMESTAMP] DESC))
       |--Filter(WHERE:(Convert([WMSERVICE].[PARENTCONTEXTID])=[@Param1002]))
            |--Table Scan(OBJECT:([wmaudit].[dbo].[WMSERVICE]))
 
 
Prepared statement without BIND VARIABLE

SELECT ROOTCONTEXTID
FROM WMSERVICE  
WHERE PARENTCONTEXTID  = '2599a1f0f52911d988f9b9c11735d179'
ORDER BY UDITTIMESTAMP  DESC
 
Execution Tree
--------------
Table Spool
  |--Sort(ORDER BY:([WMSERVICE].[AUDITTIMESTAMP] DESC))
       |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([wmaudit].[dbo].[WMSERVICE]))
            |--Index Seek(OBJECT:([wmaudit].[dbo].[WMSERVICE].[IDX_SVC_PCID]), SEEK:([WMSERVICE].[PARENTCONTEXTID]='2599a1f0f52911d988f9b9c11735d179') ORDERED FORWARD)
0
Comment
Question by:mbevilacqua
  • 4
  • 4
9 Comments
 

Author Comment

by:mbevilacqua
ID: 16843838
I have spent an hour reviewing the links and have found that they do not explain the issue I am having.

How about being a more specific in answering this question? Where in this two links does it provide a solution for my issue of different SQL execution paths?
0
 
LVL 12

Expert Comment

by:Giant2
ID: 16849978
Prepared Statement are normally used with bind variable because they are interpreted like Views.
If you specify the value instead of using the bind variable, this performance feature goes down.
I think you see like these times:
Prepared Statement with bind variable: time 100
Prepared Statement without bind variable: time 130
Normal statement: time 130
(the number are relative to your tables population)
The idea is this: if you use Prepared Statement without bind variable the performance degrade like you use a normal Statement.

Hope this could help you.
Bye, Giant.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:mbevilacqua
ID: 16852707
To reiterate, I am not asking about the benefits and drawbacks of using bind variables.

NOTE: The bind variable query does a full table scan, while the non-bind variable uses indexes. In this case, the use of the bind variable is causing a SIGNIFICATION performance issue in this query. The bind variable query takes 1 minute, while the non-bind variable takes 1 second.

I am asking for feedback on why we think this particular query performs differently when using bind variables versus not using bind variables.
0
 
LVL 12

Expert Comment

by:Giant2
ID: 16859864
Is there an index over your table?
If an index is present this is used when you not specify the bind variable. I think this could be your case.
Bye, Giant.
0
 

Author Comment

by:mbevilacqua
ID: 16862655

There is a index on the table and it should be used if using a bind variable or not using a bind variable. My question is why is the index not being used when using a bind variable.
0
 
LVL 12

Expert Comment

by:Giant2
ID: 16869104
>My question is why is the index not being used when using a bind variable.
If a bind variable is used any index on the table are "ignored" because the query is seen like a View.
Without bind variable the query as no precompile, so all the feature for performing of the DB are used (index, keys, ...)

Bye, Giant.
0
 

Author Comment

by:mbevilacqua
ID: 16870807

I disagree Giant, are you sure you understand bind variables? Queries that use bind variables use indexes. Please read this link entitled Bind Variables - The Key to Application Performance:

http://www.akadia.com/services/ora_bind_variables.html
0
 
LVL 12

Accepted Solution

by:
Giant2 earned 2000 total points
ID: 16884652
The use I see is:
>WHERE PARENTCONTEXTID  = ?  
>WHERE PARENTCONTEXTID  = '2599a1f0f52911d988f9b9c11735d179'

This binding is done by the PreparedStatement class, not by the Oracle environment.
So the select instructions are sen tboth to the Oracle engine in the same manner. The Oracle engine could not see it is a bind, so it try to compile normally (in the manner I told before).
Try to encapsulate your select statement in a stored procedure, for example. in this manner the Oracle engine is forced to see it like a bind and execute it in the right manner.
(Warning. This is my opinion)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Suggested Courses
Course of the Month16 days, 12 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question