9 levels of nested subqueries



  • When I first saw this at work on my first day, I laughed out loud, until the faces of my coworkers told me it was no joke. This is just one of DOZENS of similar views. Yes, it is written in a huge comment. That is the only place outside of the database server that the code is stored. At least it's in source control. All of these, along with every other bit of SQL in the entire project, are in a single giant 6000+ line file, with every possible query used anywhere spelled out explicitly.

    I have had endless discussions trying to argue why NOT using subqueries here is a better way to do it. The response is always "it's just a style issue, and I think this is much more readable". Sorry for the excessively long post, but the sheer size of this query is part of the wtf. (reformatted by me for readability).

    /* 2008.6.12 v_tractorSelectView definition (view not being used in any class or control)
    SELECT tractor."driverId", tractor."driverName", tractor.id AS "tractorId",
    tractor."dateAssigned", tractor."dateRemoved", tractor."year",
    tractor."tractorMakeTxt", tractor."tractorMake", tractor."tractorModel",
    tractor."tractorTypeTxt", tractor."tractorType", tractor."vinNumber",
    tractor.comments, tractor."tdComments", tractor."createdByTxt" AS "createdBy",
    tractor."dateCreated", tractor."lastUpdatedByTxt" AS "lastUpdatedBy",
    tractor."lastUpdated", tractor."tagNumber", tractor."tagExpiration",
    tractor."tagState", tractor."ownerId", tractor."ownerName", tractor.address,
    tractor.city, tractor."stateTxt", tractor.zip, tractor.phone, tractor.fax,
    tractor.email, tractor."ownedFrom", tractor."ownerComments",
    tractor."ownerRowStatus"
    FROM ( SELECT t8.id, t8."year", t8."tractorMake", t8."tractorModel",
    t8."tractorType", t8."vinNumber", t8.comments, t8."dateCreated",
    t8."createdBy", t8."lastUpdated", t8."lastUpdatedBy", t8."rowStatus",
    t8."tagNumber", t8."tagExpiration", t8."tagState", t8."tractorMakeTxt",
    t8."tractorTypeTxt", t8."createdByTxt", t8."lastUpdatedByTxt",
    t8."dateAssigned", t8."dateRemoved", t8."driverId", t8."tdComments",
    t8."driverName", t8."ownerId", t8."ownerName", t8.address, t8.city, t8.state,
    t8.zip, t8.phone, t8.fax, t8.email, t8."ownedFrom", t8."ownerRowStatus",
    t8."ownerComments", s.abbreviation AS "stateTxt"
    FROM ( SELECT t7.id, t7."year", t7."tractorMake", t7."tractorModel",
    t7."tractorType", t7."vinNumber", t7.comments, t7."dateCreated",
    t7."createdBy", t7."lastUpdated", t7."lastUpdatedBy", t7."rowStatus",
    t7."tagNumber", t7."tagExpiration", t7."tagState", t7."tractorMakeTxt",
    t7."tractorTypeTxt", t7."createdByTxt", t7."lastUpdatedByTxt",
    t7."dateAssigned", t7."dateRemoved", t7."driverId", t7."tdComments",
    t7."driverName", "owner".id AS "ownerId", ("owner"."firstName"::text || '
    '::text) || "owner"."lastName"::text AS "ownerName", "owner".address,
    "owner".city, "owner".state, "owner".zip, "owner".phone, "owner".fax,
    "owner".email, "owner"."tractorOwnedFrom" AS "ownedFrom", "owner"."rowStatus"
    AS "ownerRowStatus", "owner".comments AS "ownerComments"
    FROM ( SELECT t6.id, t6."year", t6."tractorMake", t6."tractorModel",
    t6."tractorType", t6."vinNumber", t6.comments, t6."dateCreated",
    t6."createdBy", t6."lastUpdated", t6."lastUpdatedBy", t6."rowStatus",
    t6."tagNumber", t6."tagExpiration", t6."tagState", t6."tractorMakeTxt",
    t6."tractorTypeTxt", t6."createdByTxt", t6."lastUpdatedByTxt",
    t6."dateAssigned", t6."dateRemoved", t6."driverId", t6."tdComments",
    (driver."firstName"::text || ' '::text) || driver."lastName"::text AS
    "driverName"
    FROM ( SELECT t5.id, t5."year", t5."tractorMake", t5."tractorModel",
    t5."tractorType", t5."vinNumber", t5.comments, t5."dateCreated",
    t5."createdBy", t5."lastUpdated", t5."lastUpdatedBy", t5."rowStatus",
    t5."tagNumber", t5."tagExpiration", t5."tagState", t5."tractorMakeTxt",
    t5."tractorTypeTxt", t5."createdByTxt", t5."lastUpdatedByTxt",
    td."dateAssigned", td."dateRemoved", td."driverId", td.comments AS "tdComments"
    FROM ( SELECT t4.id, t4."year", t4."tractorMake", t4."tractorModel",
    t4."tractorType", t4."vinNumber", t4.comments, t4."dateCreated",
    t4."createdBy", t4."lastUpdated", t4."lastUpdatedBy", t4."rowStatus",
    t4."tagNumber", t4."tagExpiration", t4."tagState", t4."tractorMakeTxt",
    t4."tractorTypeTxt", t4."createdByTxt", (su3."lastName"::text || ', '::text) ||
    su3."firstName"::text AS "lastUpdatedByTxt"
    FROM ( SELECT t3.id, t3."year", t3."tractorMake", t3."tractorModel",
    t3."tractorType", t3."vinNumber", t3.comments, t3."dateCreated",
    t3."createdBy", t3."lastUpdated", t3."lastUpdatedBy", t3."rowStatus",
    t3."tagNumber", t3."tagExpiration", t3."tagState", t3."tractorMakeTxt",
    t3."tractorTypeTxt", (su2."lastName"::text || ', '::text) ||
    su2."firstName"::text AS "createdByTxt"
    FROM ( SELECT t2.id, t2."year", t2."tractorMake", t2."tractorModel",
    t2."tractorType", t2."vinNumber", t2.comments, t2."dateCreated",
    t2."createdBy", t2."lastUpdated", t2."lastUpdatedBy", t2."rowStatus",
    t2."tagNumber", t2."tagExpiration", t2."tagState", t2."tractorMakeTxt",
    ttype.content AS "tractorTypeTxt"
    FROM ( SELECT t1.id, t1."year", t1."tractorMake", t1."tractorModel",
    t1."tractorType", t1."vinNumber", t1.comments, t1."dateCreated",
    t1."createdBy", t1."lastUpdated", t1."lastUpdatedBy", t1."rowStatus",
    t1."tagNumber", t1."tagExpiration", t1."tagState", tmake.content AS
    "tractorMakeTxt"
    FROM ( SELECT tractor.id, tractor."year", tractor."tractorMake",
    tractor."tractorModel", tractor."tractorType", tractor."vinNumber",
    tractor.comments, tractor."dateCreated", tractor."createdBy",
    tractor."lastUpdated", tractor."lastUpdatedBy", tractor."rowStatus",
    tractor."tagNumber", tractor."tagExpiration", tractor."tagState"
    FROM tractor) t1
    LEFT JOIN "lu_tractorMake" tmake ON t1."tractorMake" = tmake.id) t2
    LEFT JOIN "lu_tractorType" ttype ON t2."tractorType" = ttype.id) t3
    LEFT JOIN "systemUser" su2 ON t3."createdBy" = su2.id) t4
    LEFT JOIN "systemUser" su3 ON t4."lastUpdatedBy" = su3.id) t5
    LEFT JOIN "tractorDriver" td ON t5.id = td."tractorId") t6
    LEFT JOIN drivers driver ON t6."driverId" = driver.id) t7
    LEFT JOIN owners "owner" ON t7.id = "owner"."tractorId"
    WHERE "owner"."rowStatus" = 1) t8
    LEFT JOIN lu_states s ON t8.state = s.id) tractor;
    */
    


  • wow, looks like some stuff we see out of our reporting service interns.



  • My eyes!!!!

    Its one thing to have the WHERE-IN syndrome ... but this one takes the cake! The usual arguments for nested queries don't stand, in fact this is worse: it JOINs nested queries.

    I think we have a true WTF here.



  •  Agree,  a good front-page post.  Though I have to wonder -- is it auto-generated somehow by a query-designer tool?



  • @matthewr81 said:

    wow, looks like some stuff we see out of our reporting service interns.

    Oh no, this is straight from the lead developer, who is also vice president of software development.

    @Jeff S said:

    Agree, a good front-page post. Though I have to wonder -- is it auto-generated somehow by a query-designer tool?

    Hand coded. You should see it after it gets run through the PgAdmin "cleanup". Way worse.



  •  This is definitely a situation where if you really somehow feel you need to nest like this (even though of course in this case you don't), you should go ahead and use SELECT * in each of the outer selects.  (assuming all columns are always passed through each layer; hard to tell by scanning this mess)

    At least it would be somewhat  more readable ....

     



  • But if you optimize it, you won't be able to claim that the db server is grinding just to keep up; there goes your justification for a more powerful box!

     



  • Bonus points for using aliases t1 to t8 (how did those su* get in there?)



  •  Aren't these inline views rather than nested subqueries?  It doesn't seem like it would carry the same performance hit (i.e., repeating the subqueries for thousands of records), but it has been a long time since my SQL class so I'm rusty to say the least.  I am familiar with eliminating nested subqueries in WHERE clauses (in simpler queries than this), but what would be the approach here?

     



  • @NullAndVoid said:

     Aren't these inline views rather than nested subqueries?  It doesn't seem like it would carry the same performance hit (i.e., repeating the subqueries for thousands of records), but it has been a long time since my SQL class so I'm rusty to say the least.  I am familiar with eliminating nested subqueries in WHERE clauses (in simpler queries than this), but what would be the approach here?

     

     

    The term "sub-query", on its own, often refers either a derived table (or, as you said, an "inline view", which is what this is) or a correlated sub-query.  The later is typically the less efficient variation (unless the optimizer generates the execution plan as if it were written as a derived table).  

    In theory, this mess should be just as effient as a standard, well-written SELECT without all the nesting, but in reality, it is hard to know for sure without seeing how it gets compiled.  If its not pre-compiled (as, say, a stored procedure) then the parsing/compiling hit you take each time this mess is executed could be substantial.

     



  • Since this is actually being put in a view, the performance penalties are not quite as bad as they could be. Sadly, that is a primary reason why this "style" has persisted for so long.


Log in to reply