Using SQL Query Or AddNew

[Arpan De]

In order to insert records in a SQL Server 7.0 database table, is it better to use a SQL query or is it better to use AddNew? For
e.g. records can be inserted in a DB table using

<%
Dim strSQL
strSQL="INSERT INTO tblName(Col1,Col2,Col3) VALUES ('Val1','Val2','Val3')"
objRS.Open strSQL,objConn
%>

or

<%
objRS.Open "tblName"
objRS.AddNew
objRS("Col1")="Val1"
objRS("Col2")="Val2"
objRS("Col3")="Val3"
objRS.Update
%>

Which of the above 2 methods is more effecient & why? Or using either of the 2 methods doesn't make any difference??????

Thanks,

Arpan


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system ([url]http://www.grisoft.com[/url]).
Version: 6.0.502 / Virus Database: 300 - Release Date: 18/07/2003

[Mark Schupp]

Actually the 3rd option is best

Dim strSQL
strSQL="INSERT INTO tblName(Col1,Col2,Col3) VALUES ('Val1','Val2','Val3')"
objConn.Execute strsql

--
Mark Schupp
--
Head of Development
Integrity eLearning
Online Learning Solutions Provider
[email]mschupp@ielearning.com[/email]
[url]http://www.ielearning.com[/url]
714.637.9480 x17


"Arpan De" <arpan_de5A@rediffmail.com> wrote in message
news:OAPWJY9eDHA.1648@TK2MSFTNGP09.phx.gbl...

> In order to insert records in a SQL Server 7.0 database table, is it

better to use a SQL query or is it better to use AddNew? For

> e.g. records can be inserted in a DB table using
>
> <%
> Dim strSQL
> strSQL="INSERT INTO tblName(Col1,Col2,Col3) VALUES ('Val1','Val2','Val3')"
> objRS.Open strSQL,objConn
> %>
>
> or
>
> <%
> objRS.Open "tblName"
> objRS.AddNew
> objRS("Col1")="Val1"
> objRS("Col2")="Val2"
> objRS("Col3")="Val3"
> objRS.Update
> %>
>
> Which of the above 2 methods is more effecient & why? Or using either of

the 2 methods doesn't make any difference??????

>
> Thanks,
>
> Arpan
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system ([url]http://www.grisoft.com[/url]).
> Version: 6.0.502 / Virus Database: 300 - Release Date: 18/07/2003
>
>

[Nicole Calinoiu]

Mark,

Both Connection.Execute and Command.Execute implicitly return a recordset,
so you're version is not quite optimized. To prevent return of the
recordset, use the adExecuteNoRecords flag in the options argument of the
method call.

Nicole


"Mark Schupp" <mschupp@ielearning.com> wrote in message
news:uaw12v9eDHA.2260@TK2MSFTNGP10.phx.gbl...

> Actually the 3rd option is best
>
> Dim strSQL
> strSQL="INSERT INTO tblName(Col1,Col2,Col3) VALUES ('Val1','Val2','Val3')"
> objConn.Execute strsql
>
> --
> Mark Schupp
> --
> Head of Development
> Integrity eLearning
> Online Learning Solutions Provider
> [email]mschupp@ielearning.com[/email]
> [url]http://www.ielearning.com[/url]
> 714.637.9480 x17
>
>
> "Arpan De" <arpan_de5A@rediffmail.com> wrote in message
> news:OAPWJY9eDHA.1648@TK2MSFTNGP09.phx.gbl...

> > In order to insert records in a SQL Server 7.0 database table, is it

> better to use a SQL query or is it better to use AddNew? For

> > e.g. records can be inserted in a DB table using
> >
> > <%
> > Dim strSQL
> > strSQL="INSERT INTO tblName(Col1,Col2,Col3) VALUES

('Val1','Val2','Val3')"

> > objRS.Open strSQL,objConn
> > %>
> >
> > or
> >
> > <%
> > objRS.Open "tblName"
> > objRS.AddNew
> > objRS("Col1")="Val1"
> > objRS("Col2")="Val2"
> > objRS("Col3")="Val3"
> > objRS.Update
> > %>
> >
> > Which of the above 2 methods is more effecient & why? Or using either of

> the 2 methods doesn't make any difference??????

> >
> > Thanks,
> >
> > Arpan
> >
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system ([url]http://www.grisoft.com[/url]).
> > Version: 6.0.502 / Virus Database: 300 - Release Date: 18/07/2003
> >
> >

>
>

[Arpan De]

Thanks Mark for your response. I do agree that the 3rd option you have given is undoubtedly the best option for inserting records
in a database table but I would like to know which method is more efficient as far as the 2 methods which I have shown in my
thread are concerned. So could you please answer this?

Thanks once again,

Regards,

Arpan

"Mark Schupp" <mschupp@ielearning.com> wrote in message news:uaw12v9eDHA.2260@TK2MSFTNGP10.phx.gbl...
: Actually the 3rd option is best
:
: Dim strSQL
: strSQL="INSERT INTO tblName(Col1,Col2,Col3) VALUES ('Val1','Val2','Val3')"
: objConn.Execute strsql
:
: --
: Mark Schupp
: --
: Head of Development
: Integrity eLearning
: Online Learning Solutions Provider
: [email]mschupp@ielearning.com[/email]
: [url]http://www.ielearning.com[/url]
: 714.637.9480 x17
:
:
: "Arpan De" <arpan_de5A@rediffmail.com> wrote in message
: news:OAPWJY9eDHA.1648@TK2MSFTNGP09.phx.gbl...
: > In order to insert records in a SQL Server 7.0 database table, is it
: better to use a SQL query or is it better to use AddNew? For
: > e.g. records can be inserted in a DB table using
: >
: > <%
: > Dim strSQL
: > strSQL="INSERT INTO tblName(Col1,Col2,Col3) VALUES ('Val1','Val2','Val3')"
: > objRS.Open strSQL,objConn
: > %>
: >
: > or
: >
: > <%
: > objRS.Open "tblName"
: > objRS.AddNew
: > objRS("Col1")="Val1"
: > objRS("Col2")="Val2"
: > objRS("Col3")="Val3"
: > objRS.Update
: > %>
: >
: > Which of the above 2 methods is more effecient & why? Or using either of
: the 2 methods doesn't make any difference??????
: >
: > Thanks,
: >
: > Arpan
: >
: >
: > ---
: > Outgoing mail is certified Virus Free.
: > Checked by AVG anti-virus system ([url]http://www.grisoft.com[/url]).
: > Version: 6.0.502 / Virus Database: 300 - Release Date: 18/07/2003
: >
: >
:
:


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system ([url]http://www.grisoft.com[/url]).
Version: 6.0.502 / Virus Database: 300 - Release Date: 18/07/2003

[Bob Barrows]

It is always more efficient to use a set-based operation (the INSERT query)
than it is to use a cursor.

Actually, it is best to use a stored procedure as opposed to dynamic SQL.
See here:
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnile/html/docu2kbench.asp[/url]
(scroll down to the dynamic sql vs stored procedure test)

Bob Barrows

PS. Don't open a recordset when running a query that does not return
records. Use the connection's execute method instead.

Arpan De wrote:

> In order to insert records in a SQL Server 7.0 database table, is it
> better to use a SQL query or is it better to use AddNew? For e.g.
> records can be inserted in a DB table using
>
> <%
> Dim strSQL
> strSQL="INSERT INTO tblName(Col1,Col2,Col3) VALUES
> ('Val1','Val2','Val3')"
> objRS.Open strSQL,objConn
> %>
>
> or
>
> <%
> objRS.Open "tblName"
> objRS.AddNew
> objRS("Col1")="Val1"
> objRS("Col2")="Val2"
> objRS("Col3")="Val3"
> objRS.Update
> %>
>
> Which of the above 2 methods is more effecient & why? Or using either
> of the 2 methods doesn't make any difference??????
>
> Thanks,
>
> Arpan
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system ([url]http://www.grisoft.com[/url]).
> Version: 6.0.502 / Virus Database: 300 - Release Date: 18/07/2003

[Mark Schupp]

Nice tip, thanks.

--
Mark Schupp
--
Head of Development
Integrity eLearning
Online Learning Solutions Provider
[email]mschupp@ielearning.com[/email]
[url]http://www.ielearning.com[/url]
714.637.9480 x17


"Nicole Calinoiu" <nicolec@somewhere.net> wrote in message
news:OinnG29eDHA.3204@TK2MSFTNGP11.phx.gbl...

> Mark,
>
> Both Connection.Execute and Command.Execute implicitly return a recordset,
> so you're version is not quite optimized. To prevent return of the
> recordset, use the adExecuteNoRecords flag in the options argument of the
> method call.
>
> Nicole
>
>
> "Mark Schupp" <mschupp@ielearning.com> wrote in message
> news:uaw12v9eDHA.2260@TK2MSFTNGP10.phx.gbl...

> > Actually the 3rd option is best
> >
> > Dim strSQL
> > strSQL="INSERT INTO tblName(Col1,Col2,Col3) VALUES

('Val1','Val2','Val3')"

> > objConn.Execute strsql
> >
> > --
> > Mark Schupp
> > --
> > Head of Development
> > Integrity eLearning
> > Online Learning Solutions Provider
> > [email]mschupp@ielearning.com[/email]
> > [url]http://www.ielearning.com[/url]
> > 714.637.9480 x17
> >
> >
> > "Arpan De" <arpan_de5A@rediffmail.com> wrote in message
> > news:OAPWJY9eDHA.1648@TK2MSFTNGP09.phx.gbl...

> > > In order to insert records in a SQL Server 7.0 database table, is it

> > better to use a SQL query or is it better to use AddNew? For

> > > e.g. records can be inserted in a DB table using
> > >
> > > <%
> > > Dim strSQL
> > > strSQL="INSERT INTO tblName(Col1,Col2,Col3) VALUES

> ('Val1','Val2','Val3')"

> > > objRS.Open strSQL,objConn
> > > %>
> > >
> > > or
> > >
> > > <%
> > > objRS.Open "tblName"
> > > objRS.AddNew
> > > objRS("Col1")="Val1"
> > > objRS("Col2")="Val2"
> > > objRS("Col3")="Val3"
> > > objRS.Update
> > > %>
> > >
> > > Which of the above 2 methods is more effecient & why? Or using either

of

> > the 2 methods doesn't make any difference??????

> > >
> > > Thanks,
> > >
> > > Arpan
> > >
> > >
> > > ---
> > > Outgoing mail is certified Virus Free.
> > > Checked by AVG anti-virus system ([url]http://www.grisoft.com[/url]).
> > > Version: 6.0.502 / Virus Database: 300 - Release Date: 18/07/2003
> > >
> > >

> >
> >

>
>

[Arpan De]

Thanks, Bob, for your reply.Could you please tell me what do you mean by "set-based operation"?

Thanks once again,

Regards,

Arpan

"Bob Barrows" <reb_01501@yahoo.com> wrote in message news:eqHCZ99eDHA.1748@TK2MSFTNGP10.phx.gbl...
: It is always more efficient to use a set-based operation (the INSERT query)
: than it is to use a cursor.
:
: Actually, it is best to use a stored procedure as opposed to dynamic SQL.
: See here:
: [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnile/html/docu2kbench.asp[/url]
: (scroll down to the dynamic sql vs stored procedure test)
:
: Bob Barrows
:
: PS. Don't open a recordset when running a query that does not return
: records. Use the connection's execute method instead.
:
: Arpan De wrote:
: > In order to insert records in a SQL Server 7.0 database table, is it
: > better to use a SQL query or is it better to use AddNew? For e.g.
: > records can be inserted in a DB table using
: >
: > <%
: > Dim strSQL
: > strSQL="INSERT INTO tblName(Col1,Col2,Col3) VALUES
: > ('Val1','Val2','Val3')"
: > objRS.Open strSQL,objConn
: > %>
: >
: > or
: >
: > <%
: > objRS.Open "tblName"
: > objRS.AddNew
: > objRS("Col1")="Val1"
: > objRS("Col2")="Val2"
: > objRS("Col3")="Val3"
: > objRS.Update
: > %>
: >
: > Which of the above 2 methods is more effecient & why? Or using either
: > of the 2 methods doesn't make any difference??????
: >
: > Thanks,
: >
: > Arpan
: >
: >
: > ---
: > Outgoing mail is certified Virus Free.
: > Checked by AVG anti-virus system ([url]http://www.grisoft.com[/url]).
: > Version: 6.0.502 / Virus Database: 300 - Release Date: 18/07/2003
:
:
:


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system ([url]http://www.grisoft.com[/url]).
Version: 6.0.502 / Virus Database: 300 - Release Date: 18/07/2003

[Bob Barrows]

SQL works via set-based operations, i.e., operations that use sets of rows.
A Select statement retrieves a set of rows, an UPDATE statement modifies a
set of rows, an INSERT statement inserts a set of rows, and a DELETE
statement deletes a set of rows.

A cursor works by loading and locking a single row at a time and performing
an operation on that row. This was fine in the old file-based desktop
systems, but its inefficiency was exposed rather quickly when they needed
databases to be able to perform more and more transactions per unit time by
more and more users.

Modern database engines are tuned to take advantage of set-based operations.
This results in shorter transactions and more efficient operations. Using a
cursor to do data maintenance requires the database to act like a file-based
database, which means that you are not taking advantage of the database's
full capabilities.

HTH,
Bob Barrows


Arpan De wrote:

> Thanks, Bob, for your reply.Could you please tell me what do you mean
> by "set-based operation"?
>
> Thanks once again,
>
> Regards,
>
> Arpan
>
> "Bob Barrows" <reb_01501@yahoo.com> wrote in message
> news:eqHCZ99eDHA.1748@TK2MSFTNGP10.phx.gbl...

>> It is always more efficient to use a set-based operation (the INSERT
>> query)
>> than it is to use a cursor.
>>
>> Actually, it is best to use a stored procedure as opposed to dynamic
>> SQL.
>> See here:
>>

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnile/html/docu2kbench.asp[/url]

>> (scroll down to the dynamic sql vs stored procedure test)
>>
>> Bob Barrows
>>
>> PS. Don't open a recordset when running a query that does not return
>> records. Use the connection's execute method instead.
>>
>> Arpan De wrote:

>>> In order to insert records in a SQL Server 7.0 database table, is it
>>> better to use a SQL query or is it better to use AddNew? For e.g.
>>> records can be inserted in a DB table using
>>>
>>> <%
>>> Dim strSQL
>>> strSQL="INSERT INTO tblName(Col1,Col2,Col3) VALUES
>>> ('Val1','Val2','Val3')"
>>> objRS.Open strSQL,objConn
>>> %>
>>>
>>> or
>>>
>>> <%
>>> objRS.Open "tblName"
>>> objRS.AddNew
>>> objRS("Col1")="Val1"
>>> objRS("Col2")="Val2"
>>> objRS("Col3")="Val3"
>>> objRS.Update
>>> %>
>>>
>>> Which of the above 2 methods is more effecient & why? Or using
>>> either
>>> of the 2 methods doesn't make any difference??????
>>>
>>> Thanks,
>>>
>>> Arpan
>>>
>>>
>>> ---
>>> Outgoing mail is certified Virus Free.
>>> Checked by AVG anti-virus system ([url]http://www.grisoft.com[/url]).
>>> Version: 6.0.502 / Virus Database: 300 - Release Date: 18/07/2003

>>
>>
>>

>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system ([url]http://www.grisoft.com[/url]).
> Version: 6.0.502 / Virus Database: 300 - Release Date: 18/07/2003

[Arpan De]

Thanks Bob. That indeed was a great explanation.

Regards,

Arpan

"Bob Barrows" <reb_01501@yahoo.com> wrote in message news:OhGjIn#eDHA.3324@TK2MSFTNGP11.phx.gbl...
: SQL works via set-based operations, i.e., operations that use sets of rows.
: A Select statement retrieves a set of rows, an UPDATE statement modifies a
: set of rows, an INSERT statement inserts a set of rows, and a DELETE
: statement deletes a set of rows.
:
: A cursor works by loading and locking a single row at a time and performing
: an operation on that row. This was fine in the old file-based desktop
: systems, but its inefficiency was exposed rather quickly when they needed
: databases to be able to perform more and more transactions per unit time by
: more and more users.
:
: Modern database engines are tuned to take advantage of set-based operations.
: This results in shorter transactions and more efficient operations. Using a
: cursor to do data maintenance requires the database to act like a file-based
: database, which means that you are not taking advantage of the database's
: full capabilities.
:
: HTH,
: Bob Barrows
:
:
: Arpan De wrote:
: > Thanks, Bob, for your reply.Could you please tell me what do you mean
: > by "set-based operation"?
: >
: > Thanks once again,
: >
: > Regards,
: >
: > Arpan
: >
: > "Bob Barrows" <reb_01501@yahoo.com> wrote in message
: > news:eqHCZ99eDHA.1748@TK2MSFTNGP10.phx.gbl...
: >> It is always more efficient to use a set-based operation (the INSERT
: >> query)
: >> than it is to use a cursor.
: >>
: >> Actually, it is best to use a stored procedure as opposed to dynamic
: >> SQL.
: >> See here:
: >>
: [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnile/html/docu2kbench.asp[/url]
: >> (scroll down to the dynamic sql vs stored procedure test)
: >>
: >> Bob Barrows
: >>
: >> PS. Don't open a recordset when running a query that does not return
: >> records. Use the connection's execute method instead.
: >>
: >> Arpan De wrote:
: >>> In order to insert records in a SQL Server 7.0 database table, is it
: >>> better to use a SQL query or is it better to use AddNew? For e.g.
: >>> records can be inserted in a DB table using
: >>>
: >>> <%
: >>> Dim strSQL
: >>> strSQL="INSERT INTO tblName(Col1,Col2,Col3) VALUES
: >>> ('Val1','Val2','Val3')"
: >>> objRS.Open strSQL,objConn
: >>> %>
: >>>
: >>> or
: >>>
: >>> <%
: >>> objRS.Open "tblName"
: >>> objRS.AddNew
: >>> objRS("Col1")="Val1"
: >>> objRS("Col2")="Val2"
: >>> objRS("Col3")="Val3"
: >>> objRS.Update
: >>> %>
: >>>
: >>> Which of the above 2 methods is more effecient & why? Or using
: >>> either
: >>> of the 2 methods doesn't make any difference??????
: >>>
: >>> Thanks,
: >>>
: >>> Arpan
: >>>
: >>>
: >>> ---
: >>> Outgoing mail is certified Virus Free.
: >>> Checked by AVG anti-virus system ([url]http://www.grisoft.com[/url]).
: >>> Version: 6.0.502 / Virus Database: 300 - Release Date: 18/07/2003
: >>
: >>
: >>
: >
: >
: > ---
: > Outgoing mail is certified Virus Free.
: > Checked by AVG anti-virus system ([url]http://www.grisoft.com[/url]).
: > Version: 6.0.502 / Virus Database: 300 - Release Date: 18/07/2003
:
:
:


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system ([url]http://www.grisoft.com[/url]).
Version: 6.0.502 / Virus Database: 300 - Release Date: 18/07/2003