SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard Rick's Avatar
    Join Date
    Oct 2002
    Location
    Lancashire, UK
    Posts
    3,847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Speeding up two Select (Count) Queries

    As part of a college's registration system, student attendance marks are stored in a database table - one row per mark which contains the mark and an activity reference (as well as other infomation such as date the mark was added, date of the lesson etc)

    The activity ref (activity is effectivley a class) is designed to join with an activity table, which in turn is joined with a module table (module is a course) which in turn is linked to a student table. Finding a set of marks for the student is just a case of joining each table.

    Currently to find a students attendance as a percentage I have to perform a select query which counts all marks present for a given student in a given module. Then perform a second count query which uses IN in the where clause to select only present marks. The two results are then divided to work out a percentage.

    This isn't a very fast way of doing things however. For example say I want to find a percentage attendance for a course, or even an entire faculty the data the count is working on suddenly becomes very large (theres currently about 500,000 rows, but this will have doubled before too long)

    The pages producing the percentages are having to perform these queries multiple times, for example to view the percentage attendance for each course in a given subject area or faculty, as a result they run very very slowly.

    Is there a way I can combine these two queries into a single query?

    For example if I was writing this in a program I would perform the two operations in one - incrementing a counter for each row that matches the WHERE clause, at the same time (i.e. with the same operation) incrementing a second counter only if the mark is a present mark. (I hope that makes sense)

    e.g.
    PHP Code:
    foreach($rows as $row) {
      
    $all_marks++;

      if (
    $row['mark'] == 'Present') {
        
    $present_marks++;
      }

    }

    $percent_present = ($present_marks $all_marks) * 100
    Is there a way to have the database return the two results in a single operation, preferably using the same set of data - so that I only have to run one query?

    I'm using stored procedures and have tried optimising the query, but the fact that I'm having to run it twice means that I'm suffering a severe performance hit.

    Any suggestions / advice are greatly appreciated.

    Rick
    Rick

  2. #2
    SitePoint Wizard Rick's Avatar
    Join Date
    Oct 2002
    Location
    Lancashire, UK
    Posts
    3,847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry forgot to mention that this is running on MS SQL Server 2000, the code running the app is in ASP (VBscript) [although that's subject to change to php in the future]
    Rick

  3. #3
    SitePoint Evangelist DMacedo's Avatar
    Join Date
    May 2004
    Location
    Braga, Portugal
    Posts
    596
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool

    Rick,

    I'm not completely sure if I understood correctly.
    How are you doing things at the moment? Can you provide the queries and database structure w/sample data?

    It'd be easier to understand. But from what I gather I think you can use COUNT(`id`) using WHERE clause and join the results with UNION().
    ~ Daniel Macedo

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Rick
    Is there a way I can combine these two queries into a single query?
    yes
    Code:
    select count(*)                 as present_count
         , ( select count(*) 
               from yourtable
              where student = 937 ) as total_count
      from yourtable
     where student = 937
       and present = 'yes'
    then divide one by the other

    alternatively,
    Code:
    select count(*)      as total_count
         , sum(case when present = 'yes'
                    then 1 else 0 end) as present_count
      from yourtable
     where student = 937
    then divide one by the other
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard Rick's Avatar
    Join Date
    Oct 2002
    Location
    Lancashire, UK
    Posts
    3,847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    yes
    Code:
    select count(*)                 as present_count
           , ( select count(*) 
                 from yourtable
                where student = 937 ) as total_count
        from yourtable
       where student = 937
         and present = 'yes'
    then divide one by the other

    alternatively,
    Code:
    select count(*)      as total_count
           , sum(case when present = 'yes'
                      then 1 else 0 end) as present_count
        from yourtable
       where student = 937
    then divide one by the other
    That looks good - cheers

    Which do you reckon would be faster? Like I said I'm often running this 30 times in a single page, so even a slight performance hit makes a difference.

    Also would the following work? (I don't have acess to SQL Server to try it until tommorrow!)
    Code:
    select count(*)      as total_count
           , sum(case IN('P','/') then 1 else 0 end) as present_count
        from yourtable
       where student = 937
    There are about five different values that represent a present mark, so I'm currently using in as it lets me list them all in a single statement.

    I don't know if i'm barking up the wrong tree here, but when SQL Server runs this does it look through the table twice (one for each count) or will it use the same 'look through' for both counts?

    Thanks,
    Rick
    Rick

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Rick
    I'm often running this 30 times in a single page
    why would you want to do that? why wouldn't you do it for all the students in one query?

    Quote Originally Posted by Rick
    Also would the following work?
    no, you need a WHEN after CASE, but i think that was simply a copy/paste error on your part


    Quote Originally Posted by Rick
    does it look through the table twice (one for each count) or will it use the same 'look through' for both counts?
    the same pass of the data in the query using CASE
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard Rick's Avatar
    Join Date
    Oct 2002
    Location
    Lancashire, UK
    Posts
    3,847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    why would you want to do that? why wouldn't you do it for all the students in one query?
    In some pages I'm producing a virtual register for a class. I.e.
    Code:
    Register for Class A:
     John Smith   98% Present
     Jane Smith   72% Present
     Bob Smith	86% present
     etc etc
    Some classes will have upto 30 students in them, so as a result I am performing one main query for the overall percentage and then 30 queries for student specific percentages.

    Yeah, sorry - deleted a little too much!
    Code:
    ... sum(case when in('/','P')
    			   then 1 else 0 end) ...
    I'll give the above a shot tommorrow morning and see what happens, I'm hoping that if Sum will count the data in the same pass I'll see a pretty significant improvement in speed.

    Thanks
    Rick

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Rick
    Some classes will have upto 30 students in them, so as a result I am performing one main query for the overall percentage and then 30 queries for student specific percentages.
    ouch!!

    okay, don't do that, do this single query instead:
    Code:
    select student
         , 100.0 * count(*)
           / ( select count(*) 
                 from yourtable ) as present_pct
      from yourtable
     where student in ( 101, 356, 509, 937 )
       and present = 'yes' 
    group
        by student
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard Rick's Avatar
    Join Date
    Oct 2002
    Location
    Lancashire, UK
    Posts
    3,847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll give that a try - I could replace the in( 101,356...) part with code to select each student based on a student to class lookup table, I could probably replace the overall percentage query with something in the ASP code that averages all the student percentages. I'll no doubt be posting here again when I get stuck anyway!

    Thanks for your help Rudy
    Rick


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •