AnsweredAssumed Answered

Performance problems with SQL subselects

Question asked by Mike_Mitchell Expert on Mar 4, 2016
Latest reply on Mar 6, 2016 by disabled_morkus

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

Outcomes