View or Stored Procedures

[Ryan]

I have a very complex view which we've been working with for some
time. As we have progressed with this view, we have reached the stage
where the data is generated into a table which is then used in our
reporting application. This has improved reporting performance
considerably.

There is a stored procedure which copies the data from the view into a
table. Very simple select into statement.

However, I was wondering if I actually held the query code in the
stored procedure, would this improve performance ?

The only user for this would be the server for getting to the data in
the view and generating into a table.

I know it's a very general question, but would I see any performance
difference doing this as an SP instead of an SP calling a view ?

Would appreciate any constructive comments. Not posting the view as
it'll be too long, but FWIW, using SQL 7.

[Simon Hayes]

"Ryan" <ry********@hotmail.com> wrote in message
news:78**************************@posting.google.c om...

I have a very complex view which we've been working with for some
time. As we have progressed with this view, we have reached the stage
where the data is generated into a table which is then used in our
reporting application. This has improved reporting performance
considerably.

There is a stored procedure which copies the data from the view into a
table. Very simple select into statement.

However, I was wondering if I actually held the query code in the
stored procedure, would this improve performance ?

The only user for this would be the server for getting to the data in
the view and generating into a table.

I know it's a very general question, but would I see any performance
difference doing this as an SP instead of an SP calling a view ?

Would appreciate any constructive comments. Not posting the view as
it'll be too long, but FWIW, using SQL 7.

It probably won't improve performance substantially, because normal views
(ie. not an MSSQL 2000 indexed view) are 'transparent' to queries. When you
query the view, its definition is plugged in to the query before being
executed. Having said that, the only way to be sure is to test, and if the
performance is equivalent, then it may come down to whether you want to
maintain one object or two. If the view is re-used in other queries, though,
or if you need it in place for security reasons, then those are good grounds
for keeping it.

Simon