Need to speed up query.

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_ROLE
WHERE
ROLE_ID in (select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT
WHERE
USER_NAME = 'XXXX' or GROUP_ID in (select GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT
WHERE
USER_NAME = 'XXXX) )

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

VGNCCB_ROLE_JT

Column_nameType
IDint
ROLE_IDint
USER_NAMEnvarchar
GROUP_IDint

PK__VGNCCB_ROLE_JT__218BE82Bclustered, unique, primary key located on
PRIMARYID
speed_up_loginnonclustered located on PRIMARYUSER_NAME, GROUP_ID,
ROLE_ID
VGNCCB_ROLE_JT_INDEX1nonclustered located on PRIMARYUSER_NAME
VGNCCB_ROLE_JT_INDEX2nonclustered located on PRIMARYGROUP_ID

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

VGNCCB_GROUP_USER_JT

Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation
IDint
GROUP_IDint
USER_NAMEnvarchar

PK__VGNCCB_GROUP_USE__1DBB5747clustered, unique, primary key located
on PRIMARYID
VGNCCB_GROUP_USER_JT_INDEX1nonclustered located on PRIMARYGROUP_ID
VGNCCB_GROUP_USER_JT_INDEX2nonclustered located on PRIMARYUSER_NAME

************************************************** *****************Jack A (InformixMail@.yahoo.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_ROLE_JT
> WHERE
> USER_NAME = 'XXXX' or GROUP_ID in (select GROUP_ID
> FROM
> vign.VGNCCB_GROUP_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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns951B54A8CD1CYazorman@.127.0.0.1>...
> Jack A (InformixMail@.yahoo.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_ROLE_JT
> > WHERE
> > USER_NAME = 'XXXX' or GROUP_ID in (select GROUP_ID
> > FROM
> > vign.VGNCCB_GROUP_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.|||Philip Yale (philipyale@.btopenworld.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, esquel@.sommarskog.se

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