Another Slow Execution Plan with sp_prepare

Thanks to Erland, my issue with sp_prepare of NVarchar and Varchar has been
resolved by changing a parameter in JDBC to force it to use Varchar instead
of NVarchar. JDBC's default setting uses NVarchar. Look under Subject "Slo
w
Query with sp_prepare and sp_execute" to see the thread.
However, I now have a problem with the INT datatype in my prepare
statements. I have tried using various numeric data types with sp_prepare.
But none gives me a response time as fast as running a single query.
For example, I have the following table:
CREATE TABLE Segment (
SegmentId int NOT NULL,
SegmentSetId int NOT NULL,
Sequence varchar (255) NOT NULL,
CONSTRAINT PK_Segment PRIMARY KEY CLUSTERED (SegmentId)
With the below index:
CREATE INDEX IX_Segment ON Segment(SegmentSetId)
The table has 6 million rows. I am using prepare statments in a JDBC
environment. When the below prepare statement is used, the execution plan
uses index scan which generates a slow response time:
Declare @.P1 int
Exec sp_prepare @.P1 output,
N'@.P1 int',
N'select * from Segment where SegmentSetId=@.P1'
Select @.P1
Exec sp_execute @.P1,649
Execution Tree
--
Parallelism(Gather Streams)
|--Clustered Index Scan(OBJECT:([Dev].[dbo].[Segment].[PK_Segment]),
WHERE:([Segment].[SegmentSetId]=[@.P1]))
Now, if I just run the query with all the parameters already provided, the
execution plan
uses index s which generates a fast response time:
select * from Segment where SegmentSetId=649
Execution Tree
--
Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Dev].[dbo].[Segment]))
|--Index S(OBJECT:([Dev].[dbo].[Segment].[IX_Segment]),
SEEK:([Segment].[SegmentSetId]=Convert([@.1])) ORDERED FORWARD)
Can anyone give me a hint how to resolve this one? What can I use in my
prepared statement to force the optimizer to use an index s instead of an
index scan?
Again, any help will be greatly appreciated!Hi
If you add to this statement (AND 1=1) do you see an index s?
N'select * from Segment where SegmentSetId=@.P1 and 1=1'
"lorinda" <lorinda@.community.nospam> wrote in message
news:121EF92B-554B-4D96-AFCA-C6E6F0A5D757@.microsoft.com...
> Thanks to Erland, my issue with sp_prepare of NVarchar and Varchar has
> been
> resolved by changing a parameter in JDBC to force it to use Varchar
> instead
> of NVarchar. JDBC's default setting uses NVarchar. Look under Subject
> "Slow
> Query with sp_prepare and sp_execute" to see the thread.
> However, I now have a problem with the INT datatype in my prepare
> statements. I have tried using various numeric data types with
> sp_prepare.
> But none gives me a response time as fast as running a single query.
> For example, I have the following table:
> CREATE TABLE Segment (
> SegmentId int NOT NULL,
> SegmentSetId int NOT NULL,
> Sequence varchar (255) NOT NULL,
> CONSTRAINT PK_Segment PRIMARY KEY CLUSTERED (SegmentId)
> With the below index:
> CREATE INDEX IX_Segment ON Segment(SegmentSetId)
> The table has 6 million rows. I am using prepare statments in a JDBC
> environment. When the below prepare statement is used, the execution plan
> uses index scan which generates a slow response time:
> Declare @.P1 int
> Exec sp_prepare @.P1 output,
> N'@.P1 int',
> N'select * from Segment where SegmentSetId=@.P1'
> Select @.P1
> Exec sp_execute @.P1,649
> Execution Tree
> --
> Parallelism(Gather Streams)
> |--Clustered Index Scan(OBJECT:([Dev].[dbo].[Segment].[PK_Segment]),
> WHERE:([Segment].[SegmentSetId]=[@.P1]))
> Now, if I just run the query with all the parameters already provided, the
> execution plan
> uses index s which generates a fast response time:
> select * from Segment where SegmentSetId=649
> Execution Tree
> --
> Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Dev].[dbo].[Segment]))
> |--Index S(OBJECT:([Dev].[dbo].[Segment].[IX_Segment]),
> SEEK:([Segment].[SegmentSetId]=Convert([@.1])) ORDERED FORWARD)
> Can anyone give me a hint how to resolve this one? What can I use in my
> prepared statement to force the optimizer to use an index s instead of
> an
> index scan?
> Again, any help will be greatly appreciated!
>|||lorinda (lorinda@.community.nospam) writes:
> However, I now have a problem with the INT datatype in my prepare
> statements. I have tried using various numeric data types with
> sp_prepare. But none gives me a response time as fast as running a
> single query.
> For example, I have the following table:
> CREATE TABLE Segment (
> SegmentId int NOT NULL,
> SegmentSetId int NOT NULL,
> Sequence varchar (255) NOT NULL,
> CONSTRAINT PK_Segment PRIMARY KEY CLUSTERED (SegmentId)
> With the below index:
> CREATE INDEX IX_Segment ON Segment(SegmentSetId)
> The table has 6 million rows. I am using prepare statments in a JDBC
> environment. When the below prepare statement is used, the execution plan
> uses index scan which generates a slow response time:
>...
> Now, if I just run the query with all the parameters already provided, the
> execution plan
> uses index s which generates a fast response time:
> select * from Segment where SegmentSetId=649
So this is a completely different issue from the varchar thing.
This is the classical choice between using a non-clustered index or
scanning the table. If there are two rows with SegmentSetID = 649 the
index is alright. But if there is a million, it's faster to find them
by scanning the table, because using the index would require many
page accesses to the same page.
SQL Server has statistics from which it makes estimates. At least for
stored procedure it sniffs the input parameter value when it builds
the plan and puts in cache. I would assume that this is the case
with sp_prepare as well. So I would expect it to use the index. Then
again, if you first attempt was with SegmentSetID = 0, and there are
a million rows with id 0, then what ended up in the cache was the
clustered index scan.
You can force the index with an index hint:
SELECT * FROM Segment WITH (INDEX = IX_Segment) WHERE ...
but this can come back bite when you are asking for that SegmentSetID
with lots of rows.
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|||This makes sense. Looks like I will have to find a work around. Thanks
again for your help!