Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2002
    Posts
    33

    Exclamation Unanswered: Need SQL Performance Advice (Scenario Given)

    Hey guys, having a bit of a urgency here. I need to make certain queries faster in our application, lemme give you a scoup. One of the queries i need to optimize or find a better way to do is the current scenario. We have jobs in our system, these jobs have applicants, some of them are direct applicants, some are matches (from searches), and some are applicants with resumes only.
    Some of our clients have 100+ jobs, when they see the first page we list all the jobs with 6 counts next to them:

    Job Title, Total Applicants, New Applicants, Total Matches, New Matches, Total Resumes, New Resumes

    So we have 6 counts for each job, say you have 100+ jobs thats a lot of counts. Currently i'm performing a sub query for each of the counts since they represent different data points, the only thing that changes is the WHERE clause.

    As you can see this can be very heavy and in some cases (100+ jobs) depending of number of applicants might take up to a minute to finish.

    I would like to get some opinions from you guys on how to make this run faster, obviously caching cannot be considered since they employer must see the live data (counts)

    Any help is greatly appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    indexes on the join columns?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2002
    Posts
    33

    Exclamation

    yes indexes are in place, i dont really thing that they are the problem,

    i think the subqueries are killing it since one query needs to be run to get each of the count right? is there any other way to structure this thing?

    thanks for your help

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    depends

    without seeing the subqueries, it's kinda difficult to tell what's wrong or whether an alternative structure is possible
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without seeing your code, my first suggestion would be to "unwrap" the subqueries. Make a single pass through the data, doing a single join. Reimplement the counts as sums, and use CASE to provide the "smarts" to make it work.

    As Rudy pointed out, without seeing what you are doing, we are pretty sorely limited in how we can help. This is kind of like calling someone on another continent and telling them "my stomach hurts" and asking them what you should do about it.

    -PatP

Posting Permissions

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