Need to speed up query.

[Jack A]

OK Guys. I'm fed up of the query below taking too much time. I CANT
change the query since it is generated by a 3rd party product. I can
change indexes and add new indexes though.
The schema of the tables is given below. The most expensive operation
is a bookmark lookup on VGNCCB_ROLE_JT. I created the speed_up_login
index as a covering index to cover the
query but that has not seemed to help.

Any ideas, suggestions are most welcome ....


select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROL E
WHERE
ROLE_ID in (select ROLE_ID
FROM
vign.VGNCCB_ROL E_JT
WHERE
USER_NAME = 'XXXX' or GROUP_ID in (select GROUP_ID
FROM
vign.VGNCCB_GRO UP_USER_JT
WHERE
USER_NAME = 'XXXX) )

*************** *************** *************** *************** **

VGNCCB_ROLE_JT

Column_name Type
ID int
ROLE_ID int
USER_NAME nvarchar
GROUP_ID int

PK__VGNCCB_ROLE _JT__218BE82B clustered, unique, primary key located on
PRIMARY ID
speed_up_login nonclustered located on PRIMARY USER_NAME, GROUP_ID,
ROLE_ID
VGNCCB_ROLE_JT_ INDEX1 nonclustered located on PRIMARY USER_NAME
VGNCCB_ROLE_JT_ INDEX2 nonclustered located on PRIMARY GROUP_ID

*************** *************** *************** *************** ***

VGNCCB_GROUP_US ER_JT
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBla nks FixedLenNullInS ource Collation
ID int
GROUP_ID int
USER_NAME nvarchar

PK__VGNCCB_GROU P_USE__1DBB5747 clustered, unique, primary key located
on PRIMARY ID
VGNCCB_GROUP_US ER_JT_INDEX1 nonclustered located on PRIMARY GROUP_ID
VGNCCB_GROUP_US ER_JT_INDEX2 nonclustered located on PRIMARY USER_NAME

*************** *************** *************** *************** *******

[Erland Sommarskog]

Jack A (In**********@y ahoo.com) writes:

OK Guys. I'm fed up of the query below taking too much time. I CANT
change the query since it is generated by a 3rd party product. I can
change indexes and add new indexes though.
The schema of the tables is given below. The most expensive operation
is a bookmark lookup on VGNCCB_ROLE_JT. I created the speed_up_login
index as a covering index to cover the
query but that has not seemed to help.
One idea is to try whether you can make:
(select ROLE_ID
FROM
vign.VGNCCB_ROL E_JT
WHERE
USER_NAME = 'XXXX' or GROUP_ID in (select GROUP_ID
FROM
vign.VGNCCB_GRO UP_USER_JT
WHERE
USER_NAME = 'XXXX) )
into an indexed view, and hope that the optimizer picks it up. But there
are plenty of restrictions on indexed views, so if you create a view,
it may not be indexable. And even if you can, there is no guarantee that
the optimizer will use the view.
PK__VGNCCB_ROLE _JT__218BE82B clustered, unique, primary key located on
PRIMARY ID
speed_up_login nonclustered located on PRIMARY USER_NAME,
GROUP_ID,
ROLE_ID
VGNCCB_ROLE_JT_ INDEX1 nonclustered located on PRIMARY USER_NAME
VGNCCB_ROLE_JT_ INDEX2 nonclustered located on PRIMARY GROUP_ID

It seems meaningless to have the clustered index on ID. I would change
the primary key into non-clustered. Then I would try a clustered
index on ROLE_ID and keep the non-clustered indexes on USER_NAME
and GROUP_ID, hoping that the optimizer will use index intersection
on the two covering indexes. (With ROLE_ID as clustered, it will be
part of the non-clustered indexes too.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Philip Yale]

Erland Sommarskog <es****@sommars kog.se> wrote in message news:<Xn******* **************@ 127.0.0.1>...

Jack A (In**********@y ahoo.com) writes:

OK Guys. I'm fed up of the query below taking too much time. I CANT
change the query since it is generated by a 3rd party product. I can
change indexes and add new indexes though.
The schema of the tables is given below. The most expensive operation
is a bookmark lookup on VGNCCB_ROLE_JT. I created the speed_up_login
index as a covering index to cover the
query but that has not seemed to help.

One idea is to try whether you can make:

(select ROLE_ID
FROM
vign.VGNCCB_ROL E_JT
WHERE
USER_NAME = 'XXXX' or GROUP_ID in (select GROUP_ID
FROM
vign.VGNCCB_GRO UP_USER_JT
WHERE
USER_NAME = 'XXXX) )

into an indexed view, and hope that the optimizer picks it up. But there
are plenty of restrictions on indexed views, so if you create a view,
it may not be indexable. And even if you can, there is no guarantee that
the optimizer will use the view.

PK__VGNCCB_ROLE _JT__218BE82B clustered, unique, primary key located on
PRIMARY ID
speed_up_login nonclustered located on PRIMARY USER_NAME,
GROUP_ID,
ROLE_ID
VGNCCB_ROLE_JT_ INDEX1 nonclustered located on PRIMARY USER_NAME
VGNCCB_ROLE_JT_ INDEX2 nonclustered located on PRIMARY GROUP_ID

It seems meaningless to have the clustered index on ID. I would change
the primary key into non-clustered. Then I would try a clustered
index on ROLE_ID and keep the non-clustered indexes on USER_NAME
and GROUP_ID, hoping that the optimizer will use index intersection
on the two covering indexes. (With ROLE_ID as clustered, it will be
part of the non-clustered indexes too.

I quite agree with Erland about making the ID column NON-clustered,
and then using your clustered index on something more suitable.

Just for reference, though, your covered index doesn't cover the query
(if it did, you wouldn't still be seeing bookmark lookups). You
haven't included any of the columns in the SELECT clause in the index,
so it still needs to go back to the main table to get these (a truly
covered index doesn't need to go to the main table at all). However,
if you do this, your index will be about the same size as your table -
not great. Erland's suggestion of re-arranging the clustered index
onto a better column would seem to be the best.

[Erland Sommarskog]

Philip Yale (ph********@bto penworld.com) writes:

Just for reference, though, your covered index doesn't cover the query
(if it did, you wouldn't still be seeing bookmark lookups). You
haven't included any of the columns in the SELECT clause in the index,

Jack's speed_up_login index on VGNCCB_ROLE_JT is covering. I think you are
mixing up the tables.

However, since the index also includes ID, it is a variation of the
clustered index. And since there are conditions of two of the values,
the best SQL Server could do is to scan that index. And since did
not scan the table prior to adding index, there is on reason why it should
start to scan an index which is equal to the table. So speed_up_login is
probably not used.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp