Search code examples
nhibernatehqlqueryover

NHibernate and custom SQL subquery (using temp table)

Is it possible to use custom SQL for a sub query "IN" parameter. Currently, we successfully build the sub query (subQueryEstate) however it's a complicated chunk of SQL which can take time to process for larger datasets. The code generating a query using our existing sub query is as follows:

session.QueryOver(() => cAlias)
.WithSubquery.WhereProperty(x => x.CPE.ID).In(subQueryEstate)
.JoinAlias(x => x.Costs, () => aCosts, JoinType.LeftOuterJoin)
.JoinAlias(x => x.Open, () => aOpen, JoinType.InnerJoin)
.List();

To improve the speed of execution we have an idea to use a temp table (transactional life-cycle) that we will populate with ID's. The idea is to then either join to the temp table or use a much simpler sub query (SELECT ID FROM TEMP_TABLE) instead of the more complicated original.

Can we use a table that isn't mapped as a sub query in NHibernate? Can we write custom SQL or create a detached criteria to pass as parameter to the IN clause? We would like to preserve the fact NHibernate is producing the correct SQL for the remainder of the query.

Ideally something like:

session.QueryOver(() => cAlias)
.WithSubquery.WhereProperty(x => x.CPE.ID).In("SELECT ID FROM TEMP_TABLE")
.JoinAlias(x => x.Costs, () => aCosts, JoinType.LeftOuterJoin)
.JoinAlias(x => x.Open, () => aOpen, JoinType.InnerJoin)
.List();

Thoughts? Ideas? There might be a more elegant solution we haven't thought about.

Solution

  • In the end we used temporary tables to solve the problem. As our app uses a Firebird database we created four global temporary tables having a "transaction" life-cylce. That is, the data in a temp table is only valid for the life of the transaction.

    The SQL used to create the temp table (note we created four to satisfy our use case)

    create global temporary table TEMP_TABLE_ID1 (ID BIGINT NOT NULL) on commit delete rows;
CREATE INDEX IDX_TEMP_TABLE_ID1 ON TEMP_TABLE_ID1(ID);

    Using temp tables we can now systematically populate them with ID's from a master query. Filter subsequent queries by either using a sub-query or join to the temp table which is a much faster approach than using a large sub-query for each "downstream" query. Performance is dramatically improved using temp tables and because the tables are only valid for the life of the transaction it means we don't have to worry about cross-transaction pollution and/or purging the tables of data.

    A very tidy solution.

    Global Temporary Tables (Firebird)