Performance problems with query

Guys,
I'm stumped. While its not pertinent to the
matter, we are running a Vignette content management
system on Win2k with Sql 2000 Enterprise on a cluster.
The server has 2 Gig of RAM , 2 CPU's and the database
size is 1.5G.

The query below is fired at login. The indexes
seem fine based on the query plan. When I look through
profiler, the query below takes a very high # of CPU
cycles and reads. It consistently takes more than 1.5
seconds to execute the query below. I did a dbcc pintable
for ALL the tables in the query and that did not help
either. It seemed to make it worse (3 seconds and above)

Any idea what could be the issue here? The server
is not really heavily taxed.

The tables are small. They have very few rows.

VGNCCB_ROLE939
VGNCCB_ROLE_JT62389
VGNCCB_GROUP_USER_JT1364

The problem Query:

select
ROLE_ID,
NAME,
DESCRIPTION,
CREATE_DATE,
MODIFIED_DATE
FROM
vign.VGNCCB_ROLE -- Clustered Indexed on Role ID
WHERE
ROLE_ID in
(select ROLE_ID
FROM
vign.VGNCCB_ROLE_JT -- Non clustered indexes
on USER_NAME AND non clustered on GROUP_ID
WHERE
USER_NAME = 'testRole' or GROUP_ID in (select
GROUP_ID
FROM
vign.VGNCCB_GROUP_USER_JT -- Non clustered
index on USER_NAME
WHERE
USER_NAME = 'testRole'))

I'd appreciate it if someone could follow me in this
thread to completion. Such a simple query should not take
this long.

TIA,
Jack
...[posted and mailed, please reply in news]

Jack A (InformixMail@.yahoo.com) writes:
> The query below is fired at login. The indexes
> seem fine based on the query plan. When I look through
> profiler, the query below takes a very high # of CPU
> cycles and reads. It consistently takes more than 1.5
> seconds to execute the query below. I did a dbcc pintable
> for ALL the tables in the query and that did not help
> either. It seemed to make it worse (3 seconds and above)

DBCC PINTABLE is a command that very rarely is useful. If you have a
situation that you have a table that is referred to rearely, but
when it is referred to, you want the answers directly. Then you
have a case. Since these tables are referred to at log in and small,
I would assume that they are in memory anyway.

I could think of a possible rewrites of the query, but since this appears
to come from a third-party app, you don't seem to have any use for
that.

Without having the full information about the tables it is difficult
to say, but if it is correct that VGNCCB_ROLE_JT does not have a
clustered index, I think it is time to add one, and that would be
on (ROLE_ID). That could make the two indexes on USER_NAME and GROUP_ID
covering for the query, and could save you some bookmark lookups.

Another idea is to build an indexed view, and hope that SQL Server
will find the indexed view when looking for a query plan. But I am
not sure this is possible. And in any case, you need to have Enterprise
Edition for this to work.

I would encourage you to post the complete CREATE TABLE and CREATE INDEX
scripts for the tables. That makes it a little easier to guess.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK , Here goes with the table structure. BTW I've run DBCC reindex.

TABLE: VGNCCB_ROLE
PK__VGNCCB_ROLE__1FA39FB9clustered, unique, primary key located on
PRIMARY -- ROLE_ID

TABLE: VGNCCB_ROLE_JT
index_nameindex_descriptionindex_keys
PK__VGNCCB_ROLE_JT__218BE82Bclustered, unique, primary key located on
PRIMARY- ID
VGNCCB_ROLE_JT_INDEX1nonclustered located on PRIMARY- USER_NAME
VGNCCB_ROLE_JT_INDEX2nonclustered located on PRIMARY- GROUP_ID

TABLE: VGNCCB_GROUP_USER_JT
index_nameindex_descriptionindex_keys
PK__VGNCCB_GROUP_USE__1DBB5747clustered, unique, primary key located
on PRIMARY- ID
VGNCCB_GROUP_USER_JT_INDEX1nonclustered located on PRIMARY -GROUP_ID
VGNCCB_GROUP_USER_JT_INDEX2nonclustered located on PRIMARY-
USER_NAME|||Jack A (InformixMail@.yahoo.com) writes:
> OK , Here goes with the table structure. BTW I've run DBCC reindex.

Thanks, but I explicitly asked for CREATE TABLE and CREATE INDEX statements.
That could permit me see if it is possible to build an indexed view.

Also, in VGNCCB_ROLE_JT, I can't even see that there is a ROLE_ID
column.

You can script tables and indexes in Enterprise Manager or Query Analyzer.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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