I recently started using LLBLgen Pro and it is fantastic so far

However, I have a question concerning the generated SQL when I do a GetMulti for an EntityCollection. When I run profiler for SQL Server, I see it always includes the DISTINCT keyword for GetMulti.

I'm a bit concerned this may have a negative influence on scalability and would prefer to exclude it. But there is no parameter for this in GetMulti nor did I find a way to do this with a predicate expression.

Is there an easy way to let DISTINCT go away? Maybe it is an idea to let LLBLgen automatically exclude it when keys / constraints guarantee uniqueness?

Arnoud wrote:

I recently started using LLBLgen Pro and it is fantastic so far

However, I have a question concerning the generated SQL when I do a GetMulti for an EntityCollection. When I run profiler for SQL Server, I see it always includes the DISTINCT keyword for GetMulti.

I'm a bit concerned this may have a negative influence on scalability and would prefer to exclude it. But there is no parameter for this in GetMulti nor did I find a way to do this with a predicate expression.

Is there an easy way to let DISTINCT go away? Maybe it is an idea to let LLBLgen automatically exclude it when keys / constraints guarantee uniqueness?

Good point. I see there is room for more tweaking in this. Initially, DISTINCT is always emitted, to eliminate duplicates already on the server, to avoid reading alot of rows and filtering out duplicates on the client. Duplicates can occur when relations are used. So if you want all customers with an order on a given date, and customers have a lot of orders each day, the amount of duplicates is quite high, while distinct would have filtered them out already.

However, indeed, if no relations are used, it shouldn't use DISTINCT, as duplicates are not occuring (unless a typed view is filled).

The SqlServer query optimizer will remove DISTINCT however before the query is executed. See the execution plan for SELECT DISTINCT * FROM Customers: it simply does a straight forward fetch which is exactly the same as when DISTINCT wasn't specified.

I'll add to the todo that more logic has to be added to eliminate DISTINCT in certain situations.

Otis wrote:

The SqlServer query optimizer will remove DISTINCT however before the query is executed. See the execution plan for SELECT DISTINCT * FROM Customers: it simply does a straight forward fetch which is exactly the same as when DISTINCT wasn't specified.

Makes sense, should have taken a look at that

Otis wrote:

I'll add to the todo that more logic has to be added to eliminate DISTINCT in certain situations.

Great, thanks!