performance of parameterized query

Question

0.00/5 (No votes)

See more:

SQL-server-2000

SQL-Server-2005

, +

Hi all

SQL

SELECT EMP_ID,EMP_NAME,EMP_DOJ,EMP_SALARY FROM EMP WHERE EMP_ID = 76562;

This the general approach.

We can write this in parametrized way i.e.

SQL

SELECT EMP_ID,EMP_NAME,EMP_DOJ,EMP_SALARY FROM EMP WHERE EMP_ID = @EMPID;
mycommand.Parameters.Add("@EMPID",76562);

Which one is better and why?
and which one is faster(take less time to execute)
I am using MYSQL database.
Thanks in advance.

Posted 25-Apr-12 0:35am

bhagirathimfs

Updated 25-Apr-12 0:57am

v4

Add a Solution

3 solutions

Solution 1

Hey,

If you use this query into a Sql SP-

Then would be play better perform because using into SP it works a pre compile query.

We just call SP with param each time.

Posted 25-Apr-12 0:40am

Arav Pradeep Gupta

Comments

bhagirathimfs 25-Apr-12 6:43am

Sry didn't get anything.
can u please elaborately explain this.

Thanks in advance

Solution 2

the first approach is better. Please refer to http://technet.microsoft.com/library/cc966419[^] then search for a subtitle 'Avoid use of local variables in queries'

hth,
Foxyland

Posted 25-Apr-12 0:41am

foxyland

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

uspatel · Accepted Answer · 2012-04-25T00:54:00

Solution 3

The perforamnce of query is depend on Exceution Plan.
I think to write parametrized query will be good practice because it helps to prevent from SQL Injection 1
Sql Injection 2
if you are taking input from user.

Posted 25-Apr-12 0:54am

uspatel

Comments

bhagirathimfs 25-Apr-12 6:59am

Thanks Uma Shankar Patel.

one query
If that is not taken from the end user which one i should write.Which one is more faster?

uspatel 25-Apr-12 7:03am

check you both query's by clicking dispaly execution plan in SQL server mgmt studio.
Check response time of both,You can find which is best.