1 2 3 Previous Next 32 Replies Latest reply on Mar 6, 2016 5:02 AM by disabled_morkus

    Performance problems with SQL subselects

    Mike_Mitchell

      Good day, all. I'm in the middle of trying to speed up a syncing process. Part of the requirement is that the process be as application-agnostic as possible, so I have some limitations on how I can code this (i.e., direct references to the Graph are really hard to work). I have a couple of queries that are really slowing the process down, and they're likely to get worse as the data load increases. They look like this:

       

           SELECT elementOptionJoin.elementOptionJoinID

           FROM elementOptionJoin JOIN lineItem

           ON elementOptionJoin.lineItemID = lineItem.lineItemID

           WHERE lineItem.lineItemID IN

                (

                SELECT lineItem.lineItemID

                FROM lineItem JOIN inspection

                ON lineItem.inspectionID = inspection.inspectionID

                WHERE inspection.accountID = <<currentAccountID>>

                )

       

           SELECT lineItemElement.lineItemElementID

           FROM lineItemElement JOIN lineItem

           ON lineItemElement.lineItemID = lineItem.lineItemID

           WHERE lineItem.lineItemID IN

                (

                SELECT lineItem.lineItemID

                FROM lineItem JOIN inspection

                ON lineItem.inspectionID = inspection.inspectionID

                WHERE inspection.accountID = <<currentAccountID>>

                )

       

      Subselects with a pair of JOIN clauses ... ugh. Unfortunately, the tables involved are parent - child - grandchild, so you need the information from the parent before you can do anything with the grandchild.

       

      I'm definitely not a SQL maven. Is there something constructive I can do with these to help with the performance?

       

      TIA

       

      Mike

        1 2 3 Previous Next