Solved

Efficiency Question with ADO .AddNew method

Posted on 2002-04-17
16
392 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
  • 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now