Index scans while using PreparedStatements

I have a query that does a 3-table join. The tables involved
are
1. ct_list_item(li_key int, li_code nvarchar(329))
li_key is the primary key
2. ct_list_item_lang(li_key int, lang_code varchar(5),
value nvarchar(64))
li_key and lang_code form a 2-part primary key.
3. ct_list_item_map(list_key int, li_key int, list_level int)
list_key and li_key form a 2-part primary key.
All of these tables have clustered indexes on their primary
keys.
Here's the query:
SELECT map.list_key, map.li_key, lil.value, li.li_code
FROM ct_list_item li
JOIN ct_list_item_map map on map.li_key = li.li_key
JOIN ct_list_item_lang lil on li.li_key = lil.li_key and
lil.lang_code='en'
WHERE map.list_key= 1011
I am finding that when I run the query using a JDBC
PreparedStatement with bind variables (on lang_code and
list_key), the query performs an index scan over the
clustered index on the ct_list_item_lang's primary key.
However if I run the query using a JDBC Statement without
bind variables, it does an clustered index seek. I am
puzzled as to why there is a difference.
Since we hard code values in the query statement, the SQL knows the values
before hand and can use Index seek. For
preparedstatement using parameters SQL has no knowledge about the value for
each
parameter during the preparation hence Index Scan is used. This results in
Preparedstatement running slower than regular Statement with hard coded
query.
If you need to use parameterized query in code, in stead of using ad hoc
query, you can create a stored proc and call it from Java code. This
should generate a plan using Index Seek which results in better performance.
sql

Index Question

Hi,
I have the following table:
create table ProjectResource
(
ProjectCode nvarchar(20) not null,
RevisionNum int not null,
ResourceID nvarchar(15) not null,
ResourceSiteURN nvarchar(128) not null,
ActiveFlag int not null
default 1
constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
PrimaryFlag int not null
default 0
constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
(1,0)),
EPMProjectUID uniqueidentifier null ,
EPMResourceUID uniqueidentifier null ,
constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
ResourceID, ResourceSiteURN)
)
You will see that the PK constraint contains the ProjectCode, RevisionNum,
ResourceID, and ResourceSiteURN columns. I've been given a request to add a
new index on the table where it contains the following columns in the order
they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
PrimaryFlag.
What are the pros and cons of creating this new index?
Thanks in advance,
Dee
The pro is that an index that includes no more than those five columns
will be covered by the non-clustered index, and will perform a bit
faster.
The con is that the index takes up considerable space, and adds a
certain amount of overhead when rows are inserted or deleted.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 11:02:02 -0800, bpdee
<bpdee@.discussions.microsoft.com> wrote:

>Hi,
>I have the following table:
>create table ProjectResource
>(
> ProjectCode nvarchar(20) not null,
> RevisionNum int not null,
> ResourceID nvarchar(15) not null,
> ResourceSiteURN nvarchar(128) not null,
> ActiveFlag int not null
> default 1
> constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
> PrimaryFlag int not null
> default 0
> constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
>(1,0)),
> EPMProjectUID uniqueidentifier null ,
> EPMResourceUID uniqueidentifier null ,
> constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
>ResourceID, ResourceSiteURN)
>)
>You will see that the PK constraint contains the ProjectCode, RevisionNum,
>ResourceID, and ResourceSiteURN columns. I've been given a request to add a
>new index on the table where it contains the following columns in the order
>they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
>PrimaryFlag.
>What are the pros and cons of creating this new index?
>Thanks in advance,
>Dee
|||Thanks, Roy, for your quick response! I was a bit concerned at first since
we already have a clustered index on four of the five columns that are in the
non-clustered index. I thought that maybe this non-clustered index is
considered as a "duplicate" index.
"Roy Harvey (SQL Server MVP)" wrote:

> The pro is that an index that includes no more than those five columns
> will be covered by the non-clustered index, and will perform a bit
> faster.
> The con is that the index takes up considerable space, and adds a
> certain amount of overhead when rows are inserted or deleted.
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 13 Nov 2007 11:02:02 -0800, bpdee
> <bpdee@.discussions.microsoft.com> wrote:
>
|||On Tue, 13 Nov 2007 11:31:02 -0800, bpdee
<bpdee@.discussions.microsoft.com> wrote:

>Thanks, Roy, for your quick response! I was a bit concerned at first since
>we already have a clustered index on four of the five columns that are in the
>non-clustered index. I thought that maybe this non-clustered index is
>considered as a "duplicate" index.
I would not normally set up an index like that unless there was a
specific need for a covered index that it satisfied. Other than that
it really serves no purpose at all.
Roy Harvey
Beacon Falls, CT
sql

Index Question

Hi,
I have the following table:
create table ProjectResource
(
ProjectCode nvarchar(20) not null,
RevisionNum int not null,
ResourceID nvarchar(15) not null,
ResourceSiteURN nvarchar(128) not null,
ActiveFlag int not null
default 1
constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
PrimaryFlag int not null
default 0
constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
(1,0)),
EPMProjectUID uniqueidentifier null ,
EPMResourceUID uniqueidentifier null ,
constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
ResourceID, ResourceSiteURN)
)
You will see that the PK constraint contains the ProjectCode, RevisionNum,
ResourceID, and ResourceSiteURN columns. I've been given a request to add
a
new index on the table where it contains the following columns in the order
they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
PrimaryFlag.
What are the pros and cons of creating this new index?
Thanks in advance,
DeeThe pro is that an index that includes no more than those five columns
will be covered by the non-clustered index, and will perform a bit
faster.
The con is that the index takes up considerable space, and adds a
certain amount of overhead when rows are inserted or deleted.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 11:02:02 -0800, bpdee
<bpdee@.discussions.microsoft.com> wrote:

>Hi,
>I have the following table:
>create table ProjectResource
>(
> ProjectCode nvarchar(20) not null,
> RevisionNum int not null,
> ResourceID nvarchar(15) not null,
> ResourceSiteURN nvarchar(128) not null,
> ActiveFlag int not null
> default 1
> constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)
),
> PrimaryFlag int not null
> default 0
> constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
>(1,0)),
> EPMProjectUID uniqueidentifier null ,
> EPMResourceUID uniqueidentifier null ,
> constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
>ResourceID, ResourceSiteURN)
> )
>You will see that the PK constraint contains the ProjectCode, RevisionNum,
>ResourceID, and ResourceSiteURN columns. I've been given a request to add
a
>new index on the table where it contains the following columns in the order
>they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
>PrimaryFlag.
>What are the pros and cons of creating this new index?
>Thanks in advance,
>Dee|||Thanks, Roy, for your quick response! I was a bit concerned at first since
we already have a clustered index on four of the five columns that are in th
e
non-clustered index. I thought that maybe this non-clustered index is
considered as a "duplicate" index.
"Roy Harvey (SQL Server MVP)" wrote:

> The pro is that an index that includes no more than those five columns
> will be covered by the non-clustered index, and will perform a bit
> faster.
> The con is that the index takes up considerable space, and adds a
> certain amount of overhead when rows are inserted or deleted.
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 13 Nov 2007 11:02:02 -0800, bpdee
> <bpdee@.discussions.microsoft.com> wrote:
>
>|||On Tue, 13 Nov 2007 11:31:02 -0800, bpdee
<bpdee@.discussions.microsoft.com> wrote:

>Thanks, Roy, for your quick response! I was a bit concerned at first since
>we already have a clustered index on four of the five columns that are in t
he
>non-clustered index. I thought that maybe this non-clustered index is
>considered as a "duplicate" index.
I would not normally set up an index like that unless there was a
specific need for a covered index that it satisfied. Other than that
it really serves no purpose at all.
Roy Harvey
Beacon Falls, CT|||I agree with Roy. When the index key is narrow, and the row size
relatively wide, then such a "duplicate" index could be useful for
covering queries. However, in this case the row is only 36 bytes wider
than the index key of the "duplicate" index, which is probably not even
50% larger than the key size.
Had the primary key just been one int, then IMO it would have been a
different matter. As it is, I think adding this extra index is a waste
of space and will cause unnecessary contention for
inserts/updates/deletes.
BTW: I wonder why the two "Flag" columns are defined as int, and not as
something like tinyint.
Gert-Jan
bpdee wrote:[vbcol=seagreen]
> Thanks, Roy, for your quick response! I was a bit concerned at first sinc
e
> we already have a clustered index on four of the five columns that are in
the
> non-clustered index. I thought that maybe this non-clustered index is
> considered as a "duplicate" index.
> "Roy Harvey (SQL Server MVP)" wrote:
>

Index Question

Hi,
I have the following table:
create table ProjectResource
(
ProjectCode nvarchar(20) not null,
RevisionNum int not null,
ResourceID nvarchar(15) not null,
ResourceSiteURN nvarchar(128) not null,
ActiveFlag int not null
default 1
constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
PrimaryFlag int not null
default 0
constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
(1,0)),
EPMProjectUID uniqueidentifier null ,
EPMResourceUID uniqueidentifier null ,
constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
ResourceID, ResourceSiteURN)
)
You will see that the PK constraint contains the ProjectCode, RevisionNum,
ResourceID, and ResourceSiteURN columns. I've been given a request to add a
new index on the table where it contains the following columns in the order
they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
PrimaryFlag.
What are the pros and cons of creating this new index?
Thanks in advance,
DeeThe pro is that an index that includes no more than those five columns
will be covered by the non-clustered index, and will perform a bit
faster.
The con is that the index takes up considerable space, and adds a
certain amount of overhead when rows are inserted or deleted.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 11:02:02 -0800, bpdee
<bpdee@.discussions.microsoft.com> wrote:
>Hi,
>I have the following table:
>create table ProjectResource
>(
> ProjectCode nvarchar(20) not null,
> RevisionNum int not null,
> ResourceID nvarchar(15) not null,
> ResourceSiteURN nvarchar(128) not null,
> ActiveFlag int not null
> default 1
> constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
> PrimaryFlag int not null
> default 0
> constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
>(1,0)),
> EPMProjectUID uniqueidentifier null ,
> EPMResourceUID uniqueidentifier null ,
> constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
>ResourceID, ResourceSiteURN)
>)
>You will see that the PK constraint contains the ProjectCode, RevisionNum,
>ResourceID, and ResourceSiteURN columns. I've been given a request to add a
>new index on the table where it contains the following columns in the order
>they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
>PrimaryFlag.
>What are the pros and cons of creating this new index?
>Thanks in advance,
>Dee|||Thanks, Roy, for your quick response! I was a bit concerned at first since
we already have a clustered index on four of the five columns that are in the
non-clustered index. I thought that maybe this non-clustered index is
considered as a "duplicate" index.
"Roy Harvey (SQL Server MVP)" wrote:
> The pro is that an index that includes no more than those five columns
> will be covered by the non-clustered index, and will perform a bit
> faster.
> The con is that the index takes up considerable space, and adds a
> certain amount of overhead when rows are inserted or deleted.
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 13 Nov 2007 11:02:02 -0800, bpdee
> <bpdee@.discussions.microsoft.com> wrote:
> >Hi,
> >
> >I have the following table:
> >
> >create table ProjectResource
> >(
> > ProjectCode nvarchar(20) not null,
> > RevisionNum int not null,
> > ResourceID nvarchar(15) not null,
> > ResourceSiteURN nvarchar(128) not null,
> > ActiveFlag int not null
> > default 1
> > constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
> > PrimaryFlag int not null
> > default 0
> > constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
> >(1,0)),
> > EPMProjectUID uniqueidentifier null ,
> > EPMResourceUID uniqueidentifier null ,
> > constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
> >ResourceID, ResourceSiteURN)
> >)
> >
> >You will see that the PK constraint contains the ProjectCode, RevisionNum,
> >ResourceID, and ResourceSiteURN columns. I've been given a request to add a
> >new index on the table where it contains the following columns in the order
> >they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
> >PrimaryFlag.
> >
> >What are the pros and cons of creating this new index?
> >
> >Thanks in advance,
> >Dee
>|||On Tue, 13 Nov 2007 11:31:02 -0800, bpdee
<bpdee@.discussions.microsoft.com> wrote:
>Thanks, Roy, for your quick response! I was a bit concerned at first since
>we already have a clustered index on four of the five columns that are in the
>non-clustered index. I thought that maybe this non-clustered index is
>considered as a "duplicate" index.
I would not normally set up an index like that unless there was a
specific need for a covered index that it satisfied. Other than that
it really serves no purpose at all.
Roy Harvey
Beacon Falls, CT|||I agree with Roy. When the index key is narrow, and the row size
relatively wide, then such a "duplicate" index could be useful for
covering queries. However, in this case the row is only 36 bytes wider
than the index key of the "duplicate" index, which is probably not even
50% larger than the key size.
Had the primary key just been one int, then IMO it would have been a
different matter. As it is, I think adding this extra index is a waste
of space and will cause unnecessary contention for
inserts/updates/deletes.
BTW: I wonder why the two "Flag" columns are defined as int, and not as
something like tinyint.
--
Gert-Jan
bpdee wrote:
> Thanks, Roy, for your quick response! I was a bit concerned at first since
> we already have a clustered index on four of the five columns that are in the
> non-clustered index. I thought that maybe this non-clustered index is
> considered as a "duplicate" index.
> "Roy Harvey (SQL Server MVP)" wrote:
> > The pro is that an index that includes no more than those five columns
> > will be covered by the non-clustered index, and will perform a bit
> > faster.
> >
> > The con is that the index takes up considerable space, and adds a
> > certain amount of overhead when rows are inserted or deleted.
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> >
> > On Tue, 13 Nov 2007 11:02:02 -0800, bpdee
> > <bpdee@.discussions.microsoft.com> wrote:
> >
> > >Hi,
> > >
> > >I have the following table:
> > >
> > >create table ProjectResource
> > >(
> > > ProjectCode nvarchar(20) not null,
> > > RevisionNum int not null,
> > > ResourceID nvarchar(15) not null,
> > > ResourceSiteURN nvarchar(128) not null,
> > > ActiveFlag int not null
> > > default 1
> > > constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
> > > PrimaryFlag int not null
> > > default 0
> > > constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
> > >(1,0)),
> > > EPMProjectUID uniqueidentifier null ,
> > > EPMResourceUID uniqueidentifier null ,
> > > constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
> > >ResourceID, ResourceSiteURN)
> > >)
> > >
> > >You will see that the PK constraint contains the ProjectCode, RevisionNum,
> > >ResourceID, and ResourceSiteURN columns. I've been given a request to add a
> > >new index on the table where it contains the following columns in the order
> > >they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
> > >PrimaryFlag.
> > >
> > >What are the pros and cons of creating this new index?
> > >
> > >Thanks in advance,
> > >Dee
> >

Index on Computed column or Indexed View

I have a large nvarchar(2000) that need to be queried on often based on a
subset of the data (first 50 characters). The application creating and using
the data cannot be modified to capture a short and long column... I was
wondering if creating a computed column with the formula being
Left(longcolumn, 50) and creating an index based on this column could be a
good option? Or would it be preferable to create an indexed view?
Any other suggestion are welcomed
Thank you for your helpI would go with a computed column to start with.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Martin Rajotte" <MartinRajotte@.discussions.microsoft.com> wrote in message
news:0EB763CD-5C4E-455C-83E2-1454742D5507@.microsoft.com...
> I have a large nvarchar(2000) that need to be queried on often based on a
> subset of the data (first 50 characters). The application creating and
using
> the data cannot be modified to capture a short and long column... I was
> wondering if creating a computed column with the formula being
> Left(longcolumn, 50) and creating an index based on this column could be a
> good option? Or would it be preferable to create an indexed view?
> Any other suggestion are welcomed
> Thank you for your help
>|||Thank you for help. It confirms my tests that I performed last night.
"Narayana Vyas Kondreddi" wrote:

> I would go with a computed column to start with.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Martin Rajotte" <MartinRajotte@.discussions.microsoft.com> wrote in messag
e
> news:0EB763CD-5C4E-455C-83E2-1454742D5507@.microsoft.com...
> using
>
>

INDEX and VIEWS

Hi all,
Let say that I have a table Customer
CREATE TABLE [dbo].[Customer] (
[CustomerId] [int] NOT NULL ,
[CustomerName] [nvarchar] (50),
[CustomerAge] [int] NOT NULL
) ON [PRIMARY]
GO
Let say I have an index on CustomerAge.
If I have a view defined as:
CREATE VIEW dbo.VIEWCustomer
AS
SELECT dbo.Customer.*
FROM dbo.Customer
and then if I execute the following SQL statement:
select * from VIEWCustomer where CustomerAge = 25
Will that statement use the index of the table Customer (on the field
CustomerAge) or will it not (because no index can be defined on a view)?
In other words, if a select on a view is using a WHERE clause for which
there is an index defined for the table.field defined in the view, will it
be used or not?
Best regards,
Francois MalgreveYou can check this yourself by examining the execution plan in Query
Analyzer. The indexes certainly can be used when referencing a view in
just the same way as they are with tables.
David Portas
SQL Server MVP
--