Forms and Recordsource Options

[KitCaz]

What's the difference if I use a view or an SP for a record source? Any
processing considerations?

E.g., since I'm planning to open multiple instances of my form, I'm going to
set my record source on the fly, contributing my record key from a global
variable* so I could do either:

RecordSource="SELECT * from vw_MyView Where myKeyField=" & global_variable

or

InputParameters = global_variable
UniqueTable = "primary_table"
RecordSource = "MySP"

Any reason I should go one way or another?

* I couldn't use Where or Filter options via a form Open argument because
for multiple instancing I'm following the technique I found on-line where I
establish a form variable and create a "new" instance, so the Open statement
doesn't happen. If someone knows why I shouldn't have this problem let me
know.

 

[Sylvain Lafontaine]

You can make much more complex statements with a SP than with a View and
they also give you a better control over the creation/reuse of query plans.
You can also set up the security a little tighter when using SPs than when
using Views.

You can also use a SP in the same way than a View - ie., on the fly, without
using the InputParameters property - by using the EXEC statement:

RecordSource="EXEC MySP " & global_variable

However, I never tried this with multiple instances of the same form. Also,
when setting the UniqueTable property, you should also set the Resync
Command property.

 

[mchlsiano]

What's the difference if I use a view or an SP for a record source? Any
processing considerations?

E.g., since I'm planning to open multiple instances of my form, I'm going
to hell
set my record source on the fly, contributing my record key from a global
variable* so I could do either:

RecordSource="SELECT * from vw_MyView Where myKeyField=" & global_variable

or

InputParameters = global_variable
UniqueTable = "primary_table"
RecordSource = "MySP"

Any reason I should go one way or another?

* I couldn't use Where or Filter options via a form Open argument because
for multiple instancing I'm following the technique I found on-line where
I
establish a form variable and create a "new" instance, so the Open
statement
doesn't happen. If someone knows why I shouldn't have this problem let me
know.

 

[a a r o n . k e m p f]

I disagree. I think that views are superior to sprocs for many
reasons:

a) if you have 4 different queries, you need 4 different sprocs
b) views are better optimized, you can easily say 'dont give me this
column' and views listen.
sprocs get every column defined in the sproc
c) views are reusable
d) views can do almost anything that a sproc can do- if you use
multiple queries in a sproc, you're doing something wrong
e) views are indexable (if they run tooi slow, you can just create an
index)
f) views are replaceable (if they run too slow, you can easily replace
the view with a table)
g) views are testable. It's not possible to determine if a sproc works
(without knowing the parameters)
it is quite easy to test views (select top 1 * From myView) to
see if it works

thus views are better, faster, safer, more flexible

Do I need to continue?

Sorry if I disagree with other people- I Just think that there are
DEFINITELY reasons to use a view (instead of a sproc).
And performance doesn't necessarily go in Sprocs favor.

I think that a lot of people use inefficient sprocs, when a simple SQL
Select statement will do the trick.

 

[Sylvain Lafontaine]

message

I disagree. I think that views are superior to sprocs for many
reasons:

a) if you have 4 different queries, you need 4 different sprocs
b) views are better optimized, you can easily say 'dont give me this
column' and views listen.
sprocs get every column defined in the sproc
c) views are reusable
d) views can do almost anything that a sproc can do- if you use
multiple queries in a sproc, you're doing something wrong
e) views are indexable (if they run tooi slow, you can just create an
index)
f) views are replaceable (if they run too slow, you can easily replace
the view with a table)
g) views are testable. It's not possible to determine if a sproc works
(without knowing the parameters)
it is quite easy to test views (select top 1 * From myView) to
see if it works

thus views are better, faster, safer, more flexible

Do I need to continue?

Yes, you need to continue.

While some of your arguments appear to be valid, like the fact that you can
index a view, nothing prohibit you from using a view inside a SP. As for
some of your other argument, like « d) views can do almost anything that a
sproc can do- if you use multiple queries in a sproc, you're doing something
wrong »; they are just plain wrong.

However, like for anything else, it's up to the developer to use the tool
that he/she think will be the best fit for a particular situation and there
are no magical solution that will work for everyone in every case.

 

[a a r o n . k e m p f]

In general-- most people develop 'way too many sprocs' and I think
that-- in general, -WAY- too many people use loops in a sproc.

also

h) views are relatively easy to test for indexing purposes-- sprocs
aren't.
Indexes help _INFINITELY_ more than 'reuse query execution plan'.
With a sproc, it could have drastically different execution plans
depending on the arguments, so it's a PITA to properly index sprocs.

i) views support code reuse. Sprocs basically don't. (UDFs _DO_
support code reuse).

j) views allow you to export the data in a simpler manner-- a virtual
table (excel, analysis services, reporting services, etc)
the problem with sprocs is that different client applications
handle parameters in different ways./
Thus-- for most end users, views are infinitely more practical than
sprocs.

 

[a a r o n . k e m p f]

and for the record:

what is more complex:
a) a couple of views

or

b) a couple of views and a couple hundred sprocs?

In general-- 'dynamic sql' isn't going away. Most of you sproc-
addicts overuse sprocs because you're scared of dynamic SQL.

Dynamic SQL is the most powerful way to use SQL Server. It is the
simplest.
It's somewhat dangerous-- if you aren't a certified DBA for example--
but yes, it's powerful.

Sprocs-- just add another level of complexity for most programming
tasks.

Yes, they're much much much better than Access queries.
I just don't think that sprocs should blindly be used everywhere
(because of query plan reuse).

-Aaron