|
Home > Archive > MS SQL Server > October 2006 > OPTION (Keepfixed plan) syntax
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
OPTION (Keepfixed plan) syntax
|
|
| Frank Rizzo 2006-10-24, 6:37 pm |
| I am at a bit of a loss as to how I can use the OPTION (KEEPFIXED PLAN)
statement in the query where I have it below. Currently, it won't
compile - it complains on invalid syntax. I am trying to prevent SQL
Server from recompiling this statement.
WHILE (1=1)
BEGIN
IF NOT EXISTS (
SELECT 1
FROM dbo.Object_Lock
WHERE ObjectClass = @chvObjectClass
AND ObjectID = @chvObjectID
AND LockFrom = @dtmLockFrom
AND LockTo = @dtmLockTo
AND LockTimeType = @inyLockTimeType
AND LockExpiration >= @dtmNow
OPTION (KEEPFIXED PLAN)
)
BEGIN
SELECT GETDATE()
END
ELSE
BEGIN
SELECT @intError = 0
,@intRowcount = 0
BREAK
END
END
| |
| John Bell 2006-10-24, 6:37 pm |
| Hi Frank
You get an error because query hints are not allowed in sub-queries and you
SELECT 1 ... query is a subquery as defined by the synax of the EXISTS clause.
John
"Frank Rizzo" wrote:
> I am at a bit of a loss as to how I can use the OPTION (KEEPFIXED PLAN)
> statement in the query where I have it below. Currently, it won't
> compile - it complains on invalid syntax. I am trying to prevent SQL
> Server from recompiling this statement.
>
>
> WHILE (1=1)
> BEGIN
> IF NOT EXISTS (
> SELECT 1
> FROM dbo.Object_Lock
> WHERE ObjectClass = @chvObjectClass
> AND ObjectID = @chvObjectID
> AND LockFrom = @dtmLockFrom
> AND LockTo = @dtmLockTo
> AND LockTimeType = @inyLockTimeType
> AND LockExpiration >= @dtmNow
> OPTION (KEEPFIXED PLAN)
> )
> BEGIN
> SELECT GETDATE()
> END
> ELSE
> BEGIN
> SELECT @intError = 0
> ,@intRowcount = 0
>
> BREAK
> END
> END
>
| |
| Hugo Kornelis 2006-10-24, 6:37 pm |
| On Wed, 11 Oct 2006 15:34:05 -0700, Frank Rizzo wrote:
>I am at a bit of a loss as to how I can use the OPTION (KEEPFIXED PLAN)
>statement in the query where I have it below. Currently, it won't
>compile - it complains on invalid syntax. I am trying to prevent SQL
>Server from recompiling this statement.
(snip)
John already identified the cause. Here's a workaround:
DECLARE @a int
SET @a = 0
>WHILE (1=1)
>BEGIN
SELECT TOP (1) @a = 1
FROM dbo.Object_Lock
WHERE ObjectClass = @chvObjectClass
AND ObjectID = @chvObjectID
AND LockFrom = @dtmLockFrom
AND LockTo = @dtmLockTo
AND LockTimeType = @inyLockTimeType
AND LockExpiration >= @dtmNow
OPTION (KEEPFIXED PLAN)
IF @a = 0
> BEGIN
> SELECT GETDATE()
> END
> ELSE
> BEGIN
> SELECT @intError = 0
> ,@intRowcount = 0
>
> BREAK
> END
>END
--
Hugo Kornelis, SQL Server MVP
| |
| Frank Rizzo 2006-10-24, 6:38 pm |
| Hugo Kornelis wrote:
> On Wed, 11 Oct 2006 15:34:05 -0700, Frank Rizzo wrote:
>
> (snip)
>
> John already identified the cause. Here's a workaround:
Thank you very much. This hit the spot.
Regards
>
> DECLARE @a int
> SET @a = 0
> SELECT TOP (1) @a = 1
> FROM dbo.Object_Lock
> WHERE ObjectClass = @chvObjectClass
> AND ObjectID = @chvObjectID
> AND LockFrom = @dtmLockFrom
> AND LockTo = @dtmLockTo
> AND LockTimeType = @inyLockTimeType
> AND LockExpiration >= @dtmNow
> OPTION (KEEPFIXED PLAN)
> IF @a = 0
>
|
|
|
|
|