Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
llahiff
Starting Member
6 Posts |
 Posted - 2005-12-21 : 19:01:42
|
| Happy Holidays,Does anyone know why I would get a different execution plan when I use sp_prepare versus a straight T-SQL query?For example, I have the following table:CREATE TABLE TableA ( ColP int NOT NULL, Col2 varchar (200), Col3 varchar (200), Col4 varchar (1), CONSTRAINT PK_TableA PRIMARY KEY CLUSTERED (ColP)With the below index:CREATE INDEX IX_TableA ON TableA(Col2, Col3, Col4)I am using mostly prepare statment in a JDBC environment. When the below prepare statement is used, I get a terrible execution plan and a terrible response time.declare @P1 intexec sp_prepare @P1 output, N'@P1 nvarchar(200),@P2 nvarchar(1),@P3 nvarchar(200)', N'select ColP, Col2, Col3, Col4 from TableA where Col2=@P1 and Col3=@P2 and Col4=@P3 'exec sp_execute @P1, N'attcctctcccctggaacccccagatccacaactttggggtgcat', N'gcacagccacactttgtcttaccctaataaaacccagacctttg', N'r'Now, if I just run the query with all the parameters already provided. The optimizer uses an extrememly fast execution plan:select ColP, Col2, Col3, Col4 from TableA where Col2='attcctctcccctggaacccccagatccacaactttggggtgcat' and Col3='gcacagccacactttgtcttaccctaataaaacccagacctttg' and Col4='r'Any help is greatly appreciated.Lorinda |
|
|
llahiff
Starting Member
6 Posts |
Posted - 2005-12-21 : 22:35:25
|
| One more thing, I failed to mention that the table has 1.4 million rows.Lorinda |
 |
|
|
|
|
|
|
|
Different execution plan with sp_prepare