SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot Gman's Avatar
    Join Date
    Jan 2002
    Location
    Sarasota, FL
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is there a better optimized/efficient way of doing this

    There has to be a better way then doing this, obviously SQL is not one of my strong points, which I'm trying to correct now lol
    Should I be using sub-queries?


    Code:
    SELECT
            sr.id
            , sr.description
            , sr.report
            , sr.note AS internalNote
            , st.name AS serviceType
            , wo.id AS woid
            , c.name AS companyName
            , c.id AS cid
            , DATE_FORMAT(sr.dateCreated,'%m/%d/%Y @ %h:%i %p') AS dateCreated
            , ROUND((TIME_TO_SEC(sr.endtime) -
                             TIME_TO_SEC(sr.starttime)) / 3600.0 , 2 ) AS totalHours
            , ROUND((TIME_TO_SEC(sr.endtime) -
                             TIME_TO_SEC(sr.starttime)) / 3600.0 , 2 ) - sr.deduction AS totalTime
            , DATE_FORMAT(sr.startTime,'%c/%e/%Y - %h:%i %p') AS startTime    
            , DATE_FORMAT(sr.endTime,'%c/%e/%Y - %h:%i %p') AS endTime
            , sr.deduction
            , s.name AS status
            , e.mfrName
            , e.model
            , e.serial
            , l.name AS locationName
            , srn.note
            , DATE_FORMAT(srn.postedOn,'%m/%d/%Y @ %h:%i %p') AS noteDate
            , t.firstName
            , t.lastName
            , rt.name as rateName
    FROM
            service_report AS sr
    LEFT JOIN
            work_orders as wo ON
            wo.id = sr.woid
    LEFT JOIN
            company AS c ON
            sr.cid = c.id
    LEFT JOIN
            enum_status as s ON
             sr.status = s.id
    LEFT JOIN
            equipment as e ON
             sr.eid = e.id
    LEFT JOIN
            locations as l ON
            e.location = l.id
    LEFT JOIN
            enum_service_type as st ON
            sr.serviceType = st.id
    LEFT JOIN
            service_report_notes as srn ON
            sr.id = srn.srid
    LEFT JOIN
            technicians as t ON
            srn.postedBy = t.id
    LEFT JOIN
            enum_rate_type as rt ON
            sr.rateType = rt.id                                                     
    WHERE
            sr.id = ?

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you have indexes on the columns you are joining on?

  3. #3
    SitePoint Zealot Gman's Avatar
    Join Date
    Jan 2002
    Location
    Sarasota, FL
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Only Primaries on the id's, some have Unique on certain fields

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Gman View Post
    Only Primaries on the id's, some have Unique on certain fields
    here's the FROM clause from your query --
    Code:
    FROM
            service_report AS sr
    LEFT JOIN
            work_orders as wo ON
            wo.id = sr.woid
    LEFT JOIN
            company AS c ON
            c.id = sr.cid
    LEFT JOIN
            enum_status as s ON
            s.id = sr.status
    LEFT JOIN
            equipment as e ON
            e.id = sr.eid 
    LEFT JOIN
            locations as l ON
            l.id = e.location
    LEFT JOIN
            enum_service_type as st ON
            st.id = sr.serviceType
    LEFT JOIN
            service_report_notes as srn ON
            srn.srid = sr.id
    LEFT JOIN
            technicians as t ON
            t.id = srn.postedBy
    LEFT JOIN
            enum_rate_type as rt ON
            rt.id = sr.rateType
    i'm guessing the columns in blue are primary keys, therefore they do not need an (additional) index

    the columns in red would be ones that you should consider indexing

    aside: notice anything about the pattern of reds and blues? (yes, i did have to switch some of them around for you)

    each service report has exactly one matching row from all the other tables, but possibly multiple rows from the service report notes table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot Gman's Avatar
    Join Date
    Jan 2002
    Location
    Sarasota, FL
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, so I will add indexes for the red's, thank you

    so yeah, each service report can have multiple notes assigned to it, am I doing something wrong?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Gman View Post
    am I doing something wrong?
    not at all

    it was just an observation, an example of understanding the one-to-many relationships in a somewhat complex query by analyzing the column names for obvious primary keys
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •