c# - Nhibernate/linq query is extremely slow -

i updating project written in c#/wpf , makes use of nhibernate , sql server. when tested program noticed slow when retrieving specific list database.

below data access code snippet:

public static list<ticket> getlistfromperiod(datetime begindatum, datetime einddatum)     {         list<ticket> list = new list<ticket>();         using (var session = nhibernatehelper.opensession())         {             using (var transaction = session.begintransaction())             {                 list = session.query<ticket>()                     .where(x => x.tijdstip.date <= einddatum && x.tijdstip.date >= begindatum).tolist();             }         }         foreach (var item in list)         {             item.issaved = true;         }         return list;     } 

this little piece of code troublemaker , takes 90 seconds retrieve 558 objects:

list = session.query<ticket>() .where(x => x.tijdstip.date <= einddatum && x.tijdstip.date >= begindatum).tolist(); 

some additional information:

• ticket has 1 many relationship ticketrecord
• klant (= customer in english) has 1 many relationship ticket
• papiersoort (= paper category in english) has 1 many relationship ticketrecord

mappings:

public ticketmap()     {         table("tblticket");         id(x => x.id)             .column("ticketid")             .unsavedvalue(0)             .not.nullable();         map(x => x.tijdstip)             .column("tickettijdstip")             .not.nullable();         references(x => x.klant)             .column("klantid")             .not.nullable();         hasmany(x => x.ticketrecordsaslist)             .table("tblticketrecord")             .inverse()             .keycolumn("ticketid").cascade.all();                 }   public ticketrecordmap()    {        table("tblticketrecord");        id(x => x.id)            .column("ticketrecordid")            .not.nullable();        map(x => x.prijs)            .column("ticketrecordprijs")            .not.nullable();        map(x => x.gewicht)            .column("ticketrecordgewicht");        references(x => x.soort)            .column("soortid")            .not.nullable();    }  public klantmap()     {         table("tblklant");         id(x => x.id)             .column("klantid")             .not.nullable();         map(x => x.naam)             .column("klantnaam");         map(x => x.rijksregisternr)             .column("klantrijksregisternr");         map(x => x.idnr)             .column("klantidnr");         map(x => x.adres)             .column("klantadres");         map(x => x.actief)             .column("actief")             .not.nullable();     }  public papiersoortmap()     {         table("tblsoort");         id(x => x.id)             .column("papiersoortid")             .not.nullable();         map(x => x.naam)             .column("papiersoortnaam")             .not.nullable();         map(x => x.prijs)             .column("papiersoortprijs");         map(x => x.isdefault)             .column("papierisdefault")             .not.nullable();     } 

nhibernate helper:

public static class nhibernatehelper {     private static isessionfactory _sessionfactory;      private static isessionfactory sessionfactory     {                 {             if (_sessionfactory == null)                 initializesessionfactory();              return _sessionfactory;         }     }      private static void initializesessionfactory()     {         _sessionfactory = fluently.configure()             .database(mssqlconfiguration.mssql2008             .connectionstring(x => x.fromconnectionstringwithkey("dbconnect"))                           //.connectionstring(                           //    @"data source=.\sqlexpress;attachdbfilename=c:\solutions\kassaopm\kassaopm.ui\db.mdf;integrated security=true;user instance=true")                           .showsql())              .mappings(m =>                       m.fluentmappings                           .addfromassemblyof<klantmap>()                           .conventions.add(fluentnhibernate.conventions.helpers.defaultlazy.never()))             .buildsessionfactory();     }      public static isession opensession()     {         return sessionfactory.opensession();     }  } 

in sql server profiler found 3 different query's being excecuted:

the first 1 (only occurs in beginning):

exec sp_executesql n' select ticket0_.ticketid ticketid3_, ticket0_.tickettijdstip ticketti2_3_, ticket0_.klantid klantid3_  tblticket ticket0_  dateadd(dd, 0, datediff(dd, 0, ticket0_.tickettijdstip))<=@p0 , dateadd(dd, 0, datediff(dd, 0, ticket0_.tickettijdstip))>=@p1',n'@p0 datetime,@p1 datetime',@p0='2015-04-22 00:00:00',@p1='2015-03-22 00:00:00' 

second , third one:

exec sp_executesql n' select klant0_.klantid klantid0_0_, klant0_.klantnaam klantnaam0_0_, klant0_.klantrijksregisternr klantrij3_0_0_, klant0_.klantidnr klantidnr0_0_, klant0_.klantadres klantadres0_0_, klant0_.actief actief0_0_  tblklant klant0_  klant0_.klantid=@p0',n'@p0 int',@p0=4235  exec sp_executesql n' select ticketreco0_.ticketid ticketid2_, ticketreco0_.ticketrecordid ticketre1_2_, ticketreco0_.ticketrecordid ticketre1_4_1_, ticketreco0_.ticketrecordprijs ticketre2_4_1_, ticketreco0_.ticketrecordgewicht ticketre3_4_1_, ticketreco0_.soortid soortid4_1_, papiersoor1_.papiersoortid papierso1_1_0_, papiersoor1_.papiersoortnaam papierso2_1_0_, papiersoor1_.papiersoortprijs papierso3_1_0_, papiersoor1_.papierisdefault papieris4_1_0_  tblticketrecord ticketreco0_ inner join tblsoort papiersoor1_ on ticketreco0_.soortid=papiersoor1_.papiersoortid  ticketreco0_.ticketid=@p0',n'@p0 int',@p0=27342 

i suspect there wrong code, can't seem figure out problem is.

if need other information please ask , best give need.

thanks in advance

all one-to many mappings related tickets meeting date condition seems fetched in business object, according second , third queries.

maybe should declare 1 many relations lazy avoid (and maybe unnecessary) loading.