I do not want to see the results of an empty result set.

I use dynamic execution of SQL.
During the writing of this question I came up a solution, so thanks for your
attention, a solution is at the end of this message. Maybe there is a more
elegant solution.
declare @.work varchar(8000)
set @.work = 'select * from an_example_table where x = 1234'
exec @.work
And I only want to see result sets which contain rows, I do not want to see
the sets not containing rows.
(I do not want to see the hundreds of empty selections, offcourse the string
for @.work is generated).
Is there an elegant and simple solution to this problem ?
Ben Brugman.
Things I have tried
The string in work is build dynamically. I do this for hundreds of tables.
But most tables do not return a result, a count(*) would return a 0 (zero).
If I do an exec of the string and pick up the @.@.row_count there is allready
some output, which I do not want.
I tried to use a variable to count the number of rows, but I can not get
this variable outside the exec statement.
set @.work = 'select @.counter = count(*) from an_example_table where x = 1234'
the above does not work
set @.work = 'declare @.counter int select @.counter = count(*) from
an_example_table where x = 1234'
But now @.counter can not be used to test upon.
If I do not use @.counter, the information of 'empty' result set get
returned.
Is there an elegant simple solution to this problem.
SOLUTION
During the typing I came up with the following working not so elegant
solutions :
set @.work2 = 'declare @.dummy int SELECT top 1 @.dummy = 3 from
an_example_table where x = 1234'
exec (@.work2)
set @.counter = @.@.rowcount
IF @.counter <> 0 DO SOMETHING
Thanks for your time and attention just formulating the question has brought
the anwser. You being there has been enough.Ben
declare @.x nvarchar(4000)
declare @.res int
set @.x= N'set @.res = (select count(*) from ' + N'northwind.dbo.customers
where customerid=''v''' +
')'
exec sp_executesql @.x, N'@.res int output', @.res = @.res output
if @.res >0
print 'yes'
"ben brugman" <ben@.niethier.nl> wrote in message
news:eiR198WOHHA.140@.TK2MSFTNGP04.phx.gbl...
>I use dynamic execution of SQL.
> During the writing of this question I came up a solution, so thanks for
> your attention, a solution is at the end of this message. Maybe there is a
> more elegant solution.
> declare @.work varchar(8000)
> set @.work = 'select * from an_example_table where x = 1234'
> exec @.work
> And I only want to see result sets which contain rows, I do not want to
> see the sets not containing rows.
> (I do not want to see the hundreds of empty selections, offcourse the
> string for @.work is generated).
> Is there an elegant and simple solution to this problem ?
> Ben Brugman.
> Things I have tried
> The string in work is build dynamically. I do this for hundreds of tables.
> But most tables do not return a result, a count(*) would return a 0
> (zero).
> If I do an exec of the string and pick up the @.@.row_count there is
> allready some output, which I do not want.
> I tried to use a variable to count the number of rows, but I can not get
> this variable outside the exec statement.
> set @.work = 'select @.counter = count(*) from an_example_table where x => 1234'
> the above does not work
> set @.work = 'declare @.counter int select @.counter = count(*) from
> an_example_table where x = 1234'
> But now @.counter can not be used to test upon.
> If I do not use @.counter, the information of 'empty' result set get
> returned.
> Is there an elegant simple solution to this problem.
> SOLUTION
> During the typing I came up with the following working not so elegant
> solutions :
> set @.work2 = 'declare @.dummy int SELECT top 1 @.dummy = 3 from
> an_example_table where x = 1234'
> exec (@.work2)
> set @.counter = @.@.rowcount
> IF @.counter <> 0 DO SOMETHING
> Thanks for your time and attention just formulating the question has
> brought the anwser. You being there has been enough.
>|||Thanks I used your implementation, which has the advantage of delivering the
actual count.
Thanks,
Ben
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:udEbrMXOHHA.3552@.TK2MSFTNGP03.phx.gbl...
> Ben
> declare @.x nvarchar(4000)
> declare @.res int
> set @.x= N'set @.res = (select count(*) from ' + N'northwind.dbo.customers
> where customerid=''v''' +
> ')'
> exec sp_executesql @.x, N'@.res int output', @.res = @.res output
> if @.res >0
> print 'yes'
>
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eiR198WOHHA.140@.TK2MSFTNGP04.phx.gbl...
>>I use dynamic execution of SQL.
>> During the writing of this question I came up a solution, so thanks for
>> your attention, a solution is at the end of this message. Maybe there is
>> a more elegant solution.
>> declare @.work varchar(8000)
>> set @.work = 'select * from an_example_table where x = 1234'
>> exec @.work
>> And I only want to see result sets which contain rows, I do not want to
>> see the sets not containing rows.
>> (I do not want to see the hundreds of empty selections, offcourse the
>> string for @.work is generated).
>> Is there an elegant and simple solution to this problem ?
>> Ben Brugman.
>> Things I have tried
>> The string in work is build dynamically. I do this for hundreds of
>> tables. But most tables do not return a result, a count(*) would return a
>> 0 (zero).
>> If I do an exec of the string and pick up the @.@.row_count there is
>> allready some output, which I do not want.
>> I tried to use a variable to count the number of rows, but I can not get
>> this variable outside the exec statement.
>> set @.work = 'select @.counter = count(*) from an_example_table where x =>> 1234'
>> the above does not work
>> set @.work = 'declare @.counter int select @.counter = count(*) from
>> an_example_table where x = 1234'
>> But now @.counter can not be used to test upon.
>> If I do not use @.counter, the information of 'empty' result set get
>> returned.
>> Is there an elegant simple solution to this problem.
>> SOLUTION
>> During the typing I came up with the following working not so elegant
>> solutions :
>> set @.work2 = 'declare @.dummy int SELECT top 1 @.dummy = 3 from
>> an_example_table where x = 1234'
>> exec (@.work2)
>> set @.counter = @.@.rowcount
>> IF @.counter <> 0 DO SOMETHING
>> Thanks for your time and attention just formulating the question has
>> brought the anwser. You being there has been enough.
>>
>

I do not want to see the results of an empty result set.

I use dynamic execution of SQL.
During the writing of this question I came up a solution, so thanks for your
attention, a solution is at the end of this message. Maybe there is a more
elegant solution.
declare @.work varchar(8000)
set @.work = 'select * from an_example_table where x = 1234'
exec @.work
And I only want to see result sets which contain rows, I do not want to see
the sets not containing rows.
(I do not want to see the hundreds of empty selections, offcourse the string
for @.work is generated).
Is there an elegant and simple solution to this problem ?
Ben Brugman.
Things I have tried
The string in work is build dynamically. I do this for hundreds of tables.
But most tables do not return a result, a count(*) would return a 0 (zero).
If I do an exec of the string and pick up the @.@.row_count there is allready
some output, which I do not want.
I tried to use a variable to count the number of rows, but I can not get
this variable outside the exec statement.
set @.work = 'select @.counter = count(*) from an_example_table where x =
1234'
the above does not work
set @.work = 'declare @.counter int select @.counter = count(*) from
an_example_table where x = 1234'
But now @.counter can not be used to test upon.
If I do not use @.counter, the information of 'empty' result set get
returned.
Is there an elegant simple solution to this problem.
SOLUTION
During the typing I came up with the following working not so elegant
solutions :
set @.work2 = 'declare @.dummy int SELECT top 1 @.dummy = 3 from
an_example_table where x = 1234'
exec (@.work2)
set @.counter = @.@.rowcount
IF @.counter <> 0 DO SOMETHING
Thanks for your time and attention just formulating the question has brought
the anwser. You being there has been enough.Ben
declare @.x nvarchar(4000)
declare @.res int
set @.x= N'set @.res = (select count(*) from ' + N'northwind.dbo.customers
where customerid=''v''' +
')'
exec sp_executesql @.x, N'@.res int output', @.res = @.res output
if @.res >0
print 'yes'
"ben brugman" <ben@.niethier.nl> wrote in message
news:eiR198WOHHA.140@.TK2MSFTNGP04.phx.gbl...
>I use dynamic execution of SQL.
> During the writing of this question I came up a solution, so thanks for
> your attention, a solution is at the end of this message. Maybe there is a
> more elegant solution.
> declare @.work varchar(8000)
> set @.work = 'select * from an_example_table where x = 1234'
> exec @.work
> And I only want to see result sets which contain rows, I do not want to
> see the sets not containing rows.
> (I do not want to see the hundreds of empty selections, offcourse the
> string for @.work is generated).
> Is there an elegant and simple solution to this problem ?
> Ben Brugman.
> Things I have tried
> The string in work is build dynamically. I do this for hundreds of tables.
> But most tables do not return a result, a count(*) would return a 0
> (zero).
> If I do an exec of the string and pick up the @.@.row_count there is
> allready some output, which I do not want.
> I tried to use a variable to count the number of rows, but I can not get
> this variable outside the exec statement.
> set @.work = 'select @.counter = count(*) from an_example_table where x =
> 1234'
> the above does not work
> set @.work = 'declare @.counter int select @.counter = count(*) from
> an_example_table where x = 1234'
> But now @.counter can not be used to test upon.
> If I do not use @.counter, the information of 'empty' result set get
> returned.
> Is there an elegant simple solution to this problem.
> SOLUTION
> During the typing I came up with the following working not so elegant
> solutions :
> set @.work2 = 'declare @.dummy int SELECT top 1 @.dummy = 3 from
> an_example_table where x = 1234'
> exec (@.work2)
> set @.counter = @.@.rowcount
> IF @.counter <> 0 DO SOMETHING
> Thanks for your time and attention just formulating the question has
> brought the anwser. You being there has been enough.
>|||Thanks I used your implementation, which has the advantage of delivering the
actual count.
Thanks,
Ben
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:udEbrMXOHHA.3552@.TK2MSFTNGP03.phx.gbl...
> Ben
> declare @.x nvarchar(4000)
> declare @.res int
> set @.x= N'set @.res = (select count(*) from ' + N'northwind.dbo.customers
> where customerid=''v''' +
> ')'
> exec sp_executesql @.x, N'@.res int output', @.res = @.res output
> if @.res >0
> print 'yes'
>
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eiR198WOHHA.140@.TK2MSFTNGP04.phx.gbl...
>sql

I cannot explain the difference in execution plans.

I have a very strange problem with my SQL Server database that I cannot
explain. Suppose I have the following stored procedure:
CREATE PROCEDURE sp_TMS_PRJ_Test
@.TestplanVersionId uniqueidentifier = NULL
AS
SELECT @.TestplanVersionId = 'E340196B-D701-4814-8E75-04ABFD2777A5'
SELECT [TMS_PRJ_TestplanTestcases].[Id],[TMS_PRJ_TestResults].[Id]
FROM [TMS_PRJ_TestplanTestcases]
INNER JOIN [TMS_TCH_SpecificationTestcases] ON
[TMS_PRJ_TestplanTestcases].[SpecificationTestcase_Ref] =
[TMS_TCH_SpecificationTestcases].[Id]
INNER JOIN [TMS_TCH_SpecificationConditionSets] ON
[TMS_PRJ_TestplanTestcases].[ConditionSet_Ref] =
[TMS_TCH_SpecificationConditionSets].[Id]
INNER JOIN [TMS_PRJ_TestplanVersions] ON
[TMS_PRJ_TestplanTestcases].[TestplanVersion_Ref] =
[TMS_PRJ_TestplanVersions].[Id]
INNER JOIN [TMS_PRJ_Testplans] ON
[TMS_PRJ_TestplanVersions].[Testplan_Ref] = [TMS_PRJ_Testplans].[Id]
INNER JOIN [TMS_PRJ_TestResults] ON [TMS_PRJ_TestResults].[Project_Ref]
= [TMS_PRJ_Testplans].[Project_Ref]
INNER JOIN [TMS_SWP_TestcaseParameters] ON
[TMS_PRJ_TestResults].[Parameter_Ref] = [TMS_SWP_TestcaseParameters].[Id]
INNER JOIN [TMS_SWP_SoftwarePackageTestcases] ON
[TMS_SWP_TestcaseParameters].[SoftwarePackageTestcase_Ref] =
[TMS_SWP_SoftwarePackageTestcases].[Id]
INNER JOIN [TMS_SWP_UniformTestcases] ON
[TMS_SWP_UniformTestcases].[SoftwarePackageTestcase_Ref] =
[TMS_SWP_SoftwarePackageTestcases].[Id]
WHERE [TMS_PRJ_TestplanTestcases].[TestplanVersion_Ref] =
@.TestplanVersionId
AND [TMS_SWP_UniformTestcases].[UniformTestcase_Ref] =
[TMS_TCH_SpecificationTestcases].[UniformTestcase_Ref]
AND [TMS_PRJ_Testplans].[Active] <> 0
AND [TMS_PRJ_TestResults].[ConditionSet_Ref] =
[TMS_TCH_SpecificationConditionSets].[ConditionSet_Ref]
When I run this stored procedure then it takes over 4 minutes to complete
and takes almost 62 million read
operations to return 1295 rows. Suppose I rewrite this procedure like this:
CREATE PROCEDURE sp_TMS_PRJ_Test
AS
DECLARE @.TestplanVersionId AS uniqueidentifier
SELECT @.TestplanVersionId = 'E340196B-D701-4814-8E75-04ABFD2777A5'
SELECT [TMS_PRJ_TestplanTestcases].[Id],[TMS_PRJ_TestResults].[Id]
FROM [TMS_PRJ_TestplanTestcases]
INNER JOIN [TMS_TCH_SpecificationTestcases] ON
[TMS_PRJ_TestplanTestcases].[SpecificationTestcase_Ref] =
[TMS_TCH_SpecificationTestcases].[Id]
INNER JOIN [TMS_TCH_SpecificationConditionSets] ON
[TMS_PRJ_TestplanTestcases].[ConditionSet_Ref] =
[TMS_TCH_SpecificationConditionSets].[Id]
INNER JOIN [TMS_PRJ_TestplanVersions] ON
[TMS_PRJ_TestplanTestcases].[TestplanVersion_Ref] =
[TMS_PRJ_TestplanVersions].[Id]
INNER JOIN [TMS_PRJ_Testplans] ON
[TMS_PRJ_TestplanVersions].[Testplan_Ref] = [TMS_PRJ_Testplans].[Id]
INNER JOIN [TMS_PRJ_TestResults] ON [TMS_PRJ_TestResults].[Project_Ref]
= [TMS_PRJ_Testplans].[Project_Ref]
INNER JOIN [TMS_SWP_TestcaseParameters] ON
[TMS_PRJ_TestResults].[Parameter_Ref] = [TMS_SWP_TestcaseParameters].[Id]
INNER JOIN [TMS_SWP_SoftwarePackageTestcases] ON
[TMS_SWP_TestcaseParameters].[SoftwarePackageTestcase_Ref] =
[TMS_SWP_SoftwarePackageTestcases].[Id]
INNER JOIN [TMS_SWP_UniformTestcases] ON
[TMS_SWP_UniformTestcases].[SoftwarePackageTestcase_Ref] =
[TMS_SWP_SoftwarePackageTestcases].[Id]
WHERE [TMS_PRJ_TestplanTestcases].[TestplanVersion_Ref] =
@.TestplanVersionId
AND [TMS_SWP_UniformTestcases].[UniformTestcase_Ref] =
[TMS_TCH_SpecificationTestcases].[UniformTestcase_Ref]
AND [TMS_PRJ_Testplans].[Active] <> 0
AND [TMS_PRJ_TestResults].[ConditionSet_Ref] =
[TMS_TCH_SpecificationConditionSets].[ConditionSet_Ref]
In the second example the entire operation is finished in 1.3 seconds, so
this is much faster and takes only
5500 reads (and of course also returns 1295 rows). The execution plans
differ for both queries. As you can
see from the execution trees below, the fast version uses hash matches and
the slow version uses nested
loops. Can anyone explain the difference between the two queries?
PS: If I remove the '[TMS_PRJ_Testplans].[Active] <> 0' clause from the slow
query then it is fast again.
This is weird, because it only needs to check one record in this table.
Execution Tree (slow)
--
Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_PRJ_TestplanTestcases].[SpecificationTestcase_Ref],
[TMS_SWP_UniformTestcases].[UniformTestcase_Ref]) WITH PREFETCH)
|--Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_SWP_TestcaseParameters]
.[SoftwarePackageTestcase_Ref]))
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_PRJ_TestResults].[Parameter_Ref]))
| | |--Nested Loops(Inner Join,
WHERE:([TMS_PRJ_Testplans].[Id]=[TMS_PRJ_TestplanVersions].[Testplan_Ref]))
| | | |--Clustered Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_TestplanVersions].[PK_TMS_PRJ_TestplanVersions]),
SEEK:([TMS_PRJ_TestplanVersions].[Id]=[@.TestplanVersionId]) ORDERED FORWARD)
| | | |-- Filter(WHERE:(Convert([TMS_PRJ_Testplans
].[Active])<>0))
| | | |--Bookmark Lookup(BOOKMARK:([Bmk1008]),
OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_Testplans]))
| | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_PRJ_TestResults].[Project_Ref]))
| | | |--Hash Match(Inner Join,
HASH:([TMS_TCH_SpecificationConditionSet
s].[ConditionSet_Ref])=([TMS_PRJ_Tes
tResults].[ConditionSet_Ref]),
RESIDUAL:([TMS_PRJ_TestResults]. [ConditionSet_Ref]=[TMS_TCH_Specificatio
nCon
ditionSets].[ConditionSet_Ref]))
| | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_PRJ_TestplanTestcases].[ConditionSet_Ref]))
| | | | | |--Clustered Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_TestplanTestcases].[IX_TMS_PRJ_TestplanTestcases_Tes
tplanVersionSpecificationTestcaseConditi
onSet]),
SEEK:([TMS_PRJ_TestplanTestcases]. [TestplanVersion_Ref]=[@.TestplanVersionI
d]
)
ORDERED FORWARD)
| | | | | |--Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_TCH_SpecificationConditionSets].[IX_TMS_TCH_Specificatio
nConditionSets_ID]),
SEEK:([TMS_TCH_SpecificationConditionSet
s].[Id]=[TMS_PRJ_TestplanTestcases].[Con
ditionSet_Ref]) ORDERED FORWARD)
| | | | |--Clustered Index
Scan(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_TestResults].[PK_TMS_PRJ_TestResults]))
| | | |--Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_Testplans]. [IX_TMS_PRJ_Testplans_MilestoneType_Name
]
),
SEEK:([TMS_PRJ_Testplans].[Project_Ref]=[TMS_PRJ_TestResults].[Project_Ref])
ORDERED FORWARD)
| | |--Clustered Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_SWP_TestcaseParameters].[PK_TMS_SWP_TestcaseParameters])
,
SEEK:([TMS_SWP_TestcaseParameters].[Id]=[TMS_PRJ_TestResults].[Parameter_Ref]) O
RDERED FORWARD)
| |--Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_SWP_UniformTestcases].[IX_TMS_SWP_UniformTestcases_Softw
arePackageTestcaseUniformTestcase]),
SEEK:([TMS_SWP_UniformTestcases]. [SoftwarePackageTestcase_Ref]=[TMS_SWP_T
est
caseParameters].[SoftwarePackageTestcase_Ref]) ORDERED FORWARD)
|--Clustered Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_TCH_SpecificationTestcases].[PK_TMS_TCH_SpecificationTes
tcases]),
SEEK:([TMS_TCH_SpecificationTestcases].[Id]=[TMS_PRJ_TestplanTestcases].[Specifi
cationTestcase_Ref]),
WHERE:([TMS_SWP_UniformTestcases]. [UniformTestcase_Ref]=[TMS_TCH_Specifica
ti
onTestcases].[UniformTestcase_Ref]) ORDERED FORWARD)
Execution Tree (fast)
--
Hash Match(Inner Join, HASH:([TMS_TCH_SpecificationConditionSet
s].[Id],
[TMS_TCH_SpecificationTestcases].[Id])=([TMS_PRJ_TestplanTestcases].[ConditionSe
t_Ref],
[TMS_PRJ_TestplanTestcases].[SpecificationTestcase_Ref]),
RESIDUAL:([TMS_TCH_SpecificationConditio
nSets].[Id]=[TMS_PRJ_TestplanTestcases].
[ConditionSet_Ref]
AND
[TMS_TCH_SpecificationTestcases].[Id]=[TMS_PRJ_TestplanTestcases].[Specification
Testcase_Ref]))
|--Hash Match(Inner Join,
HASH:([TMS_SWP_UniformTestcases]. [UniformTestcase_Ref])=([TMS_TCH_Specifi
cat
ionTestcases].[UniformTestcase_Ref]),
RESIDUAL:([TMS_SWP_UniformTestcases].[UniformTestcase_Ref]=[TMS_TCH_Specific
ationTestcases].[UniformTestcase_Ref]))
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_SWP_TestcaseParameters]
.[SoftwarePackageTestcase_Ref]))
| | |--Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_PRJ_TestResults].[Parameter_Ref]))
| | | |--Hash Match(Inner Join,
HASH:([TMS_TCH_SpecificationConditionSet
s].[ConditionSet_Ref])=([TMS_PRJ_Tes
tResults].[ConditionSet_Ref]),
RESIDUAL:([TMS_PRJ_TestResults]. [ConditionSet_Ref]=[TMS_TCH_Specificatio
nCon
ditionSets].[ConditionSet_Ref]))
| | | | |--Index
Scan(OBJECT:([SiemensTMS].[dbo].[TMS_TCH_SpecificationConditionSets].[IX_TMS_TCH_Specificatio
nConditionSets_ID]))
| | | | |--Nested Loops(Inner Join,
WHERE:([TMS_PRJ_TestResults].[Project_Ref]=[TMS_PRJ_Testplans].[Project_Ref]
))
| | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_PRJ_TestplanVersions].[Testplan_Ref]))
| | | | | |--Clustered Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_TestplanVersions].[PK_TMS_PRJ_TestplanVersions]),
SEEK:([TMS_PRJ_TestplanVersions].[Id]=[@.TestplanVersionId]) ORDERED FORWARD)
| | | | | |--Clustered Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_Testplans].[PK_TMS_PRJ_Testplans]),
SEEK:([TMS_PRJ_Testplans].[Id]=[TMS_PRJ_TestplanVersions].[Testplan_Ref]),
WHERE:(Convert([TMS_PRJ_Testplans].[Active])<>0) ORDERED FORWARD)
| | | | |--Clustered Index
Scan(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_TestResults].[PK_TMS_PRJ_TestResults]))
| | | |--Clustered Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_SWP_TestcaseParameters].[PK_TMS_SWP_TestcaseParameters])
,
SEEK:([TMS_SWP_TestcaseParameters].[Id]=[TMS_PRJ_TestResults].[Parameter_Ref]) O
RDERED FORWARD)
| | |--Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_SWP_UniformTestcases].[IX_TMS_SWP_UniformTestcases_Softw
arePackageTestcaseUniformTestcase]),
SEEK:([TMS_SWP_UniformTestcases]. [SoftwarePackageTestcase_Ref]=[TMS_SWP_T
est
caseParameters].[SoftwarePackageTestcase_Ref]) ORDERED FORWARD)
| |--Index
Scan(OBJECT:([SiemensTMS].[dbo].[TMS_TCH_SpecificationTestcases].[IX_TMS_TCH_SpecificationTes
tcases_SpecificationVersionUniformTestca
se]))
|--Clustered Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_TestplanTestcases].[IX_TMS_PRJ_TestplanTestcases_Tes
tplanVersionSpecificationTestcaseConditi
onSet]),
SEEK:([TMS_PRJ_TestplanTestcases]. [TestplanVersion_Ref]=[@.TestplanVersionI
d]
)
ORDERED FORWARD)Google for "parameter sniffing".
Also consider using indexed views when low-selectivity status columns exist
in the tables. If [TMS_PRJ_Testplans].[Active] is such a column, then rather
than indexing the column, consider using an indexed view for each status
value.
Although in your case a simple "[TMS_PRJ_Testplans].[Active] = 1" (if value
1 is appropriate) might help.
ML
http://milambda.blogspot.com/|||Ramon de Klein (RamondeKlein@.discussions.microsoft.com) writes:
> I have a very strange problem with my SQL Server database that I cannot
> explain. Suppose I have the following stored procedure:
> CREATE PROCEDURE sp_TMS_PRJ_Test
> @.TestplanVersionId uniqueidentifier = NULL
> AS
> SELECT @.TestplanVersionId = 'E340196B-D701-4814-8E75-04ABFD2777A5'
>...
> CREATE PROCEDURE sp_TMS_PRJ_Test
> AS
> DECLARE @.TestplanVersionId AS uniqueidentifier
> SELECT @.TestplanVersionId = 'E340196B-D701-4814-8E75-04ABFD2777A5'
>...
> In the second example the entire operation is finished in 1.3 seconds, so
> this is much faster and takes only
> 5500 reads (and of course also returns 1295 rows). The execution plans
> differ for both queries. As you can
> see from the execution trees below, the fast version uses hash matches and
> the slow version uses nested
> loops. Can anyone explain the difference between the two queries?
This is something that baffles about anyone at some point in his SQL
Server career, but once you know how SQL Server builds query plans, it is
less mysterious.
Say that would hard-code the GUID into the query. In this case, SQL
Server knows exactly what you are after, and can look at the statistics
to be able to make an estimate of how many rows it will hit.
On the other hand, when you use a variable, SQL Server does not know
which value the variable will have at run-time, so it makes a standard
assumption.
For procedure parameters there is yet a choice. When you call the
procedure the first time, SQL Server uses the actual parameter value
and builds the plan according to that. Tbis means that if the first
call is for an atypical value, or, as in this, you assign a default
value, you're taking SQL Server to the cleaners.
For procedures where you need to have a parameter with a NULL default
value which in reality means something else, for instance "today" for
a date parameter, you should always copy the parameter into a local
variable, so that the optimizer does not act on incorrect information.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||When I rewrite the stored procedure, so it doesn't the parameter at all then
it is still slow. In this case I hardcoded the GUID
'E340196B-D701-4814-8E75-04ABFD2777A5' into the query, but it is still slow.
When I use an intermediate variable to push the GUID into the SELECT query
then it is fast again, so this query is fast (1 second)
SELECT @.Parameter = 'E340196B-D701-4814-8E75-04ABFD2777A5'
SELECT X,Y
FROM ...
INNER JOIN ...
WHERE Clause = @.Parameter
And this one is slow (over 4 minutes):
SELECT X,Y
FROM ...
INNER JOIN ...
WHERE Clause = 'E340196B-D701-4814-8E75-04ABFD2777A5
It doesn't have anything to do with stored procedures, because when I run it
manually in SQL Query Analyser then the results are the same. I think I have
hit a bug in SQL Server 2000.
--
Greetings,
Ramon de Klein|||Ramon de Klein (RamondeKlein@.discussions.microsoft.com) writes:
> When I rewrite the stored procedure, so it doesn't the parameter at all
> then it is still slow. In this case I hardcoded the GUID
> 'E340196B-D701-4814-8E75-04ABFD2777A5' into the query, but it is still
> slow. When I use an intermediate variable to push the GUID into the
> SELECT query then it is fast again, so this query is fast (1 second)
> SELECT @.Parameter = 'E340196B-D701-4814-8E75-04ABFD2777A5'
> SELECT X,Y
> FROM ...
> INNER JOIN ...
> WHERE Clause = @.Parameter
> And this one is slow (over 4 minutes):
> SELECT X,Y
> FROM ...
> INNER JOIN ...
> WHERE Clause = 'E340196B-D701-4814-8E75-04ABFD2777A5
> It doesn't have anything to do with stored procedures, because when I
> run it manually in SQL Query Analyser then the results are the same. I
> think I have hit a bug in SQL Server 2000.
Maybe. Or maybe not.
The likely problem here is that the statistics indicate that the
query will hit more rows for this GUID that it actually does. First
try UPDATE STATISTICS WITH FULLSCAN for this table. If you still get
the same result, post the output from DBCC SHOW_STATISTICS for this
index.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx