Generated SQL

Hi,
 
I'm currently trialling a few OR mappers to replace LINQ to SQL. (We've found it to be a bit of a pain with complex stuff) I've been looking at the SQL generated by LightSpeed and it seems a little strange.

I have the following table structure:

A { int Id, string Name }
B { int Id, int AId, string Name }

I.e. there is a many to one relationship between A and B, with B having a reference to 1 A. I've specified eager loading for the link to A when loading a B. This is producing SQL like:

SELECT
  B.Id,
  B.AId,
  B.Name
FROM
  B
WHERE
  B.Id = 123456;

SELECT
  A.Id,
  A.Name
FROM
  A
WHERE
  EXISTS (
    SELECT
      B.*
    FROM
      B
    WHERE
      B.AId = A.Id AND
      B.Id = 123456
  )
 
However I'd expect this to be done in a single query, e.g.
 
SELECT
  B.Id,
  B.AId,
  B.Name,
  A.Id,
  A.Name
FROM
  B
INNER JOIN A ON A.Id = B.Aid
WHERE
  B.Id = 123456;
 
Which I'd guess would be more efficient most of the time. (Given that the query plan of the single query with the inner join has the same plan as the second query of the LightSpeed on my setup) When eager loading with multiple tables the problem gets worse; in the code I'm currently testing LightSpeed is giving me 5 queries when it can be done in one with 5 joins.

Is there any reason LightSpeed does things like this? Is there a way to get LightSpeed to perform joins instead of multiple queries?

Cheers,

Kev

Hello Kev,

There are a couple of reasons why LightSpeed uses EXISTS subqueries rather than JOINs for eager loaded associations, but at this stage I believe they come down to time.  Previous versions of LightSpeed used EXISTS because they didn't support JOIN, and although now we do have JOIN we haven't had time to revisit eager loads using this mechanism.  We have a ticket on the backlog to look at this, but because the EXISTS mechanism works well enough for most scenarios we've prioritised other work ahead of this.  We'll keep reviewing it and your feedback helps us prioritise -- thanks!

 Do you have any idea when you might get around to looking at this, even a very, very rough idea? I ask because it's a deal breaker as to whether we use LightSpeed or not.

Hi Ken,

Its something we will be reviewing over the next few months as part of the next release however at this stage its not clear exactly what the implementation changes for this will involve so there are no guarentees we can make around making the changes at this time.

Sorry we cant be more specific on this.

Jeremy