List<int> takes long time to instantiate with Nhibernate Criteria

Question

I have this query which returns 7 rows and takes 4ms in the database but 1075ms to instantiate. That is just one example of the times and it varies on the objects but it doesn't seem to make sense that it takes so long. Any pointers on how to increase the speed?

var criteria =
                GetSession().CreateSQLQuery(
                    @"
                    select circt_id as CircuitId from normal_upstream
                        where dni_equip_type = 'A'

                        start with up_equip_stn_no in (:UpStationNo1)
                        connect by prior equip_stn_no = up_equip_stn_no
                        union
                        select circt_id as CircuitId
                        from normal_upstream 
                        where up_equip_stn_no in (:UpStationNo1)")
                    .AddScalar("CircuitId", NHibernateUtil.Int32).SetParameterList("UpStationNo1",upstreamStations)
                    .List<int>()

SQL Query generated

   select
     circt_id as CircuitId 
from normal_upstream

where dni_equip_type = 'A'

                            start with up_equip_stn_no in (
    'B' /* :p0 */)
                        connect by prior equip_stn_no = up_equip_stn_no
                        union
                        select
 circt_id as CircuitId
                        from normal_upstream       
where up_equip_stn_no in (
    'B' /* :p1 */)

Any help is appreciated. Thanks

Answer 1

Apparently what was slowing it down was the SetParameterList call. When I format the SQL before I get something along the lines of 8ms database and 485ms instantiation, which is acceptable. I'd like to have something faster but that will do for now.

Here is the code I'm currently using:

var sql =
            String.Format(
                @"select circt_id as CircuitId from normal_upstream
                where dni_equip_type = 'FDR_OCR'
                        start with up_equip_stn_no in ({0})
                        connect by prior equip_stn_no = up_equip_stn_no
                        union
                        select circt_id as CircuitId
                        from normal_upstream 
                        where up_equip_stn_no in ({0})",
                String.Join(",",upstreamStations.Select(x=>"'"+x+"'").ToArray()));
        var criteria =
            GetSession().CreateSQLQuery(sql)
                .AddScalar("CircuitId", NHibernateUtil.Int32)
                .List();
            return criteria;