Lightspeed 3 & 4, Projections, huge selects in generated sql and Distinct()

 I am struggling with 2 issues which is really starting to impact performance on a big project I am working on.

1. When projecting generated SQL has all the columns from all tables in the joins as opposed to just the columns defined in the projected class.
This is really bad as it is an issue with one entity or joins to many entities and it is a major performance hit.  I have checked the generated sql that both Entity Framework 4 and Linq 2 Sql produces and they both limit the select to the projected classes fields.  What can be done to help this?

2. When using a Distinct() on a query like below:

 var query = (from q in uow.OnetWords
 join o in uow.Onets on q.OnetId equals o.Id
 where (q.Word.StartsWith(word) || q.Stem == stem)          
 select new WordFrequency
 {
  OnetId = q.OnetId,
  OnetRingId = q.OnetRingId,
  OnetFrequency = (q.Frequency.HasValue ? q.Frequency.Value : 0),
  OnetWord = q.Word,
  OnetStem = q.Stem
 });

onetWordList.AddRange(query.Distinct().ToList());

For some reason a distinct doesn't get added to the sql whereas it is for both EF and L2S.

Please help.

Hi Jim,

If you are seeing a query with all columns included then this means that LightSpeed believes it cannot perform a server side translation of the projection and therefore is loading in the all data to perform the projection client side. To find out what its not liking you can try tweaking your projection to remove aspects of it until it does project server side - use of things such as inclusion of language syntax that we cant translate or use of client side types or methods generally cause this. If you havnt already you should grab the latest nightly build as we periodically make improvements to translation in the LINQ provider which will help with server side projections so this may solve your problem.

In your query above I believe you would be having all columns selected due to the use of the conditional, so the distinct would be performed after the projection occurs which will be done client side. Removing that from the projection would move it server side and that would also push the distinct aspect down to the server as well.

Jeremy

Just curious, is there a major technical hurdle to implementing the ternary operator in the LINQ provider? Seems like it would translate to CASE statements pretty easily.  Is there something wonky about the expression tree?

No Im actually surprised it isnt translating as we do support it in the underlying query API and in where clauses, but it appears its just not checked for with projections so I am currently looking at adding this in today - stay tuned :)

Jeremy

Thanks for the reply.

I have taken on board to what you are saying and the only way I can get it to select just the comments specified is to project into an anonymous type.

When projecting the class I dropped each field out one at a time until it was blank and nothing helped.  It just doesn't like projecting into a class which makes me wonder what class would project and keep the fields selected in the sql to the projection set.

I also installed the latest build and still doesn't help with a class.

I will speak with a colleague about this tomorrow because we will have to go through all projections and change them to be 2 stage.  Firstly to project into an anonymous type and then into the actual return class.

Jim

Hi Jim,

Are you able to send through a small example of this? You should be able to project into a known type, there was a previous issue with which was fixed recently but you would have this in the latest nightly build (20111102).

I have added in some changes which will resolve the issue around projecting the ternary operator into a server side projection and this will be available in the next nightly build.

Thanks!

Jeremy

Hi Jeremy,

  I have a 2MB file that I can send you that has a projects for Lightspeed, Linq2Sql and EF plus a test db.  Can you provide me with an email address to send it please?

Many Thanks,

Jim

Hi Jim,

You can email it through to us at support@mindscape.co.nz

Jeremy