I see little if any need to start writing a stored procedures with t-sql
code in it if you have the ability to make a simple view?
I guess I am asking why start writing a bunch of code when you can just use
some sql saved as a view?
The advantage of a view is that you can in fact have a linked table to that
view. And, that linked table (to the view) can be an updatable view.
Keep in mind, linking to a view is not a pass-through query anymore.
You take a performance hit, but you wind up with something that is
updatable.
I could have created a SQL view instead and linked it to Access.
Well, using a linked table to a view vs that of pass-though is a DIFFERENT
issue and a VERY different question.
You can make a pass-though query that is based on a stored proc.
you can make a pass-though query that is based on a view
You CANNOT HAVE a linked table to a stored proc, but as mentioned you can
linked table to a view.
Hence for pass-though you can go:
exec sp_YourStoredProc
or
select * from myTable
or
select * from MyView
In all 3 of the above, we taking about pass-though and this is NOT a linked
table. None of the above results are updatable in ms-access.
In addition to the above, you can link a table to a view you created on sql
server.
does one run faster than the other?
There not going to be any difference between the stored proc and the select
and the select * from MyView. as above and assuming these are ALL
pass-though.
However, there is going to be a performance hit by using linked table to a
view (and, there is not difference between linked to a table or view..they
perform the same). In this case, because a linked table (be it linked to a
table or view) is not pass-though anymore but functions though the JET
engine.
You will use JET sql syntax in this case, where as pass-though means you
use native sql server sql.
However, if you are USING where clauses to filter a report, then DO NOT use
pass-though queries because JET WILL PULL THE WHOLE table.
You are FAR FAR better off to use a linked view if you using the filter
("where" clause) in access forms and reports.
The reason for this contradiction is ms-access does not know the syntax of
the server in question when you use pass-though. So it can't filter the
pass-though unless it FIRST PULLS ALL records from the server. Ms-access
will THEN filter this data set. A pass-though is so fast often it is STILL
preferred to pull all that extra data and then filter it as opposed to being
linked to a view.
So if you need/want to restrict the data the most high speed approach is to
modify the sql in the pass-through (be it a stored proc with parameters, or
raw sql -- this is the fastest way performance wise).
However having to modify the sql in the pass-though is TOO MUCH work
for you as a developer. So, as a general rule when I going to use the
filter options of a report (the where close of openReprot), then I link
the report to a view. This is especially the case if the sql is based
on MORE then one table. If you use more then one table, then quires
based on those linked tables run quite slow, but if you link to a view,
then the joins are done server side (but you still get high speed
filters from the report).
So a linked view is a really good choice because it performs well if
joins are involved, and it also performs performer very well when opening
forms/reports that have a where clause. Pass-though quires are fast, but
they generally pull down too much data unless the actual pass-though has
the restrictions in the data already set. So, actually a view pulls LESS
data then a pass-though **if** you using the where clause of the openreport.
The other main advantage of using a linked table to a view is it is
updatable, and you can bind it to a form.
So, as a general rule, I prefer links to views for reports. they don't pull
data as fast of a pass-tough, but they pull LESS DATA with less work on my
part.
The stored procedure is a
better choice **if** you passing parameters to that procedure over and over.
eg:
dim rst as DAO.recordset
dim qdf as DAO.QueryDef
Set qdf = currentdb.QueryDefs("qryExecuteMySP")
qdf.SQL = "exec MySP 'Canada','Edmonton'
qdf.execute
In the above, we passing two parms in code to a procedure that does some
update stuff (however, we not returning records in the above with).
So, this is preferred to building the sql (update) in code and passing a
different string that selects from a view/table for example.
(each different string to you send to sql server has to be compiled and
cached in memory. Sql server will NOT likely be able to use a long sql
string passed over and over, but sql with a stored proc will use the SAME
procedure over and over in memory.
So, a stored proc is a better choice **if** you are passing parameters like
above.
In a strange twist, when dealing with large tables, you best avoid
pass-though for the reports when using the 'where' clause unless that
pass-though query ALREADY has the record restrictions you want.
