Re: A general question about SQL statement

From: Bob Grommes (bob_at_bobgrommes.com)
Date: 08/13/04 

Date: Fri, 13 Aug 2004 09:04:37 -0700

SQL Server caches commands and precompiles them. If the command string does
not change from one call to the next, as is the case with a parameterized
query, performance is significantly increased. But if you build the values
into the command string, it will change virtually every time, defeating this
optimization.

I haven't seen any benchmarks, and one's mileage probably varies, but
parameterized SQL statements probably approach the performance level of
stored procedures in some cases, particularly batch processes. The main
performance advantage of stored procedures would be that in most cases there
would be no server-side compiles at all, not even on the first call.

--Bob

"Michael" <m@m.com> wrote in message
news:O6sZpnMgEHA.1652@TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> I would like to get people's opinion about executing SQL statements in C#
> (or any other .NET language really). I used to create my SQL statement by
> building a string and replacing single quote with two single quotes.
> Sometimes, I used SqlParameter. Maybe, I'm a bit lazy when I build the SQL
> string. Should I always use SqlParameters? What are the
> advantages/disadvantages between building SQL string and using
SqlParameter?
> Does one perform better than the other?
>
> Thanks.
> Michael
>
>

Relevant Pages

  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Need help with Code Please!!!
    ... posted in response to my last post was some SQL, but this is not the SQL that ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)
  • RE: Web Part and Access database
    ... I dont know if it is possible to connect to access,but you can download SQL ... Server Error in '/Webparts' Application. ... The connection string specifies a local Sql Server Express instance ... String user, String password, Boolean trusted, String connectionString) ...
    (microsoft.public.sharepoint.portalserver.development)
  • Re: INDEXES: BTRIEVE vs EXTFH (cobol)
    ... In SQL, you can specify just about anything, but at the lower MKDE ... the engine has to pick the right Btrieve ... When COBOL needs to find this value, it knows that the key is a string. ...
    (comp.databases.btrieve)
  • Re: INDEXES: BTRIEVE vs EXTFH (cobol)
    ... Bill Bach wrote: ... In SQL, you can specify just about anything, but at the lower MKDE ... the engine has to pick the right Btrieve ... When COBOL needs to find this value, it knows that the key is a string. ...
    (comp.databases.btrieve)