performance penalty using views

Is there much of a performance penalty for using views? When I use the view
it takes 42 seconds to return results but when I run the query inside the
view by itself it returns records in 5 to 7 seconds.
Would a stored procedure be better? Would the stored procedure be better at
keeping the execution plan?
Thanks,
Dan D.
Views require some extra time to look up info in system tables and although
they should offer the same execution time as the statement itself they may
have a slower execution time as a result. Although this may improve if the
execution plan is cached. A stored procedure would likely offer better
execution time over a view.
"Dan D." wrote:

> Is there much of a performance penalty for using views? When I use the view
> it takes 42 seconds to return results but when I run the query inside the
> view by itself it returns records in 5 to 7 seconds.
> Would a stored procedure be better? Would the stored procedure be better at
> keeping the execution plan?
> Thanks,
> --
> Dan D.
|||After some more testing, I've discovered that if I delete the view and then
recreate it, I get the same performance from both the view and the raw query.
I guess something got out of sync somewhere.
Thanks,
Dan D.
"Francis" wrote:
[vbcol=seagreen]
> Views require some extra time to look up info in system tables and although
> they should offer the same execution time as the statement itself they may
> have a slower execution time as a result. Although this may improve if the
> execution plan is cached. A stored procedure would likely offer better
> execution time over a view.
>
> "Dan D." wrote:

performance penalty using views

Is there much of a performance penalty for using views? When I use the view
it takes 42 seconds to return results but when I run the query inside the
view by itself it returns records in 5 to 7 seconds.
Would a stored procedure be better? Would the stored procedure be better at
keeping the execution plan?
Thanks,
--
Dan D.Views require some extra time to look up info in system tables and although
they should offer the same execution time as the statement itself they may
have a slower execution time as a result. Although this may improve if the
execution plan is cached. A stored procedure would likely offer better
execution time over a view.
"Dan D." wrote:
> Is there much of a performance penalty for using views? When I use the view
> it takes 42 seconds to return results but when I run the query inside the
> view by itself it returns records in 5 to 7 seconds.
> Would a stored procedure be better? Would the stored procedure be better at
> keeping the execution plan?
> Thanks,
> --
> Dan D.|||After some more testing, I've discovered that if I delete the view and then
recreate it, I get the same performance from both the view and the raw query.
I guess something got out of sync somewhere.
Thanks,
--
Dan D.
"Francis" wrote:
> Views require some extra time to look up info in system tables and although
> they should offer the same execution time as the statement itself they may
> have a slower execution time as a result. Although this may improve if the
> execution plan is cached. A stored procedure would likely offer better
> execution time over a view.
>
> "Dan D." wrote:
> > Is there much of a performance penalty for using views? When I use the view
> > it takes 42 seconds to return results but when I run the query inside the
> > view by itself it returns records in 5 to 7 seconds.
> >
> > Would a stored procedure be better? Would the stored procedure be better at
> > keeping the execution plan?
> >
> > Thanks,
> > --
> > Dan D.

performance penalty using views

Is there much of a performance penalty for using views? When I use the view
it takes 42 seconds to return results but when I run the query inside the
view by itself it returns records in 5 to 7 seconds.
Would a stored procedure be better? Would the stored procedure be better at
keeping the execution plan?
Thanks,
--
Dan D.Views require some extra time to look up info in system tables and although
they should offer the same execution time as the statement itself they may
have a slower execution time as a result. Although this may improve if the
execution plan is cached. A stored procedure would likely offer better
execution time over a view.
"Dan D." wrote:

> Is there much of a performance penalty for using views? When I use the vie
w
> it takes 42 seconds to return results but when I run the query inside the
> view by itself it returns records in 5 to 7 seconds.
> Would a stored procedure be better? Would the stored procedure be better a
t
> keeping the execution plan?
> Thanks,
> --
> Dan D.|||After some more testing, I've discovered that if I delete the view and then
recreate it, I get the same performance from both the view and the raw query
.
I guess something got out of sync somewhere.
Thanks,
--
Dan D.
"Francis" wrote:
[vbcol=seagreen]
> Views require some extra time to look up info in system tables and althoug
h
> they should offer the same execution time as the statement itself they may
> have a slower execution time as a result. Although this may improve if the
> execution plan is cached. A stored procedure would likely offer better
> execution time over a view.
>
> "Dan D." wrote:
>

Performance penalty for LIKE when I really mean Equal?

Hi,
I have a situation where I can retrieve either all the rows in a table or
just some of them based on a "type" field in the row. The type of the rows
to select is passed as a parm to the method that retrieves the rows. If the
calling program wants the whole table it passes String.Empty as the type.
I was thinking of having one parameterized SQL statement like this...
SELECT * FROM T1 WHERE T1.TYPE LIKE @.Type
I'll append '%' to whatever "type" is passed the method. So if the calling
routine wants all records, LIKE '%' should return all rows. If the User
passes 'OT' (assuming a two character type code) I should still get all the
'OT' rows from the passed parm of
'OT%'.
Will I suffer a performance penalty by doing this, or is the query optimizer
smart enough to figure out that for a two character field, LIKE 'OT%' is th
e
equivalent of = 'OT'?
Thanks.
BBMFor prefix criteria (i.e. where the wildcard character is not used at the
beginning of the search argument), the query optimizer can take advantage of
indexes if the column used in the LIKE expression is indexed.
ML|||If @.Type is NULL, then the following will return all rows without evaluating
the like comparison:
SELECT * FROM T1 WHERE (@.Type Is Null) or (T1.TYPE LIKE @.Type)
"BBM" <bbm@.bbmcompany.com> wrote in message
news:E8437BCA-E0D4-4D1D-9A98-5F62A95938BE@.microsoft.com...
> Hi,
> I have a situation where I can retrieve either all the rows in a table or
> just some of them based on a "type" field in the row. The type of the
> rows
> to select is passed as a parm to the method that retrieves the rows. If
> the
> calling program wants the whole table it passes String.Empty as the type.
> I was thinking of having one parameterized SQL statement like this...
> SELECT * FROM T1 WHERE T1.TYPE LIKE @.Type
> I'll append '%' to whatever "type" is passed the method. So if the
> calling
> routine wants all records, LIKE '%' should return all rows. If the User
> passes 'OT' (assuming a two character type code) I should still get all
> the
> 'OT' rows from the passed parm of
> 'OT%'.
> Will I suffer a performance penalty by doing this, or is the query
> optimizer
> smart enough to figure out that for a two character field, LIKE 'OT%' is
> the
> equivalent of = 'OT'?
> Thanks.
> BBM|||Thanks for the tip. I'll try this.
"JT" wrote:

> If @.Type is NULL, then the following will return all rows without evaluati
ng
> the like comparison:
> SELECT * FROM T1 WHERE (@.Type Is Null) or (T1.TYPE LIKE @.Type)
>
> "BBM" <bbm@.bbmcompany.com> wrote in message
> news:E8437BCA-E0D4-4D1D-9A98-5F62A95938BE@.microsoft.com...
>
>