ASP 101 - Active Server Pages 101 - Web06
The Place ASP Developers Go!



Windows Technology Windows Technology
15 Seconds
4GuysFromRolla.com
ASP 101
ASP Wire
VB Forums
VB Wire
WinDrivers.com
internet.commerce internet.commerce
Partners & Affiliates
ASP 101 is an
internet.com site
ASP 101 is an internet.com site
IT
Developer
Internet News
Small Business
Personal Technology

Search internet.com
Advertise
Corporate Info
Newsletters
Tech Jobs
E-mail Offers

ASP 101 News Flash ASP 101 News Flash



 Top ASP 101 Stories Top ASP 101 Stories
Connections, Commands, And Procedures
What is ASP?
VBScript Classes: Part 1 of N

QUICK TIP:
Using Response.CacheControl
Show All Tips >>
ASP 101 RSS Feed ASP 101 Updates


Quick Tips


Don't Retrieve Data When Using Recordset.AddNew

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.

Hello,

Re: Don't Use Select * - great tip.

Here is another wrinkle:

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.

Previous      Show All Tips      Next

If you have a tip you would like to submit, please send it to: webmaster@asp101.com.


Home |  News |  Samples |  Articles |  Lessons |  Resources |  Forum |  Links |  Search |  Feedback

Internet.com
The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers