473,320 Members | 1,868 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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_ROLE 939
VGNCCB_ROLE_JT 62389
VGNCCB_GROUP_USER_JT 1364
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
...
Jul 20 '05 #1
3 1771
[posted and mailed, please reply in news]

Jack A (In**********@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, so****@algonet.se

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

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

TABLE: VGNCCB_ROLE_JT
index_name index_description index_keys
PK__VGNCCB_ROLE_JT__218BE82B clustered, unique, primary key located on
PRIMARY - ID
VGNCCB_ROLE_JT_INDEX1 nonclustered located on PRIMARY - USER_NAME
VGNCCB_ROLE_JT_INDEX2 nonclustered located on PRIMARY - GROUP_ID

TABLE: VGNCCB_GROUP_USER_JT
index_name index_description index_keys
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
Jul 20 '05 #3
Jack A (In**********@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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
3
by: Brian Oster | last post by:
After applying security patch MS03-031 (Sql server ver 8.00.818) a query that used to execute in under 2 seconds, now takes over 8 Minutes to complete. Any ideas on what the heck might be going...
12
by: serge | last post by:
I have an SP that is big, huge, 700-800 lines. I am not an expert but I need to figure out every possible way that I can improve the performance speed of this SP. In the next couple of weeks I...
5
by: John Bailo | last post by:
I wrote a webservice to output a report file. The fields of the report are formatted based on information in an in-memory XmlDocument. As each row of a SqlDataReader are looped through, a...
8
by: lyn.duong | last post by:
Hi, I have a large table (about 50G) which stores data for over 7 years. I decided to split this table up into a yearly basis and in order to allow minimum changes to the applications which...
5
by: Scott | last post by:
I have a customer that had developed an Access97 application to track their business information. The application grew significantly and they used the Upsizing Wizard to move the tables to SQL...
4
by: Bob Alston | last post by:
Some more, rather specific Access performance questions. IN a split front-end & back-end Access/Jet ONLY LAN situation, and with all query criteria fields and join fields indexed: 1. Is is...
20
by: Development - multi.art.studio | last post by:
Hello everyone, i just upgraded my old postgres-database from version 7.1 to 7.4.2. i dumped out my 7.1 database (with pg_dump from 7.1) as an sql-file with copy-commands and to one file using...
4
by: Jim Devenish | last post by:
I have converted an Access back-end to SQL Server back-end but am having some problems. The Access to Access application has been running well for some years. I have successfully copied all the...
9
by: HC | last post by:
Hello, all, I started out thinking my problems were elsewhere but as I have worked through this I have isolated my problem, currently, as a difference between MSDE and SQL Express 2005 (I'll just...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.