Click here to Skip to main content
Click here to Skip to main content
Go to top

Always use Binding Variables in SQL queries

, 19 Apr 2011
Rate this:
Please Sign up or sign in to vote.
Boost application speed and performance
While writing queries in SQL always prefer using the Binding Variables. Here's why:
 
Everytime a query is executed, it is first checked into the Shared Pool to see whether the query was executed before or not. If yes, then its execution plan is used again to execute the new query. If no, Hard Parse is done by the database. The query is parsed, working out the various execution paths and coming up with an optimal access plan before it can be executed. Hard parsing is very CPU intensive, and involves obtaining latches on key shared memory areas.
 
So, lets take an example:
     select * from table1 where salary = 2000
 
Now if the value 2000 changes everytime with input from user, the query will never be unique and will be hard parsed everytime, generating extra CPU burden.
 
Solution: Binding Variables
Example:
     select * from table1 where salary = :salary
 
Now this makes the statement unique everytime and just the values change in it, reducing the Hard Parse overhead. Every reference to a PL/SQL variable is in fact a binding variable.
 

For more details see the link @ http://www.akadia.com/services/ora_bind_variables.html[^]

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Amit Kumar Tiwari
Technical Lead
India India
I am module lead with experience in Microsoft Technologies like .NET, SharePoint etc.
Follow on   Google+

Comments and Discussions

 
GeneralI see you've tagged this as SQL Server, though I don't think... PinprotectorAspDotNetDev28-Apr-11 14:20 
GeneralThanks for the tip Amit! PinmemberPranav Ainavolu26-Apr-11 1:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 20 Apr 2011
Article Copyright 2011 by Amit Kumar Tiwari
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid