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_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_USER_JT
Column_name Type Computed Length Prec
Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
ID int
GROUP_ID int
USER_NAME nvarchar
PK__VGNCCB_GROUP_USE__1DBB5747 clustered
, unique, primary
key located on PRIMARY ID
VGNCCB_GROUP_USER_JT_INDEX1 nonclustered
located on
PRIMARY GROUP_ID
VGNCCB_GROUP_USER_JT_INDEX2 nonclustered
located on
PRIMARY USER_NAME
****************************************
*******************
********Jack,
I'll have a go at it...
1. Create a nonclustered index on
VGNCCB_GROUP_USER_JT(USER_NAME,GROUP_ID)
or
Make index VGNCCB_GROUP_USER_JT_INDEX1 clustered and the primary key
nonclustered.
In fact, my guess is that the USER_NAME-GROUP_ID combination should be
unique. In that case, you should not add additional indexes, but simply
add a UNIQUE constraint to the table on (USER_NAME,GROUP_ID). This will
automatically cause SQL-Server to add a unique (nonclustered) index.
2. Change the primary key on table VGNCCB_ROLE_JT to nonclustered and
add a clustered index on (ROLE_ID). Make sure you keep the
VGNCCB_ROLE_JT_INDEX1 and VGNCCB_ROLE_JT_INDEX2 indexes.
3. Create a clustered index on VGNCCB_ROLE (ROLE_ID)
I would be interested in the query plan before and after these
changes...
Hope this helps,
Gert-Jan
Jack A wrote:
> 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_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_USER_JT
>
>
> Column_name Type Computed Length Prec
> Scale Nullable TrimTrailingBlanks
> FixedLenNullInSource Collation
> ID int
> GROUP_ID int
> USER_NAME nvarchar
> PK__VGNCCB_GROUP_USE__1DBB5747 clustered, unique, primary
> key located on PRIMARY ID
> VGNCCB_GROUP_USER_JT_INDEX1 nonclustered located on
> PRIMARY GROUP_ID
> VGNCCB_GROUP_USER_JT_INDEX2 nonclustered located on
> PRIMARY USER_NAME
> ****************************************
*******************
> ********
(Please reply only to the newsgroup)|||Jack,
Any chance you could show the query plans? I think Gert-Jan has made
good suggestions, and I would try them first.
I'm wondering, though, whether SQL Server realizes values of a column
Y retrieved are in order when a predicate is WHERE X = 'this' AND [any
predicate on Y] and it uses an index seek from an index starting with
(X,Y). If it does, it can use a merge instead of nested loops to
evaluate the INs faster. It's also not clear which IN will benefit most
from a merge instead of nested loops, if it turns out to be possible to
coax one from the optimizer. If Gert-Jan's suggestions don't help
enough, and especially if user 'XXXX' accounts for a good percentage of
the rows, it may be worth trying either
covering or clustered indexes on VGNCCB_ROLE_JT and
VGNCCB_GROUP_USER_JT that have GROUP_ID as the first column
or
changing the clustered index on VGNCCB_ROLE to (ROLE_ID, ID) and
making the clustered or covering index on VGNCCB_ROLE_JT begin with ROLE_ID.
It's late, so I might be misreading something and making poor
suggestion, but it's an interesting question, and I hope you'll let us
know how it turns out.
Steve Kass
Drew University
Jack A wrote:

>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_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_USER_JT
>
>
>Column_name Type Computed Length Prec
> Scale Nullable TrimTrailingBlanks
> FixedLenNullInSource Collation
>ID int
>GROUP_ID int
>USER_NAME nvarchar
> PK__VGNCCB_GROUP_USE__1DBB5747 clustered
, unique, primary
>key located on PRIMARY ID
> VGNCCB_GROUP_USER_JT_INDEX1 nonclustered
located on
>PRIMARY GROUP_ID
> VGNCCB_GROUP_USER_JT_INDEX2 nonclustered
located on
>PRIMARY USER_NAME
> ****************************************
*******************
>********
>