Home » RDBMS Server » Performance Tuning » Using Bind Variables
Using Bind Variables [message #205722] Mon, 27 November 2006 04:33 Go to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Hi All,
In almost all books including Tom Kyte, everyone has emphasized on the usage of Bind Variables.

For example:
It is not advised to use
   select * from emp where empno=123;

However it is advised to use
   select * from emp where empno = :empno;


However in a practical scenario consider a web application, I would use a connection object say db, a recordset say rs and variables say empNo and strQry.

Example:

empNo = 123;

strQry = "SELECT * FROM EMP WHERE EMPNO ="&empNo
rs.open strQry, db


Using this recordset (rs), I'll perform further operations.

I would like to know how the query will be treated? A query with a bind variable, or a query without a bind variable.

Thanks in advance
Anand
Re: Using Bind Variables [message #205735 is a reply to message #205722] Mon, 27 November 2006 04:59 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
In that case your query is NOT using bind variables and performs HARD parse for each execution.

You have to use COMMAND object to enable bind variables usage.

HTH.
Re: Using Bind Variables [message #205736 is a reply to message #205722] Mon, 27 November 2006 05:02 Go to previous messageGo to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Hi Michael,
Thanks for your post. Can you elaborate a little using some examples if possible?

Regards,
Anand
Re: Using Bind Variables [message #205757 is a reply to message #205722] Mon, 27 November 2006 07:06 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try following:


With cmd
.CommandText = "SELECT * FROM EMP WHERE EMP_ID = ?"
.CommandType = adCmdText
.ActiveConnection = cnn
.Parameters.Append .CreateParameter(, adDouble, adParamInput )
End With
cmd(0) = 123
SET rs = cmd.execute

HTH
Re: Using Bind Variables [message #205785 is a reply to message #205736] Mon, 27 November 2006 09:51 Go to previous message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Thanks for the explanation.

Regards,
Anand
Previous Topic: Query Tuning (Performance)
Next Topic: # VERY URGENT # -Monitoring memory utilization in oracle 9i
Goto Forum:
  


Current Time: Sun Dec 11 00:16:43 CST 2016

Total time taken to generate the page: 0.12764 seconds