Performance problems with query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Jack A (anonymous_at_discussions.microsoft.com)
Date: 05/20/04 

Date: Thu, 20 May 2004 09:57:09 -0700

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
.

Relevant Pages

  • Re: mining process flow
    ... In BI Dev Studio Mining Model ... Prediction pane there is a button, I think the icon represents a Floppy ... click the Save Query Result button. ... Cluster() AS FROM YourModel PREDICTION JOIN ...
    (microsoft.public.sqlserver.datamining)
  • Re: mining process flow
    ... so how can I save this select as table on my Database? ... click the Save Query Result button. ... Cluster() AS FROM YourModel PREDICTION JOIN ... using sql data mining structures. ...
    (microsoft.public.sqlserver.datamining)
  • Re: mining process flow
    ... The simplest way to to this is to build a a query in the BI Dev Studio tool ... Cluster() AS FROM YourModel PREDICTION JOIN ... using the DMX Filtering feature. ... using sql data mining structures. ...
    (microsoft.public.sqlserver.datamining)
  • Query Tuning problem.
    ... system on Win2k with Sql 2000 Enterprise on a cluster. ... The server has 2 Gig of RAM, ... seem fine based on the query plan. ...
    (microsoft.public.sqlserver.server)
  • Re: ODBC - Clustered Servers
    ... hmm - that doesn't look like a 'cluster' problem, just normal Jet stupidity. ... That is, it looks like the query optimiser has just made a bad choice, and I ... the clustered server returns different database statistics information?) ...
    (microsoft.public.access.modulesdaovba)