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/
Re: Parameterized query ( with bind parameters ) running much slower than non-paramet
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.
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
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)
Re: Parameterized query ( with bind parameters ) running much slower than non-paramet
Quote:
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??