I recently received the following email which brings up a good point for those of you using the
Recordset.AddNew method to add new rows to your database tables.
I inherited an application that used .AddNew to add a new record to the
table. The SQL statement used to open the recordset was "SELECT * FROM
MyTable" with no "WHERE" clause attached. And this was
when we knew the purpose of the recordset was only to add a new record.
I found the performance improved remarkably after making one small change to
this code.
SELECT Top 1 * FROM MyTable
Why ask for all records when all we need is a map of the table?
Of course in this case, we did need "SELECT * FROM ..." because we needed
access to all the fields in the table.
Hope this helps,
Ann
Thanks for the great tip Ann. You're absolutely right. If the only reason for the
Recordset is to add new data, then why are we wasting time retrieving the existing data?
I've actually seen this done a lot in code that people have sent me over the years. I think
it stems from newer developers just not knowing SQL very well and not being able to come up
with an easy way to retrieve all the field information without doing a "SELECT *" and
pulling back all the data as well.
While Ann's method works great, we can actually take things one step further.
Why request any records at all? I've been using the following twist on her idea for a while now.
SELECT * FROM MyTable WHERE 0=1
Naturally zero will never equal one, so when you execute the above query you'll get
back an empty recordset, but all the field information will still be available.
If you'd like to take a look at an example, this is the same method I use in our
classic ASP sample that shows how to add a record to a database
(db_add.asp).
Note: While this tip focused on ways to optimize using the Recordset.AddNew
method of adding new records to a table, it's often faster and just as easy to use a
SQL INSERT statement instead. That said, many people prefer using .AddNew and that's fine, but
if you are going to use it, please keep the above tip in mind.
If you have a tip you would like to submit, please send it to:
webmaster@asp101.com.
