Parameterized query ( with bind parameters ) running much slower than non-parameteriz
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6

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

  1. #1
    Join Date
    Nov 1999
    Location
    Bangalore, India
    Posts
    31

    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/
    ==================================

  2. #2
    Join Date
    Nov 2007
    Location
    .NET 3.5 / VS2008 Developer
    Posts
    624

    Re: Parameterized query ( with bind parameters ) running much slower than non-paramet

    care to post your code?

  3. #3
    Join Date
    Mar 2002
    Location
    NY, USA
    Posts
    12,097

    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
    In theory, there is no difference between theory and paractice; 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

  4. #4
    Join Date
    Nov 1999
    Location
    Bangalore, India
    Posts
    31

    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/
    ==================================

  5. #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)

  6. #6
    Join Date
    Mar 2002
    Location
    NY, USA
    Posts
    12,097

    Re: Parameterized query ( with bind parameters ) running much slower than non-paramet

    Quote Originally Posted by kangkan View Post
    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
    In theory, there is no difference between theory and paractice; 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
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center