Which is Better for Returning Simple Value: View, Stored Proc or Function?

[bullfrog83]

I'm working in an Access 07 project tied to a sql server db. In my continuing effort to optimize application performance I would like to know which is better at returning just a simple value for a variable: a view, stored proc, or function? Here are two examples:

 sngDiscCredits = Nz(DSum("Credit_Grade", "vwStdDiscClassCreditsCourses", "StdDegReqDiscId = " & lngDiscId))

or

sngDiscCreditMin = DSum("CreditMin", "StdDegReqClass", "StdDegReqDiscId = " & lngDiscId)

The first example is already aggregating against a view and the second is against a table. All three work but I'm trying to figure out which is the best in this scenario. I understand the basic differences between all three but sometimes I have difficulty figuring out which one to use when one or more can return the results I need. Thanks!

[nico5038]

Creating a "Pass-Through" query will be the fastest. It will have to be written in MS-SQL, check the help for details.

Nic;o)

[bullfrog83]

I don't think I have that option working in a .adp. I do remember seeing it when I was working in an .mdb, however.

[nico5038]

When working directly with MS SQL views and procedures, the view will be faster as a procedure (when no proc's are used in the view). A view is executed by the database engine, the procedure will require an interpreter to execute the code and in general recordset processing is slower as a query/view.

Nic;o)

[Jim Doherty]

I'm working in an Access 07 project tied to a sql server db. In my continuing effort to optimize application performance I would like to know which is better at returning just a simple value for a variable: a view, stored proc, or function? Here are two examples:

 sngDiscCredits = Nz(DSum("Credit_Grade", "vwStdDiscClassCreditsCourses", "StdDegReqDiscId = " & lngDiscId))

or

sngDiscCreditMin = DSum("CreditMin", "StdDegReqClass", "StdDegReqDiscId = " & lngDiscId)

The first example is already aggregating against a view and the second is against a table. All three work but I'm trying to figure out which is the best in this scenario. I understand the basic differences between all three but sometimes I have difficulty figuring out which one to use when one or more can return the results I need. Thanks!

Use a Function....you can embed a function in a view where it gets presented as a column in much the same way as though you were using DSum in an mdb file. You cannot embed a stored procedure in a view.

Both Stored procedures and views are server side and return only the rows and columns you define restricted by criteria to the client

There really is no reason in my experience to use Domain Aggregate functions when working with ADP files if speed and efficiency is your ultimate aim and your design focus is fashioned to be more sympathetic to server side rather than client based.

Keep all of your processing optimised server side wherever possible to take advantage of pre-compilation, saved execution plans and the overall architectural efficiency of sql server

[bullfrog83]

Use a Function....you can embed a function in a view where it gets presented as a column in much the same way as though you were using DSum in an mdb file. You cannot embed a stored procedure in a view.

Both Stored procedures and views are server side and return only the rows and columns you define restricted by criteria to the client

There really is no reason in my experience to use Domain Aggregate functions when working with ADP files if speed and efficiency is your ultimate aim and your design focus is fashioned to be more sympathetic to server side rather than client based.

Keep all of your processing optimised server side wherever possible to take advantage of pre-compilation, saved execution plans and the overall architectural efficiency of sql server

Okay, I'm creating functions and calling them in a view vwStdDegReqTota ls. However, when referencing this view in vba I'm running into a datatype mismatch error and I'm not sure why. This is the vba:

Dim lngDiscCourses As Long

lngDiscCourses = "SELECT DiscCoursesTaken FROM vwStdDegReqTotals WHERE StdDegReqDiscId = " & lngDiscId

This is the view:

SELECT     D.StdDegReqDiscId, 
dbo.fnCountStdDiscCoursesTaken(D.StdDegReqDiscId) AS DiscCoursesTaken, dbo.fnCountStdDiscClassTotal(D.StdDegReqDiscId) AS DiscClassCount
FROM         StdDegReqDisc D

This is the function for view column DiscCoursesTake n:

CREATE FUNCTION dbo.fnCountStdDiscCoursesTaken
(
@StdDegReqDiscId int
)
RETURNS int
AS
BEGIN
	DECLARE @DiscCoursesTaken int
	SELECT @DiscCoursesTaken = COUNT(StdDegReqDiscId)
	FROM StdDegReqCourse Cr INNER JOIN
	StdDegReqClass Cl ON Cr.StdDegReqClassId = Cl.StdDegReqClassId
	WHERE TakenCourseId IS NOT NULL AND StdDegReqDiscId = @StdDegReqDiscId
RETURN @DiscCoursesTaken
END
GO

[Jim Doherty]

You are throwing a string into a LONG declared variable look at it

Dim lngDiscCourses As LONG 
  
lngDiscCourses = "SELECT DiscCoursesTaken FROM vwStdDegReqTotals WHERE StdDegReqDiscId = " & lngDiscId

[bullfrog83]

You are throwing a string into a LONG declared variable look at it

Dim lngDiscCourses As LONG 
  
lngDiscCourses = "SELECT DiscCoursesTaken FROM vwStdDegReqTotals WHERE StdDegReqDiscId = " & lngDiscId

Then how do I set lngDiscCourses to equal the column DiscCoursesTake n from the view?

[Jim Doherty]

Then how do I set lngDiscCourses to equal the column DiscCoursesTake n from the view?

You simply open the view as an ADO recordset using the criteria applied in your where clause and grab the column (DiscCoursesTak en) value returned by the function as you would ordinarily

[bullfrog83]

You simply open the view as an ADO recordset using the criteria applied in your where clause and grab the column (DiscCoursesTak en) value returned by the function as you would ordinarily

Duh. Thank you!

[Jim Doherty]

Duh. Thank you!

You are welcome :)