(SQL Server 2000, SP3)
Hello all!
I've got a VIEW whose use seems to require the OPTION (ROBUST PLAN) query hint.
(Otherwise it's very dependant on the "width" of my data -- and I'll get errors if it
exceeds 8094 bytes.)
Can this hint be incorporated into the VIEW itself? When I try that, it seems to fail,
but I'm wondering if I'm putting it in the right place?
However, if I do it outside the VIEW:
select * from MyView option (robust plan)
Then it works. But, I'd prefer not to have to change my code to incorporate the OPTION
(ROBUST PLAN) query hint everywhere that I reference this VIEW.
Any suggestions?
Thanks!
John PetersonI don't see anything that states you can't use this in a view but your issue
may be outside the view itself. When you select against a view that selects
from views the optimizer tries to look at them all as a whole to get the
best plan. The view by itself may not run into this issue. I would try to
cut down the columns first and see if that helps.
--
Andrew J. Kelly
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:ej$ah9IdDHA.2932@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3)
> Hello all!
> I've got a VIEW whose use seems to require the OPTION (ROBUST PLAN) query
hint.
> (Otherwise it's very dependant on the "width" of my data -- and I'll get
errors if it
> exceeds 8094 bytes.)
> Can this hint be incorporated into the VIEW itself? When I try that, it
seems to fail,
> but I'm wondering if I'm putting it in the right place?
> However, if I do it outside the VIEW:
> select * from MyView option (robust plan)
> Then it works. But, I'd prefer not to have to change my code to
incorporate the OPTION
> (ROBUST PLAN) query hint everywhere that I reference this VIEW.
> Any suggestions?
> Thanks!
> John Peterson
>|||Hello Andrew!
Thanks for your help -- yeah, if I try to create a VIEW like:
create view Test
as
select * from sysobjects option (robust plan)
go
It will fail to create the VIEW (citing a syntax error near "option"). It's almost like I
can only have that OPTION clause "outside" of the VIEW. But, I don't want to have to
change a lot of code to explicitly put the OPTION clause in place for those objects that
are calling the VIEW -- I'd like to be able to do that in *one* place if at all possible.
Additional thoughts?
John Peterson
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uv9%23UBKdDHA.652@.tk2msftngp13.phx.gbl...
> I don't see anything that states you can't use this in a view but your issue
> may be outside the view itself. When you select against a view that selects
> from views the optimizer tries to look at them all as a whole to get the
> best plan. The view by itself may not run into this issue. I would try to
> cut down the columns first and see if that helps.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:ej$ah9IdDHA.2932@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3)
> >
> > Hello all!
> >
> > I've got a VIEW whose use seems to require the OPTION (ROBUST PLAN) query
> hint.
> > (Otherwise it's very dependant on the "width" of my data -- and I'll get
> errors if it
> > exceeds 8094 bytes.)
> >
> > Can this hint be incorporated into the VIEW itself? When I try that, it
> seems to fail,
> > but I'm wondering if I'm putting it in the right place?
> >
> > However, if I do it outside the VIEW:
> >
> > select * from MyView option (robust plan)
> >
> > Then it works. But, I'd prefer not to have to change my code to
> incorporate the OPTION
> > (ROBUST PLAN) query hint everywhere that I reference this VIEW.
> >
> > Any suggestions?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>|||Thanks, Olaf -- that's exactly what I tried. Unfortunately, that doesn't appear to work
either. The only way to get that OPTION clause seems to be in a stored procedure of the
"base" call. :-(
"Olaf Pietsch" <olaf_pietsch@.t-online.de> wrote in message
news:ewq$B7OdDHA.2296@.TK2MSFTNGP09.phx.gbl...
> "John Peterson" <j0hnp@.comcast.net> wrote
> > create view Test
> > as
> > select * from sysobjects option (robust plan)
> > go
> >
> > It will fail to create the VIEW (citing a syntax error near
> > "option"). It's almost like I can only have that OPTION clause
> > "outside" of the VIEW. But, I don't want to have to change a lot of
> > code to explicitly put the OPTION clause in place for those objects
> > that are calling the VIEW -- I'd like to be able to do that in *one*
> > place if at all possible.
> If you can use an UDF:
> CREATE FUNCTION test_function ()
> RETURNS @.table_var TABLE (n varchar(100), id int)
> AS
> BEGIN
> INSERT @.table_var SELECT [name], id from sysobjects option (robust plan)
> RETURN
> END
> GO
> CREATE VIEW test
> AS
> SELECT * FROM [dbo].[test_function]()
> GO
> --
> Olaf
>
>
>|||Hi John,
"John Peterson" <j0hnp@.comcast.net> wrote
news:OpQaFVPdDHA.2748@.tk2msftngp13.phx.gbl
> Thanks, Olaf -- that's exactly what I tried. Unfortunately, that
> doesn't appear to work either. The only way to get that OPTION
> clause seems to be in a stored procedure of the "base" call. :-(
I tested my suggestion and is running successful, I use SQL 2000 SP3.
>> If you can use an UDF:
>> CREATE FUNCTION test_function ()
>> RETURNS @.table_var TABLE (n varchar(100), id int)
>> AS
>> BEGIN
>> INSERT @.table_var SELECT [name], id from sysobjects option (robust
>> plan) RETURN
>> END
>> GO
>> CREATE VIEW test
>> AS
>> SELECT * FROM [dbo].[test_function]()
>> GO
Sorry, did you test the same version really?
The following raises an error:
CREATE FUNCTION dbo.test_fn ()
RETURNS TABLE
AS
RETURN (
SELECT [name], id from sysobjects option (robust plan)
)
Server: Msg 156, Level 15, State 1, Procedure test_fn, Line 8
Incorrect syntax near the keyword 'option'.
--
Olaf|||Oh...you know what I did:
create function Test()
returns table
as
return(select * from sysobjects option (robust plan))
go
And *that* didn't work. I had hoped that I wouldn't have to define the table structure
and just be an inline function (IsInlineFunction as opposed to IsTableFunction). But I
guess that doesn't quite work.
If all else fails, I'll probably try your suggested technique. It really bugs me that the
VIEW needs the OPTION hint -- I'm hopeful to refactor it so that it's not required.
Thanks for your help!
"Olaf Pietsch" <olaf_pietsch@.t-online.de> wrote in message
news:u8C7ZFSdDHA.728@.TK2MSFTNGP11.phx.gbl...
> Hi John,
> "John Peterson" <j0hnp@.comcast.net> wrote
> news:OpQaFVPdDHA.2748@.tk2msftngp13.phx.gbl
> > Thanks, Olaf -- that's exactly what I tried. Unfortunately, that
> > doesn't appear to work either. The only way to get that OPTION
> > clause seems to be in a stored procedure of the "base" call. :-(
> I tested my suggestion and is running successful, I use SQL 2000 SP3.
> >> If you can use an UDF:
> >>
> >> CREATE FUNCTION test_function ()
> >> RETURNS @.table_var TABLE (n varchar(100), id int)
> >> AS
> >> BEGIN
> >> INSERT @.table_var SELECT [name], id from sysobjects option (robust
> >> plan) RETURN
> >> END
> >> GO
> >>
> >> CREATE VIEW test
> >> AS
> >> SELECT * FROM [dbo].[test_function]()
> >> GO
> Sorry, did you test the same version really?
> The following raises an error:
> CREATE FUNCTION dbo.test_fn ()
> RETURNS TABLE
> AS
> RETURN (
> SELECT [name], id from sysobjects option (robust plan)
> )
> Server: Msg 156, Level 15, State 1, Procedure test_fn, Line 8
> Incorrect syntax near the keyword 'option'.
> --
> Olaf
>
. I will give this a shot. The only thing is that - afte