Stored Procedure or FIlter Form

[Bryan]

Hello:

Is it faster and better practise to use a stored
procedure with input parameters or to filter a recordset
in a form.

For example say I have a table of employees with approx
500 rows. I design a form to query this table depending
on certain variables such as name, position, salary etc
from controls. Should I open the form with the whole
table and apply filters and requery when a user drills
down depending on criterias selected in controls

OR

should I create a stored procedure with input parameters
that returns a new recordset everytime the user updates
the controls on the criteria form.

Suggestions or no-no's

thanks all

 

[BJ Freeman]

I am a 100% SP advocate.
1) using SP takes care of multi-user issues.
2) if the ADP blows up if does not corrupt the Tables.
3) If I want limited access to the information all the tables and view are
hidden via SQL login ID.

Now as far as speed. it does not show on a single table.
I have a 20 table Join with 5 parms. The table have 1.5meg rows, it takes
about 3 seconds via a stored procedure.

if your really interested in the overhead. go to the vba section and write a
SQL statement to be executed on the table.

then create the identical in a sP. and call it via the execute command.

Bring up the profiler and watch the interaction when both are run.

 

[Guy Horton]

Bryan,

Personally I think the faster/best practice question isn't about a choice
between whether you use a stored procedure or view and more about careful
form and query design.

Generally if performance is important to you then the golden rule is...
always try to retrieve the minimum amount of information.

To give you an example... I might create a continuous form "Employee List"
which showed the minimum amount of employee information a user needed to
make a decision about which employee record to process. If the table
contained 50 fields I might show the following:

EmployeeNo, FirstName, LastName, DateEmployed, Salary

The user would then drill down "double-click the EmployeeNo field" to an
"Employee Detail" form which would retrieve and show all data fields for
that employee only (1 record).

If the database contained 100,000 employees instead of 500 I would also
apply default filtering criteria to the "Employee List" Form which was
applied when the form was first opened. Such as showing only those employees
who joined in the last 6 months because we know that 9 times out of 10 those
records are of most interest to our users. Once the form was open the user
could adjust this filter perhaps by amending the default date range shown at
the top of the List form.

The key here is don't retrieve more data than you need to, if the user wants
to view all 100,000 employee records in the "Employee List" form they can do
it, but they accept the performance penalty associated with doing so.

Using this type of approach means that you get consistent performance
regardless of how much data is in the database. The performance limiting
factor is driven by user choice and it matters not whether you used a stored
procedure or view.

Just my 2 cents worth

Best Regards
Guy Horton

 

[Guy Horton]

Bryan,

As a follow up to my previous posting... unlike Lyle or BJ my preference is
to use updateable views (views with the metadata clause specified) for the
following reasons:

1) You can specify Where clause criteria with the Docmd.Openform command
(convenience)
2) Users require no access to the underlying tables to perform insert,
update and deletes (security)

Generally I use stored procedures when I have a task to perform that
requires a parameterised T-SQL statement, a series of T-SQL statements or
the T-SQL statement exceeds view rules regarding data updateability
(computed fields, aggregates etc).

Hope this helps

Best Regards
Guy Horton

 

[Bryan]

Excellent advice Guy. Appreciated.

-=Bryan=-

-----Original Message-----
Bryan,

Personally I think the faster/best practice question isn't about a choice
between whether you use a stored procedure or view and more about careful
form and query design.

Generally if performance is important to you then the golden rule is...
always try to retrieve the minimum amount of information.

To give you an example... I might create a continuous form "Employee List"
which showed the minimum amount of employee information a user needed to
make a decision about which employee record to process. If the table
contained 50 fields I might show the following:

EmployeeNo, FirstName, LastName, DateEmployed, Salary

The user would then drill down "double-click the EmployeeNo field" to an
"Employee Detail" form which would retrieve and show all data fields for
that employee only (1 record).

If the database contained 100,000 employees instead of 500 I would also
apply default filtering criteria to the "Employee List" Form which was
applied when the form was first opened. Such as showing only those employees
who joined in the last 6 months because we know that 9 times out of 10 those
records are of most interest to our users. Once the form was open the user
could adjust this filter perhaps by amending the default date range shown at
the top of the List form.

The key here is don't retrieve more data than you need to, if the user wants
to view all 100,000 employee records in the "Employee List" form they can do
it, but they accept the performance penalty associated with doing so.

Using this type of approach means that you get consistent performance
regardless of how much data is in the database. The performance limiting
factor is driven by user choice and it matters not whether you used a stored
procedure or view.

Just my 2 cents worth

Best Regards
Guy Horton




.