Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Recordset or as needed ?

Author  Topic 

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-05-13 : 09:44:23
When retrieving data from the server for use in the front end application, does it really make a difference if you retrieve an entire recordset and work with it, requierying every now and then, or working with a record at a time when needed using stored procedures. What is the best method?

Lets say for example, I have a table that holds companies and one that holds contacts for each company. Now should I obtain a recordset of all the companies and contacts (adUseClient) and when displaying, editing, adding, removing, from these recordsets, update at will and requery every so often to ensure the recordsets are in sync with what other users are doing. Or should I just work with the data on a "On demand" basis, when a company is selected, retrieve its corresponding information? When the user wants to change, add, or delete a record, send the parameters to a stored proc.

Mike B


TurdSpatulaWarrior
Starting Member

36 Posts

Posted - 2004-05-13 : 10:03:31
That depends on a number of factors. How many users will the system have on it at any given time? How large are your tables? How frequently does the data in these tables change? Generally, it is pointless to tie up resources retrieving data that is not needed. Perhaps if you provide a little more detail, someone here can point you in the proper direction.
Go to Top of Page

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-05-13 : 10:21:29
quote:
Originally posted by TurdSpatulaWarrior

That depends on a number of factors. How many users will the system have on it at any given time? How large are your tables? How frequently does the data in these tables change? Generally, it is pointless to tie up resources retrieving data that is not needed. Perhaps if you provide a little more detail, someone here can point you in the proper direction.


- Well, not many users at all maybe 10 using this information concurrently.

- Maybe add a handful of records each day but do not expect the company table to exceed 1000 entries.

The company tables are used simply to provide "Company" selection for certain activities. This is an estimating software applciation so the user will entry a certain job to be done, select the company to do it, and enter an rate, blah, blah, blah :).

This part of the design always confuses me because I don't see how one can be better then the other.

Mike B
Go to Top of Page

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2004-05-13 : 11:30:28
I feel your DATA ON A DEMAND basis is the proper one. Its unlikely that you will get into situations updating multiple COMPANY profiles.

The problem with selecting all the data in the record set is COLLISIONS (If multiple users are trying to update the data). Also this will take more memory, table scans and n/w bandwidth.


------------------------
I think, therefore I am
Go to Top of Page

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2004-05-13 : 11:57:44
quote:
Originally posted by ravilobo
The problem with selecting all the data in the record set is COLLISIONS (If multiple users are trying to update the data). Also this will take more memory, table scans and n/w bandwidth.


------------------------
I think, therefore I am



That was my main concern.

Thanks for the reply

Mike
Go to Top of Page
   

- Advertisement -