Solved

Efficiency Question with ADO .AddNew method

Posted on 2002-04-17
16
404 Views
Last Modified: 2008-02-07
When updating a record, I will usually do something like:

strSQL = "Select * FROM tblTestTable WHERE numRecordID = " & numVariable

objRS.Open strSQL,objConnection,adOpenKeyset,adLockOptimistic

objRS("SomeField") = SomeValue
objRS("OtherField") = OtherValue
objRS.update

and then close and clear my recordset and connection objects. Similarly, when executing delete commands, my SQL statement limits the number of records I retrieve in my recordset.

When adding a new record, however, no SQL statement is really needed; I can just do the following:

objRS.Open tblTestTable,objConnection,adOpenKeyset,adLockOptimistic

objRS.AddNew
   objRS("SomeField") = SomeValue
   objRS("OtherField") = OtherValue
objRS.update

The problem with this approach, at least as I understand it, is that the recordset will now comprise all records from that table, and that would not be a particularly efficient approach.


I know that I could make a dummy SQL statement to retrieve just one row, but surely, there must be some standard way that people approach this problem?

Frtiz the Blank

0
Comment
Question by:fritz_the_blank
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 2
  • +3
16 Comments
 
LVL 1

Expert Comment

by:HenningF
ID: 6947811
Using these ado-commands creates quite a bit of server overhead which is really not necissary. SQL is a language speciffically designed for manipulating databases, and using sql to do this is definetly the best way.

To insert into a database do like this:


SQL = "INSERT INTO table (Value1, Value2) VALUES('" & txtVar1 & "', " numVar1 & ")"

Database = "put you connectionstring here"
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open Database
MyConn.Execute(SQL)
MyConn.Close
Set MyConn = nothing

You can also use "stored procedures" but this is a whole new ballpark and quite a bit more advanced...

HenningF
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 6947847
Henning,

Not to be contrary, but my understanding is that using ADO is more efficient than using literal SQL strings, especially with larger numbers of fields. Imagine that I have a form with 10 text fields, a few selects, and a couple of memo fields; now you can imagine that the literal SQL string will become quite long, especially if there is a good deal of content in the memo fields. Speaking of which, if they run too long, a literal SQL string will fail because of the maximum character lenght. Finally, there is the issue of troublesome characters and formatting that problematizes literal SQL strings that dissappear when using ADO. Speed tests have shown that ADO actually performs--albeit quite marginally--better than a literal insert.

Fritz the Blank
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 6947894
ADO is more intelligent than standard SQL :-)
1.It can take care of special characters
2. For text/image type in SQL/Oracle you have the options to use getchunk/appenchunk and it works like a charm

But in ADO,
Casting is not compatible with the db especially date/time fields
As far as efficiency is concerned, I think ADO is lagging behind  ANSI SQL in the case  of SORT/FILTER method ( ORDER BY/WHERE clause is much faster)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 46

Author Comment

by:fritz_the_blank
ID: 6947919
For displaying data and so forth, I always use SQL statements to gather, sort, group and etc. It is only for adding, updating, and deleting that I like to use the ADO method. The question remains, however, regarding the best way to call the recordset when adding a new record.

Fritz the Blank
0
 
LVL 11

Expert Comment

by:thunderchicken
ID: 6948095
I'm for the conn.execute(SQL) argument

I've found it faster, not much, but only on huge tasks.
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 6948140
Okay, let's say that I am committed to using the .AddNew, .Update, and .Delete methods. What do people think about my question stated at the beginning?

Fritz the Blank
0
 
LVL 15

Expert Comment

by:robbert
ID: 6949084
It's true that when you're selecting a complete table, the time (and RAM) will be taken that is necessary to gather this data.
This fact will become obvious when you're selecting, say, one or a few millions of records.

Thus, instead of:

> objRS.Open tblTestTable,objConnection,adOpenKeyset,adLockOptimistic

state:

objRS.Open "SELECT * FROM table WHERE FIELD = (nonexistant)", objConnection, adOpenKeyset, adLockOptimistic
objRs.AddNew
...

I.e., "SELECT * FROM table WHERE id = -1"

- The "objCnn.Execute" method is faster but I don't use it, too, as it is not as flexible as using a recordset, and requires some string parsing and concatenating.
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 6949272
Robbert,

You are getting at the point of my question. Currently, my idea is to do exactly what you suggest, i.e., either select one record or create a query that returns no records.

However, I am surprised that there is no standard way of doing this. Didn't the folks who developed ADO consider this a problem?

Fritz the Blank
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6949455
Either I am missing the point or you are confusing ADO's functionality.  ALL the suggestions offered so far use ADO, period.

What you are asking is whether you should update using a recordset object or not.  The answer (especially in ASP) is that you should never use a recordset object. Using a Connection or Command object will always give you better performance.

This is especially true using SQL Server, where yuo should never give Select writes to a table, but instead only use Stored Procedures (with Execute permissions)

There has only been one ocassion when I had to use a recordset and that was because the third party tool I was using to upload images required (or at least I thought it did) a recordset.

You are very welcome to continue to use a recordset, but eventually you will come to the same conclusion I have just stated.

Hope this helps,
Anthony
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 6951151
HenningF & ThunderChicken

I stand corrected--using the recordset for updating rather than executing a SQL string through the command object may actually be less efficient.

Jit,

Would you please explain the issue about the date fields?

Anthony,

I really like how easily using the recordset object gets around issues like invalid characters and excessively long SQL strings when I have multiple memo fields on the form.

Using stored procedures would be best, but sometimes I am not given the permission to create them on the server, or I am required to write the code in such a way that I can switch the back end quickly. So, it is not always an option.

Do you feel that the performance decrease outweighs the conveniece of using the recordset object in these situations?

Robbert,

So there is no way around using the dummy SQL select?

Fritz the Blank

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6951337
I am sorry, but I cannot see how the recordset is any more convenient.  Are you referring to fields that contain an apostrophe?  If this is the case than write a function that will replace a single apostrophe with two apostophes.

Function SQLSafe(Value)

SQLSafe = Replace(Value,"'", "''")

End Function

Aside from the performance issues, holding a connection longer (as you inevitably would with a Recordset object) than needed is not a good idea.

My 2 cents worth.
Anthony
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 6951363
Anthony,

The convenience has mostly to do with the size of multiple memo fields on a form. If too much data is posted, the literal SQL string becomes too long and returns errors.


How is the connection held open longer? Do you mean because the .AddNew or .Update proceedures take longer with a recordset than with the command object?

Fritz the Blank
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6951431
>> How is the connection held open longer? Do you mean because the .AddNew or .Update proceedures take
longer with a recordset than with the command object?<<

Yes.

I am impressed (but not surprised) that the IT gods will not allow you easy access to create and modify Stored Procedures, yet will give you Select/Update/Delete permissions to the tables. Yikes!

Anthony
0
 
LVL 15

Accepted Solution

by:
robbert earned 100 total points
ID: 6951620
> So there is no way around using the dummy SQL select?

Definitely not. A recordset is, at the first line, a resultset to work with, not a means of updating single records.
(I'm using it to insert/update, though; at least when there are many columns to update.)
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 6953389
Thank you to all for helping. Since Robbert gave me the answer that I was looking for, I would like to award the points to him. However, I would also like to post points for all who helped, you have all given me something to think about:

HenningF: http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=asp&qid=20291375

Jit: http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=asp&qid=20291376

ThunderChicken: http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=asp&qid=20291377

Anthony: http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=asp&qid=20291378


Fritz the Blank
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 6954003
Thanks once again to everybody.

Fritz the Blank
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
JS does not refresh 6 42
Index on a Table 6 36
html Uncheck Checkbox 2 36
calculate days away 11 61
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question