?
Solved

Reducing enormous logical reads on SQL 2000

Posted on 2003-03-18
15
Medium Priority
?
620 Views
Last Modified: 2010-05-18
Hey there,

I have a stored procedure that is producing high scan counts (516700 & 1336097) and logical reads(1043003 & 2674816) on a couple my 'UserGroupUserMember' and 'UserGroupSet' tables respectively. I'm new to being a DBA and can't figure out how to improve performance on the queries.

I've tried creating a clustered index as opposed to the non-clustered one in place and updating the statistics, but to no avail. Any ideas on how to improve performance on the following stored procedure?

Thanks in advance...

Rick

/////////////////////////////////////////////////////////

CREATE PROCEDURE [dbo].ReportParamProgress_UPAR(
    @Parameter       VARCHAR(10),
    @UserGroupKey    INTEGER,
    @CourseGroupKey  INTEGER,
    @UserKey         INTEGER = 0 )
AS
BEGIN

        IF ( @Parameter = 'U')
        BEGIN
                  SELECT DISTINCT
                         u.LastName + ',  ' + u.FirstName + ' (' + u.UserName + ')' "User",
                         u.UserKey "UserKey",
                         LOWER(u.LastName) "Last Name",
                         LOWER(u.FirstName) "First Name",
                         LOWER(u.UserName) "Username"
                  FROM
                 ( SELECT ugum2.MemberKey UserKey , ugcs.CourseKey CourseKey
                     FROM UserGroupSet ugs1,
                          UserGroupCourseSet ugcs,
                          UserGroupUserMember ugum1,
                          UserGroupUserMember ugum2
                    WHERE ugs1.UserGroupKey  = @UserGroupKey
                      AND ugs1.MemberKey     = ugum1.UserGroupKey
                      AND ugum1.MemberKey    = ugum2.MemberKey
                      AND ugum2.UserGroupKey = ugcs.UserGroupKey
                    UNION ALL
                   SELECT ugum.MemberKey UserKey , uc.MemberKey CourseKey
                    FROM UserGroupSet ugs1,
                         UserGroupUserMember ugum,
                         UserCourse uc
                   WHERE ugs1.UserGroupKey = @UserGroupKey
                     AND ugs1.MemberKey    = ugum.UserGroupKey
                     AND uc.UserKey        = ugum.MemberKey
                   UNION ALL
                   SELECT ugum1.MemberKey UserKey , cgcm.MemberKey CourseKey
                     FROM UserGroupSet ugs1,
                          UserGroupUserMember ugum1,
                          UserCourseGroupSet ucgs,
                          CourseGroupSet cgs,
                          CourseGroupCourseMember cgcm
                    WHERE ugs1.UserGroupKey   = @UserGroupKey
                      AND ugs1.MemberKey      = ugum1.UserGroupKey
                      AND ugum1.MemberKey     = ucgs.UserKey
                      AND ucgs.CourseGroupKey = cgs.CourseGroupKey
                      AND cgs.MemberKey       = cgcm.CourseGroupKey ) m0,
                      UserGroupSet ugs,
                      UserGroupUserMember ugum0,
                      UserProfile u,
                      ( SELECT DISTINCT cgcm.MemberKey CourseKey
                          FROM CourseGroupSet cgs,
                               CourseGroupCourseMember cgcm
                         WHERE cgs.CourseGroupKey = @CourseGroupKey
                           AND cgs.MemberKey      = cgcm.CourseGroupKey ) m1
                WHERE ugs.UserGroupKey   = @UserGroupKey
                  AND m0.UserKey         = ugum0.MemberKey
                  AND ugs.MemberKey      = ugum0.UserGroupKey
                  AND m0.CourseKey       = m1.CourseKey
                  AND ugum0.MemberKey    = u.UserKey
                ORDER BY LOWER(u.LastName),LOWER(u.FirstName),LOWER(u.UserName);
        END
        ELSE IF ( @Parameter = 'C')
        BEGIN
                  SELECT DISTINCT c.Title "Resource",
                                  c.CourseKey "CourseKey",
                                  UPPER(c.Title) "Title"
                  FROM
                    UserCourseSet ucs,
                    Course c,
                   ( SELECT DISTINCT cgcm.MemberKey CourseKey
                       FROM CourseGroupSet cgs,
                            CourseGroupCourseMember cgcm
                      WHERE cgs.CourseGroupKey = @CourseGroupKey  
                        AND cgs.MemberKey      = cgcm.CourseGroupKey ) m1
                   WHERE ucs.UserKey           = @UserKey
                     AND m1.CourseKey          = ucs.CourseKey  
                     AND m1.CourseKey          = c.CourseKey
                  ORDER BY UPPER(c.Title), c.CourseKey ;  
        END
       

END

GO
0
Comment
Question by:RickDeet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +4
15 Comments
 
LVL 2

Expert Comment

by:gdbjohnson
ID: 8163735
Well this is a complicated query, but I think I can see the root of your problem.

You want to reduce your result sets as much as possible within the body of the query and the DISTINCT part of your query is where most of your time is being eaten, I'd wager.

Since you want to get a DISTINCT set of names, and you are simply unioning this set, you can actually use the names like Keys!  This is a very useful fact.

Since the most important thing is speed, the easiest way to get what you want here is to use a temporary table.

Take your first query subsection:


-----------
SELECT ugum2.MemberKey UserKey , ugcs.CourseKey CourseKey
                    FROM UserGroupSet ugs1,
                         UserGroupCourseSet ugcs,
                         UserGroupUserMember ugum1,
                         UserGroupUserMember ugum2
                   WHERE ugs1.UserGroupKey  = @UserGroupKey
                     AND ugs1.MemberKey     = ugum1.UserGroupKey
                     AND ugum1.MemberKey    = ugum2.MemberKey
                     AND ugum2.UserGroupKey = ugcs.UserGroupKey
----------

and dump it to a temp table, call it #TEMP.  Then take the next subsection:

---------
SELECT ugum.MemberKey UserKey , uc.MemberKey CourseKey
                   FROM UserGroupSet ugs1,
                        UserGroupUserMember ugum,
                        UserCourse uc
                  WHERE ugs1.UserGroupKey = @UserGroupKey
                    AND ugs1.MemberKey    = ugum.UserGroupKey
                    AND uc.UserKey        = ugum.MemberKey
--------

but change it to the following:
-----
INSERT INTO #TEMP
SELECT ugum.MemberKey UserKey , uc.MemberKey CourseKey
FROM UserGroupSet ugs1
inner join
UserGroupUserMember ugum
on ugs1.MemberKey    = ugum.UserGroupKey
inner join
UserCourse uc
on uc.UserKey        = ugum.MemberKey
left join #TEMP B
on ugum.UserKey = B.UserKey and uc.MemberKey = B.UserKey
WHERE ugs1.UserGroupKey = @UserGroupKey
and B.UserKey is null
-------

Basically, you will be breaking up your single query into several queries, each one adding to this #TEMP table.  You will notice that the above query using the left join eliminates the need for the DISTINCT keyword, which is what slows your query down.

Also, I've used the inner join syntax above...  I'm not sure if that syntax results in faster queries, or if the SQL Parser treats them equivalently, but I usually write my queries using this syntax with excellent results.  It's also generally easier to read.

I hope this helps you out.  Basically, try to think on how to remove the DISTINCT from your query.  It's always slow.  Esp if your result set is large.



0
 
LVL 9

Expert Comment

by:xenon_je
ID: 8165534
Observations:
1)The query
SELECT ugum2.MemberKey UserKey , ugcs.CourseKey CourseKey
                    FROM UserGroupSet ugs1,
                         UserGroupCourseSet ugcs,
                         UserGroupUserMember ugum1,
                         UserGroupUserMember ugum2
                   WHERE ugs1.UserGroupKey  = @UserGroupKey
                     AND ugs1.MemberKey     = ugum1.UserGroupKey
                     AND ugum1.MemberKey    = ugum2.MemberKey
                     AND ugum2.UserGroupKey = ugcs.UserGroupKey
is in fact equivalent with
SELECT ugum.MemberKey UserKey , ugcs.CourseKey CourseKey
                    FROM UserGroupSet ugs1,
                         UserGroupCourseSet ugcs,
                         UserGroupUserMember ugum
                   WHERE ugs1.UserGroupKey  = @UserGroupKey
                     AND ugs1.MemberKey     = ugum.UserGroupKey
                     AND ugum.UserGroupKey = ugcs.UserGroupKey
(Why appears the UserGroupUserMember  twice, and achieving nothing by it???)
*****************

2) In general you use selects in the from clause.
These are in fact tables with no indexes on them (these is how they are treated).
For m0 it should be a better approach to create a temporary table, with an index on it on the
CourseKey. This would speed up a lot.

********************
3) The same goes for the m1 (here the gain is less)
********

4) If the sp (stored procedure) is called many times, then it would be better to create
2 stored procedure, one treating the parameter ='U' and one for 'c'
something like this:
create my proc(..)
AS
IF ( @Parameter = 'U')
       BEGIN
         exec sp1
       END
       ELSE IF ( @Parameter = 'C')
       BEGIN
        exec sp2
       end;
This way the query optimezer will be used as its best
**************************

5)The distinct is killing you everywhere. Thing very well where you need it.
For example:
in the 'C' part, the m1 contains many duplicates? If not so many than you can omit the distinct
because it appears upwards in the same sql. So something like this:
.....
       ELSE IF ( @Parameter = 'C')
       BEGIN
                 SELECT DISTINCT c.Title "Resource",
                                 c.CourseKey "CourseKey",
                                 UPPER(c.Title) "Title"
                 FROM
                   UserCourseSet ucs,
                   Course c,
                  ( SELECT cgcm.MemberKey CourseKey
                      FROM CourseGroupSet cgs,
                           CourseGroupCourseMember cgcm
                     WHERE cgs.CourseGroupKey = @CourseGroupKey  
                       AND cgs.MemberKey      = cgcm.CourseGroupKey ) m1
                  WHERE ucs.UserKey           = @UserKey
                    AND m1.CourseKey          = ucs.CourseKey  
                    AND m1.CourseKey          = c.CourseKey
                 ORDER BY UPPER(c.Title), c.CourseKey ;  
       END
Plus here comes the same optimization as at 2). Create a temporary table, with an index on it
on the CourseKey field (better a clustered one).
The gain of using a temporary table + index is better if the table has many records.

GL
        if you needan complete example tell me
xenon
0
 
LVL 9

Accepted Solution

by:
xenon_je earned 500 total points
ID: 8165555
Seriously...try to rethink this sp,
plus try using the sintax with inner join, left join and so on, so the joining condition to not be put in the where clause. It's much more easier to read and understand, ad sometimes eliminates the possibility to make errors. (this will not improve speed, but it will improve readability and maintenance of the code...)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 8167091
I presume you recompiled the procedure when you made the index changes...

if your keeping the procedure this complex you may benefit from using the with recompile option on the
procedure definition so that the optimum access path is
chosen each time dependant on the available stats  

I agree that probably using temporary tables is the way
to go with it however ... the simpler you sql is the better the optimiser can work...

hth  
good luck
0
 

Author Comment

by:RickDeet
ID: 8170188
First of all, thanks for all the quick comments. I really appreciate it! I couldn't wait to get to work and try the suggestions out.

Gdbjohnson et al, the temporary table suggestion is golden. I haven't worked with them before, but I'm learning trying to implement the suggestions here. I'm not there yet, but working on it.

Xenon, the duplication you mentioned is necessary for this reason: when a user signs up for course (a course contains multiple classes), there is a CourseGroupKey associates with that course. When they sign up for a class inside that course, there is a CourseKey associated with that class. Here's where the duplication comes in... I want to report on not only which Courses the user is signed up for, but also which classes the user is signed up for.

If I remove the duplication, I only get that they are signed up for at least one class in a course, not all the classes. I wouldn't be able to report on their progress for each class without the duplication.

Your suggestion for the 2 sps is interesting. I will see if I can implement that into the page...

By the way, I didn't write this behemoth, I'm just charged with optimizing it!

Rick
0
 

Author Comment

by:RickDeet
ID: 8170195
LowFat...

I'm hoping to get the thing working with temp tables. If I end up having to keep it closer to the way it is, I'll definitely add the RECOMPILE statement.

Thanks!

Rick
0
 

Assisted Solution

by:jcelko
jcelko earned 500 total points
ID: 8170283
Since you did not post any DDL, nobody can really help you; the best you can hope for is a kludge to improve performance.  Probably temp tables.  Why?  When you have a really bad schema, you fall back to non-relational stuff and procedural processing.  It will never run well, have data integrity or give you any of the advantages of a relational database, but you can keep writing 12 table joins until you find a new job.  

I can maker a few general remarks froj what was posted:

Your procedural design is wrong.  You have a single stored procedure that returns two totally different results!!  This is plain basic software engineering and has nothing to do with SQL.  Go back and re-read DeMarco, Yourdon, Constantine, et al.  Look up the principles of coupling and cohension.  

Next, the table names are weird -- they are all singular, so they must have only one row.  Pieces seem to be missing in the schema.  For example, you have "user_group_set" and "user_group_user_member" but no "User_Groups".   A data element cannot be named with a postfixed "_key" -- that is HOW it is used, not what it means.  Read the ISO-11179 Metadata Standards.  

The large number of tables and names like that imply that somneone is doing an OO schema design. I cannot remember having over five tables in a single query unless the schema was a total nightmare.  

You can very often replace UNIONs with OR-ed predicates and CASE expressions, but I cannot tell if that is possible here without DDL.  No specs or even an explanation of what this is supposed to do makes it jsut about impossible to do anything.  

Want to try again?
0
 
LVL 9

Expert Comment

by:xenon_je
ID: 8173323
So here it goes:
**********
create table UserCourseSet ( userKey int, CourseKey int)
create table Course (CourseKey int, Title varchar(20))
create table CourseGroupCourseMember (CourseGroupKey int, MemberKey int)
create table CourseGroupSet (CourseGroupKey int, MemberKey int)

insert into CourseGroupCourseMember Values(11, 100)
insert into CourseGroupCourseMember Values(12,101)
insert into CourseGroupCourseMember Values(11, 101)
insert into CourseGroupSet Values(33,11)
insert into CourseGroupSet Values(33,12)
SELECT cgcm.MemberKey CourseKey
                      FROM CourseGroupSet cgs,
                           CourseGroupCourseMember cgcm
                     WHERE
                       cgs.MemberKey      = cgcm.CourseGroupKey

insert into Course  Values(100, 'Title1')
insert into Course  Values(101, 'Title2')
insert into Course  Values(101, 'Title3')

insert into UserCourseSet Values(7, 100)
insert into UserCourseSet Values(8, 101)

SELECT DISTINCT c.Title "Resource",
                                 c.CourseKey "CourseKey",
                                 UPPER(c.Title) "Title"
                 FROM
                   UserCourseSet ucs,
                   Course c,
                  ( SELECT cgcm.MemberKey CourseKey
                      FROM CourseGroupSet cgs,
                           CourseGroupCourseMember cgcm
                     WHERE --cgs.CourseGroupKey = @CourseGroupKey   AND
                       cgs.MemberKey      = cgcm.CourseGroupKey ) m1
                  WHERE --ucs.UserKey           = @UserKey AND
                    m1.CourseKey          = ucs.CourseKey  
                    AND m1.CourseKey          = c.CourseKey
                 ORDER BY UPPER(c.Title), c.CourseKey

SELECT DISTINCT c.Title "Resource",
                                 c.CourseKey "CourseKey",
                                 UPPER(c.Title) "Title"
                 FROM
                   UserCourseSet ucs,
                   Course c,
                  ( SELECT DISTINCT cgcm.MemberKey CourseKey
                      FROM CourseGroupSet cgs,
                           CourseGroupCourseMember cgcm
                     WHERE --cgs.CourseGroupKey = @CourseGroupKey   AND
                       cgs.MemberKey      = cgcm.CourseGroupKey ) m1
                  WHERE --ucs.UserKey           = @UserKey AND
                    m1.CourseKey          = ucs.CourseKey  
                    AND m1.CourseKey          = c.CourseKey
                 ORDER BY UPPER(c.Title), c.CourseKey


drop table UserCourseSet
drop table Course
drop table CourseGroupCourseMember
drop table CourseGroupSet
*********************

As you can see above even if I use or not the distinct, the final result is the same. If you give me a sample code in which it will return different results than I'll eat my socks :)))))))))

I'll wait you example....
     xenon
0
 
LVL 2

Expert Comment

by:gdbjohnson
ID: 8173960
whoa Xenon...
do you get paid for this?
0
 
LVL 9

Expert Comment

by:xenon_je
ID: 8174604
gdbjohnson, I don't get paid for this.. but it's nice to try to understand what others want.... :)))
0
 
LVL 2

Expert Comment

by:gdbjohnson
ID: 8174806
haha...

No, I know what you mean.
I just joined here - it's a good forum for helping others and improving your own skills in the process - it's just that I noticed your name in several places which must be quite an investment of time on your part.

...Which is great for those who need help.  :)
0
 

Expert Comment

by:jcelko
ID: 8176798
That was not DDL.  All of the tables had NULL-able columns, so it was IMPOSSIBLE for them to have keys.  If you don't have keys, they are not tables; they are imitations of a file that you might use to scrub data, but they are not tables.  

You have improper postfix "-key"  which I assume are trying to tell the reader that those columns are keys **somewhere** in the schema.  Name a data element for what it is, not for how you use it.  So, I have to make some guesses.  

1) Can I assume things like UserCourseSets are the *actual way * that people refer to this relationship?  Nobody ever says something like "Enrollment"?  Hard to believe, but possible.  Here is a guess at some of the DDL that would be rfequired to make this a usable table:

CREATE TABLE UserCourseSets
(user_id INTEGER NOT NULL  -- where are the users?
      REFERENCES Users(user_id)
       ON UPDATE CASCADE
       ON DELETE CASCADE,
 course_nbr INTEGER NOT NULL
       REFERENCES Courses(course_nbr)
       ON UPDATE CASCADE
       ON DELETE CASCADE,
 PRIMARY KEY(user_id, course_nbr));  

I made the assumption about the key;  I assumeed without specs that this is a relationship between users and the courses they take.  A course can have many users and a user can take many courses.  

2) I had to guess at the key on this one; is title also unique?

CREATE TABLE Courses
(course_nbr INTEGER NOT NULL PRIMARY KEY,
 title VARCHAR(20) NOT NULL);

3) The following two tables are IDENTICAL in structure!!  That means they are sets of the same kind of things; that means one is redundant and your schema is not right.  Look up Chris Date and orthogonal design.  

CREATE TABLE CourseGroupCourseMembers
(course_group_id INTEGER NOT NULL,
 member_nbr INTEGER NOT NULL);

CREATE TABLE CourseGroupSets
(course_group_id INTEGER NOT NULL,
 member_nbr INTEGER NOT NULL);

If they are different in some logical way, then you need to add a column for the attribute that makes them different.  

Doesn't the redundancy in a name like "-GroupSets" bother you when you read it?   Why not just a plain old CourseGroup?  Things should probably look more like this:

CREATE TABLE CourseGroups
(course_group_id INTEGER NOT NULL,
 member_nbr INTEGER NOT NULL UNIQUE
       REFERENCES Members (member_id)
       ON UPDATE CASCADE
       ON DELETE CASCADE,
 member_type CHAR(1) DEFAULT 'x' NOT NULL
       CHECK(member_type IN ('x', 'y', 'z',..),
 PRIMARY KEY (course_group_id, member_nbr));


I am guessing that a member belongs to one and only one course group.  Also, where is the Members table?  If it is a foreign key, then we need a REFERENCES() to its base table.

It is weird to things called both users and members in things that deal with courses.  Why are they different?  

Want to try again?
0
 

Expert Comment

by:CleanupPing
ID: 9275747
RickDeet:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 11183481
No comment has been added to this question in more than 268 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: xenon_je http:#8165534, jcelko http:#8170283

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

monosodiumg
EE Cleanup Volunteer
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question