NHibernate and custom SQL subquery (using temp table)

Question

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.

I think using a stored proc that returns the either the ids or even full blown records might be the best bet here. — Rippo
– Rippo, Commented Apr 9, 2013 at 5:51

Mark Rotteveel · Accepted Answer · 2023-06-15 08:11:00Z

1

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)

edited Jun 15, 2023 at 8:11

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

answered Apr 16, 2013 at 2:03

paligap

9422 gold badges13 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joppe Over a year ago

I'm still curious how you got NHibernate to map your temporary tables. Since you use "on commit delete rows" I assume that you create them in the same transaction as you intend to use them in? So do you perform an on-the-fly mapping in NHibernate at the same time?

Rafi Over a year ago

@paligap can you explain how you hooked this up with nhibernate? I have the same issue?

Collectives™ on Stack Overflow

NHibernate and custom SQL subquery (using temp table)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related