Why EXISTS? (I know, it's early for philosophy)

I find LS generating queries like this:

SELECT
  (columns)
FROM
  A
INNER JOIN
  B
ON
  A.BId = B.ID
WHERE
  ((A.ID = @p0 AND A.OtherColumn = @p1) AND EXISTS (
    SELECT
      B.*
    FROM
      B
    WHERE
      B.ID = A.BId AND
      B.DeletedOn IS NULL AND
      B.DeletedOn IS NULL
  ))
ORDER BY
  B.ID DESC

My puzzle is, why does it do an EXISTS and coordinate joining in a table that already is present in the FROM list? (and why does it check deletedon twice?)

Surely the above, and this, are equivalent:

SELECT
  (columns)
FROM
  A
INNER JOIN
  B
ON
  A.BId = B.ID
WHERE
  A.ID = @p0 AND A.OtherColumn = @p1 AND B.DeletedOn IS NULL
ORDER BY
  B.ID DESC

I've caught other examples where the EXIST nesting is huge. We have projects that contain boards that contain section that contain questions that contain messages, and I occasionally find queries that

SELECT Messages 
WHERE EXISTS 
    (a questions query WHERE EXISTS 
        (a sections query WHERE EXISTS 
            (a boards query WHERE EXISTS 
                (a projects query WHERE deletedon is null ))))

Is it the standard way that LS selects messages, ensuring that they aren't ultimately part of a deleted project?

We use an EXISTS to scope related entities for child queries (e.g. when eager loading). This is the syntax we have used dating back to LightSpeed 1 which pre-dates the inclusion of joins into the querying API.

When we added support for joins we did identify this as something which could be improved and its a long standing backlog item but due to the amount of work involved it continues to remain on the backlog.

Jeremy

Hi J

Thanks for that. I strongly suspect that the optimizer rewrites the query anyway, as there doesn't seem to be a significant performance issue; just a bit of a readability one!

Is there an easy way I can tell, given that I'm looking at a list of my queries that my SQL Server says are performance problems, what bit of code gave rise to a particular query? Can I get LS to tell me what query it generated, into a log file of some sort, so I can trace what bit of code relates to what query?

Thanks M

Yes, you can log query execution using a Logger (see: http://www.mindscapehq.com/documentation/lightspeed/Testing-and-Debugging/Logging for details) although this only caters for handling the actual logging of SQL being executed by LightSpeed rather than tying this back to a callsite so you would need to add in something yourself to handle the latter concern.

Generally if you know the general scope of what is being executed its generally pretty easy to infer what query was generated by what code, you will find however that lazy load queries etc can be tricky to infer since they are not a direct query but rather are generated by property access etc.

Jeremy