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

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
>***********************************************************
>********
>