Parameterized query ( with bind parameters ) running much slower than non-parameteriz

[kangkan]

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/

[eclipsed4utoo]

care to post your code?

[TheCPUWizard]

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.

[kangkan]

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

[mmetzger]

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)

[TheCPUWizard]

Could not make out what CPUWIZARD wants to say.

Compare the following (psuedo-code - not intendd to compile)

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()
     }
}

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