Most efficient way to perform an update

[Chris Strug]

Hi,

I have an unbound form. A user enters data in a couple of fields, clicks
submit and the fields are included in a table through an update statement.

However, my question is what is the best (most efficient) way to do this.
I'm something of a newbiew to ADP / SQL Server, however, as I see it I have
these options:

1. Create a Stored Proc which takes the information to be added as
parameters and includes the data as an UPDATE statement.

2. Use an ADO recordset in code. I.e.

rst.addnew
rst!description = me.txtDesc
rst!...
..
..
rst.update

3. Create the SQL statement in code as a string and then execute that string
using an ADO command object.

From what I can see and from what others have already told me (in particular
Vadim - many thanks) - options 1 & 2 have the advantage that they#re on the
server so changes won't mean me having to update every client, however,
option 3 means one less object in the project.

Of course, are there any other ways that I have missed?

Sorry if this is stupid but I think this could be important to know if some
projects I have my eye on get approved.

Many thanks

Chris Strug

 

[Rod Gill]

Hi,

I feel Stored procedures are best. They provide a middle layer and are easy
to manage. However, for a couple of systems I'm writing at the moment, I
have found it very time consuming to add stored procedures to the database.
They all have to be submitted to IT and then added (usually after 2 or 3
days). Security is locked down so tight I can't create them using Access
even.

I can't work like that, so for the development phase I am writing them all
as strings in code. If they want to pay me to translate to SP's in one go
later I will do.

I did an interesting experiment. In a table of 120,000 records in SQL Server
2000 (running on my development PC) I wrote a loop that created a recordset,
searched for a value in an int field then updated a real field.

I also wrote a loop (again it executed 100 times) that did an update
statement to do the same thing.

Recordset time: 1.7 seconds average after 5 repeats.
Update string time after 5 repeats: 4.7 seconds.
Converting the Update string to an SP: 4 seconds.

I then added an index to the lookup field and got the very interesting
times:
Recordset: 4 seconds
Update string: 0.27 seconds!!

Now, this statement is very simple and I suspect an SP will show a bigger
improvement for more complex statements. For simple work however, I'm happy
building statements in code then submitting them. A 1400% improvement in
performance by adding an index isn't bad!

--
Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit www.projectlearning.com/