Hello all! Help me please create SelfServicing (OrganizationEntity) query:

SELECT 
  org.*
FROM
  (
    SELECT
      *
    FROM organization
    WHERE 
        organization.active = true
    LIMIT 1000
  ) AS org
INNER JOIN ...
...
INNER JOIN ...

This query is correct for me. It execution time: 500 ms. But if i use standard filters + relations execution time: 2 min. This query with paging (1000 org per page). And 10 JOINs. Executing time without paging ~ 2 min. It slow for production, where database timeout = 30 sec. Returning type: OrganizationEntity This query is correct and work fine. I need implement it in LBLGen.

What have you tried yourself that failed? ALso which llblgen pro version are you using?

LBLGen Pro version - 4.2. My tries:

var dtFields = new ResultsetFields(14);
dtFields.DefineField(...);
...
var filter = new PredicateExpression()
{
    new FieldCompareValuePredicate(OrganizationFields.Active, ComparisonOperator.Equal, true)
}
var dtDefenition = new DerivedTableDefinition(dtFields, "o", filter);

var r = new RelationCollection();
r.Add(new DynamicRelation(dtDefenition));
// trying add other relation - not happen
r.SelectListAlias = "o";

var organizations = new OrganizationCollection();
organizations.GetMulti(null, r);

But this doesn't work Generate:

SELECT 
  *
FROM 
   (
      SELECT 
        *
      FROM organization 
      WHERE organization.active = true
    ) 
LIMIT 1000;

But i want use limit in FROM. Because a lot of JOINs.

UPD!!! Sorry for my first post. Query in first post - are correct. But how it implement in LLBLGen?

Try the following overload of the DerivedTableDefinition

public DerivedTableDefinition( IEnumerable fields, string alias, IPredicateExpression filter, IRelationCollection relations, ISortExpression sorter, IGroupByCollection groupBy, long maxNumberOfItemsToReturn, bool allowDuplicates )

Walaa wrote:

Try the following overload of the DerivedTableDefinition

public DerivedTableDefinition( IEnumerable fields, string alias, IPredicateExpression filter, IRelationCollection relations, ISortExpression sorter, IGroupByCollection groupBy, long maxNumberOfItemsToReturn, bool allowDuplicates )

It's cool! Thanks. But what about OFFSET in derived table? For paging.

SQL doesn't support paging inside a derived table / nested query, it only supports TOP / LIMIT if order by is present. So we can't magically add paging statements to the nested query as it won't work on the database (the offset part).

If a query with lots of joins is slow, it's likely you don't have indexes on the foreign key fields (or you have millions of rows to join, but then it's not going to help either). Please check whether you have indeed indexes on the foreign key fields in the tables used in the join. You can see whether indexes are used by checking the execution plan of the query in the database management tool you're using. I presume this is sql server 2012/14 ?

No, PostgreSQL. Thanks all, i find my mistake. I'm added so much JOINs, but some not needed.