-
November 14th, 2008, 02:25 AM
#1
Parameterized query ( with bind parameters ) running much slower than non-parameteriz
I have observed that when I use a parameterised query with bind parameters, the performance of the query is much slower in comparison to the performance when I use a string builder and create the query dynamically.
I am using Oracle 10G and C# .net 3.0.
Can somebody help me to find out what might be the issue here?
Thanks to all for your support.
Regards,
Kangkan
http://www.geekays.net/
==================================
Kangkan
GEEK At Your Service
Website: http://www.geekays.net/
==================================
-
November 15th, 2008, 09:42 AM
#2
Re: Parameterized query ( with bind parameters ) running much slower than non-paramet
-
November 15th, 2008, 12:50 PM
#3
Re: Parameterized query ( with bind parameters ) running much slower than non-paramet
The most common cause is that the command is being re-created, rather than having a stable instance of the command which is properly "prepared".
Even this condition is unlikely to have a significant impact on a well designed system, but without a minimal yet complete [this means it will compile and exhibit the behaviour, but contain NO code which is not pertinent to the issue] post, it is impossible to draw any conclusions.
TheCPUWizard is a registered trademark, all rights reserved. (If this post was helpful, please RATE it!)
2008, 2009,2010
In theory, there is no difference between theory and practice; in practice there is.
* Join the fight, refuse to respond to posts that contain code outside of [code] ... [/code] tags. See here for instructions
* How NOT to post a question here
* Of course you read this carefully before you posted
* Need homework help? Read this first
-
November 16th, 2008, 12:09 AM
#4
Re: Parameterized query ( with bind parameters ) running much slower than non-paramet
Hi eclipsed4utoo,
Shall be publishing the code after sometime. I am away from the code as of now.
Could not make out what CPUWIZARD wants to say.
Thanks,
Regards,
Kangkan
==================================
Kangkan
GEEK At Your Service
Website: http://www.geekays.net/
==================================
-
November 17th, 2008, 10:58 AM
#5
Re: Parameterized query ( with bind parameters ) running much slower than non-paramet
It's clear to me so I'm not sure why this is needed but translation:
- You're possibly creating the command object every time, and not necessarily doing it in the most expedient fashion. That said, it shouldn't be noticeable on a well designed system.
- Show some code that has the problem but without any extraneous components (ie, no logging operations / etc)
-
November 17th, 2008, 11:15 AM
#6
Re: Parameterized query ( with bind parameters ) running much slower than non-paramet
Originally Posted by kangkan
Could not make out what CPUWIZARD wants to say.
Compare the following (psuedo-code - not intendd to compile)
Code:
class One
{
public void Execute(int x)
{
SqlCommand cmd = new SqlCommand("select * from foo where bar=@param);
cmd.Params.Add(new SqlParameter("param", x));
cmd.Execute()
}
}
Code:
class Two
{
private SqlCommand cmd;
public Two()
{
cmd = new SqlCommand("select * from foo where bar=@param);
cmd.Params.Add(new SqlParameter("param"));
cmd.Prepare();
}
public void Execute(int x)
{
cmd.Params[0].Value = x;
cmd.Execute()
}
}
SEE the difference??
TheCPUWizard is a registered trademark, all rights reserved. (If this post was helpful, please RATE it!)
2008, 2009,2010
In theory, there is no difference between theory and practice; in practice there is.
* Join the fight, refuse to respond to posts that contain code outside of [code] ... [/code] tags. See here for instructions
* How NOT to post a question here
* Of course you read this carefully before you posted
* Need homework help? Read this first
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|