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/

care to post your code?

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.

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

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)

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??