Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    santa clara
    Posts
    25

    Unanswered: Can I optimize this query

    Here is a query that takes some time to complete... Is there a way I can optimize it?

    SELECT DISTINCT models.modelid, models.mfrid, models.mfrname,
    models.catid, models.class, models.modelname, models.cpu, models.expansionsockets,
    models.modelcomments, models.memorycomments, models.processors, models.OS,
    models.standrardmem, models.maxmem, models.hdbustype, models.bustype,
    models.standardvideo, models.maxvideo, models.videocomments
    FROM models
    inner join manufacturers on models.mfrid = manufacturers.mfrid
    inner join oemmemory on models.modelid = oemmemory.modelid
    inner join crossref on oemmemory.crossrefid = crossref.crossrefid
    inner join pdppart on crossref.crossrefid = pdppart.crossrefid
    left outer join exclude on models.modelid = exclude.modelid where
    models.mfrid = ? AND models.catid = ? ORDER BY models.modelname

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. ensure all join columns have indexes
    2. remove the DISTINCT (unless you really need it, which i doubt), because it requires a humungous sort
    3. remove the ORDER BY if possible
    4. reconsider why you are doing all those joins if you aren't pulling any fields from any table except models
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Posts
    706
    Quote Originally Posted by r937
    4. reconsider why you are doing all those joins ...
    Don't be afraid to use more than one query to get your answer. The old tried-and-true method of using temporary tables still works great. When the query is complex and involves many tables, "sometimes the optimizer comes up with a good plan, and sometimes it doesn't."

    The risk that you (conceptually) run into is anything that smacks of a so-called "cartesian product" operation: one that joins everything with everything. The more tables you have in a query, the more possible combinations you have, "all at once."

    It looks like you are using those inner joins to locate all the models for which the other types of information are all known. If this is a frequent requirement, Logical-fields either in the models-table or elsewhere might be indicated, to show what kinds of information are available about this model. (Sure, database-purists would argue with me on that one, but performance is a valid issue and no database exists in a vacuum.)
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

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