Hello there, i'm running queries from NHibernate and have my profiler set up to trace any slow queries, there are a few which seem to take 10+ seconds in my profiler mainly:
exec sp_executesql N'SELECT top 50 this_.Debiteur_ID as Debiteur1_44_6_, this_.Debiteurnaam as Debiteur2_44_6_, this_.Debiteurnummer as Debiteur3_44_6_, this_.IsBedrijf_NeeJa as
IsBedrijf4_44_6_, this_.Bedrijfsnaam as Bedrijfs5_44_6_, this_.Achternaam as Achternaam44_6_, this_.Tussenvoegsel as Tussenvo7_44_6_, this_.Voorletters as Voorlett8_44_6_,
this_.Geboortedatum as Geboorte9_44_6_, this_.Titel_ID as Titel10_44_6_, this_.ManVrouw as ManVrouw44_6_, this_.Entiteit_ID as Entiteit12_44_6_, this_.Rechtsvorm_ID as Rechtsvorm13_44_6_,
this_.Account_ID as Account14_44_6_, this_.Vestiging_Postcode as Vestiging15_44_6_, this_.Vestiging_Adres as Vestiging16_44_6_, this_.Vestiging_Plaats as Vestiging17_44_6_,
this_.Vestiging_Huisnummer as Vestiging18_44_6_, this_.Vestiging_Land_ID as Vestiging19_44_6_, this_.Correspondentie_Postcode as Corresp20_44_6_, this_.Correspondentie_Adres as
Corresp21_44_6_, this_.Correspondentie_Plaats as Corresp22_44_6_, this_.Correspondentie_Huisnummer as Corresp23_44_6_, this_.Correspondentie_Land_ID as Corresp24_44_6_, this_.Telefoonnummer as Telefoo25_44_6_, this_.Email as Email44_6_, this_.Mobiel as Mobiel44_6_, title3_.Titel_ID as Titel1_65_0_, title3_.Omschrijving as Omschrij2_65_0_, gender4_.Geslacht_ID as Geslacht1_30_1_, gender4_.Omschrijving as Omschrij2_30_1_, typeofcomp5_.Rechtsvorm_ID as Rechtsvorm1_8_2_, typeofcomp5_.Omschrijving as Omschrij2_8_2_, country6_.Land_ID as Land1_7_3_, country6_.Omschrijving as Omschrij2_7_3_, country6_.ISO as ISO7_3_, country6_.ISO_3166_a3 as ISO4_7_3_, country7_.Land_ID as Land1_7_4_, country7_.Omschrijving as Omschrij2_7_4_, country7_.ISO as ISO7_4_, country7_.ISO_3166_a3 as ISO4_7_4_, debtorreac1_.Debiteur_ID as Debiteur1_3_5_, debtorreac1_.DateOfOldestReaction as DateOfOl2_3_5_, debtorreac1_.TotalAmountOfReactions as TotalAmo3_3_5_, debtorreac1_.AmountOfResolvedReactions as AmountOf4_3_5_, debtorreac1_.AmountOfUnresolvedReactions as AmountOf5_3_5_, debtorreac1_.TotalOpenAmount as TotalOpe6_3_5_, debtorreac1_.UnresolvedOpenAmount as Unresolv7_3_5_, debtorreac1_.ResolvedOpenAmount as Resolved8_3_5_ FROM tbl_Debiteur this_ left outer join tbl_Titel title3_ on this_.Titel_ID=title3_.Titel_ID left outer join tbl_Geslacht gender4_ on this_.ManVrouw=gender4_.Geslacht_ID left outer join tbl_Rechtsvorm typeofcomp5_ on this_.Rechtsvorm_ID=typeofcomp5_.Rechtsvorm_ID left outer join tbl_Land country6_ on this_.Vestiging_Land_ID=country6_.Land_ID left outer join tbl_Land country7_ on this_.Correspondentie_Land_ID=country7_.Land_ID left outer join vw_DebtorReactionDetails debtorreac1_ on this_.Debiteur_ID=debtorreac1_.Debiteur_ID WHERE this_.Entiteit_ID = @p0 and debtorreac1_.AmountOfUnresolvedReactions > @p1 ORDER BY this_.Debiteur_ID asc',N'@p0 int,@p1 int',@p0=1104,@p1=0
When i run the exact same query i just pasted here like this so including the parameters and everything the query takes less then a second.
I have no idea why this is happening but performance as it is right now simply isn't going to work out.
Greetings.