Click here to Skip to main content
12,948,567 members (63,031 online)
Click here to Skip to main content
Add your own
alternative version


4 bookmarked
Posted 19 Apr 2011

Always use Binding Variables in SQL queries

, 19 Apr 2011 CPOL
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
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 @[^]


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


About the Author

Amit Kumar Tiwari
Technical Lead
United States United States
I am a developer in Microsoft Technologies like .NET, SharePoint etc.

You may also be interested in...


Comments and Discussions

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

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170524.1 | Last Updated 20 Apr 2011
Article Copyright 2011 by Amit Kumar Tiwari
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid