NHibernate query optimization

Question

I have an nhibernate query which generates a query like this. I just extracted the example scenario

SELECT ID FROM EMPLOYEE WHERE EMPLOYEE.NAME = @P1 AND AND MANAGER_ID IN (@p2, @p3, @p4...@pn )etc.

Using NHprofiler I found the query takes around 500 ms. Table has index on name and manager_id. Employee table is around 6 million records.

When the query has only one record to search within manager_id e.g.

SELECT ID FROM EMPLOYEE WHERE EMPLOYEE.NAME = @P1 AND AND MANAGER_ID IN (@p2), the query completes in around 2 secs.

Hence, I tried to divide this query by using final query and running the query @n times (n - number of pn's). However, the performance for each of those query adds up to around 400 ms or more.

There's an equivalent Storedproc's (which has if then conditions for finding the manager ID tree) and performs much faster..

Is there something that I am missing in here?

Thanks.

Answer 1

Thoughts...

• IN evaluates to ..OR..OR..OR etc so you have a index scan. This is the killer.

• Are indexes "covering" to avoid key lookups?

• What difference does an index both ways make? I'd suggest having both so the optimizer can pick what's best (based on stats, cardinality etc)

like this:

CREATE INDEX IX_MN ON EMPLOYEE (MANAGER_ID, NAME) INCLUDE (ID)
CREATE INDEX IX_NM ON EMPLOYEE (NAME, MANAGER_ID) INCLUDE (ID)

• Can you rewrite as UNION/UNION ALL?

like this:

SELECT ID FROM EMPLOYEE WHERE EMPLOYEE.NAME = @P1 AND AND MANAGER_ID = @p2
UNION ALL
SELECT ID FROM EMPLOYEE WHERE EMPLOYEE.NAME = @P1 AND AND MANAGER_ID = @p3
UNION ALL
SELECT ID FROM EMPLOYEE WHERE EMPLOYEE.NAME = @P1 AND AND MANAGER_ID = @p4
UNION ALL
SELECT ID FROM EMPLOYEE WHERE EMPLOYEE.NAME = @P1 AND AND MANAGER_ID = @p5
UNION ALL
....

• Do @p1 and @p2..@pn match the column data types exactly?

• What does the SP look like?