Open a Recordset vs. execute a stored procedure

[batteam]

My group is converting our Access 2000 .mdb files over to Access Projects (.adp) with our databases on MS SQL Server 2000. Our dilemma is that our .mdb files used DAO to open recordsets to linked tables, but now that our tables are on a server, should we continue to use the Open Recordset method (using ADO and the Command object)for adds, edits and viewing records, or should we use Insert Into, Update and Select stored procedures to do all the table reading and writing between client and server? Or is either way as good as the other? We won't have more than a dozen users on any one Project at one time once we're up and running. Thanks for your help.

 

[Golom]

If you are looping through recordsets to insert, update, etc., then you are generating lots of network traffic because the records need to be retrieved from the server and then returned to it. If you use SPs or passthrough queries then only the query needs to travel over the network and the updates happen on the server.

Additionally, set-based SQL operations are usually much faster than cursor-based recordset processing.

 

[ClayG]

The overhead of using recordsets for inserts & updates depends on how the application works, and whether it uses client-side or server-side cursors. If you use a server-side cursor to retrieve one record (that is the where clause matches only one) then update it, the overhead wont matter to most interactive applications. Conversely, if you use a client-side cursor and then use ADO Find to match the record you really want, then you are retrieving a lot of data you are not really using, and it might be slow. But that really nothing to do with stored procedures, it just means you should impose all your conditions in the where clause.

 

[batteam]

OK, but if we're just adding, viewing or editing a single record in our Access .adp application, which method would cause the least network traffic - ADO/open recordsets or Insert/Update stored procedures? Or both the same for all practical purposes. Thanks.