none
Multiples statements into Compiled Query RRS feed

  • Question

  • Hi,

    i am using this code in my app

       Int32 nn = int.Parse(textBox.Text); // the function parameter
        VISITE v = me.VISITEs.FirstOrDefault(a => a.VINUM == nn);
                    DateTime d = (DateTime)v.DATEVIS;
                    Int32 p = (Int32)v.PANUM;
    
                 
    
                  
          var numberGroups =
                from n in me.VISRUBs.Where(a => a.VISANA.VISITE.DATEVIS <= d && a.VISANA.VISITE.PANUM == p)
                group n by n.RUBRIQUE into g
    
                select new
                {
    
                    RemainderCHAPLIB = g.Key.ANALYSE.CHAPITRE.LIBELLE,
                    RemainderLIB = g.Key.LIBELLE,
    
                    RemainderRUNUM = g.Key.RUNUM,
                    vals = (from vlist in g.OrderByDescending(a => a.VISANA.VISITE.DATEVIS)
                            select vlist.VALEUR)
                };
    
    
                    var lst = from n in numberGroups.
                    ToList().
                    Where(a => a.RemainderLIB != null)
    
                              select new ResultClass
                              {
                                  CHAP = n.RemainderCHAPLIB,
                                  RUB = n.RemainderLIB,
    
                                  VAL = n.vals.FirstOrDefault(),
                                  ANT = n.vals.Skip(1).FirstOrDefault()
    
                              };

    the queries run very very slowly in aspnet web service, about (60 sec !)

    and slow in WPF (about 5/6 sec)only  first time , after that it execute super fast (0.1s)

    after some searching   i have found the compiled queries , but all examples i have found take a single select query

    so my questions :

    - how to turn this statment code into a compiled query please  ?

    - and  any explanations on the huge performance difference between Asp.net and desktop ?

    - any other tricks i may do to get fast queries ?

    thanks.

    Monday, November 16, 2015 4:17 PM

Answers

  • Problem Solved !!!!

    after preparing the new solution from scratch and testing it, things seems to work correctly .

    i get the response in no time :)

    anyway thanks a lot  for your time and assistance .


    • Edited by issam1975 Wednesday, November 18, 2015 12:26 AM
    • Marked as answer by issam1975 Wednesday, November 18, 2015 12:34 AM
    Wednesday, November 18, 2015 12:25 AM

All replies

  • If you are using EF 6, then compiled Linq queries do not work with DBcontext. It only works from Objectcontext, which is though to get working when I tried it. So, I abandoned  complied Linq queries with EF 6. 

    and  any explanations on the huge performance difference between Asp.net and desktop ?

    Web applications are stateless and desktop applications are not stateless. Therefore, it can be that on the desktop that the query is always in memory, possibly.

    If you were able to use Objectcontext and the Static key word, then the complied Linq query would stay in memory on the Web server based on the Application Pool serviced by the WPW3.exe (the ASP.NET Worker Process). As long as the ASP.NET Worker Process is not recycled, then compiled Linq queries stayed in static memory.

    any other tricks i may do to get fast queries ?

    You can try Entity SQL or T-SQL queries from EF. You can even use the EF backdoor, use and sproc that is complied and cached on MS SQL Sever, use entities on the EF model as DTO(s) or make your own custom objects using them as DTO(s).  With E-SQL you can do the same thing as already explained in using a DTO concept.

    Monday, November 16, 2015 8:02 PM
  • the wierd thing is that i can execute this query super fast in linqpad , about 2/3second first time and 0.08 second next.

    but really really slow in a web application .

    any hint on this please

    Tuesday, November 17, 2015 12:16 AM
  • >>- any other tricks i may do to get fast queries ?

    Have a check this article to see if your query performance could be improved when using the Entity Framework:

    Performance Considerations for Entity Framework 4, 5, and 6

    Or instead of using entity framework, write your query in a store produce and call it with ADO.NET.

    Regards,

    --Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 17, 2015 3:21 AM
    Moderator
  • the wierd thing is that i can execute this query super fast in linqpad , about 2/3second first time and 0.08 second next.

    Linqpad is desktop solution, it is always in memory keeping stte, and its Linq, T-SQL generation engine and T-SQL execution are completely different than what is happening with EF and a Web service. You don't know what Linqpad is doing under the hood.

    You do know that the Web service has to be complied first and put into scope on first-time execution, right? If the Web service goes out of scope, because it has not been used for sometime, then the Web service has to be complied again and put back into scope, let along the Linq query has to be complied before it is executed.

    So yes, you are going to take a hit on time of execution with a Web service if the Web service is not kept in scope on the Web server.

    Tuesday, November 17, 2015 4:56 AM
  • also in WPF i have no problem :

    same EDMX (copy paste), same ef and db providers.

    first query is about 3/4 second then 0.1/0.2 sec .

    the problem is only in webservice or in webpage (i have tested with webform)

    also i have tried my web hosting in case there is a problem with iisexpress wich i use for developpement .

    so any suggestion on what can i do at this satge please ?

    in the meantime i will try fred suggestion .

    Tuesday, November 17, 2015 1:56 PM
  • the problem is only in webservice or in webpage (i have tested with webform)

    I have explained to you what is happening with any Web based solution, which is the Web page or Web service must be complied and put into scope based on the conditions that I have explained previously.

    https://msdn.microsoft.com/en-us/library/ms366723.aspx?f=255&MSPPError=-2147217396

    also i have tried my web hosting in case there is a problem with iisexpress wich i use for developpement .

    IIS Express and local IIS (the real one) are not the same solution, just like VS's Web development server is not local IIS either.

     

    Tuesday, November 17, 2015 2:35 PM
  • the problem is only in webservice or in webpage (i have tested with webform)

    I have explained to you what is happening with any Web based solution, which is the Web page or Web service must be complied and put into scope based on the conditions that I have explained previously.

    https://msdn.microsoft.com/en-us/library/ms366723.aspx?f=255&MSPPError=-2147217396

    also i have tried my web hosting in case there is a problem with iisexpress wich i use for developpement .

    IIS Express and local IIS (the real one) are not the same solution, just like VS's Web development server is not local IIS either.

     

    ok, but comparing 3 seconds in linqpad/WPF and 60/120seconds in web appliation , it's a huge difference !!!

    something bad is hapenning for sure, this is why i am asking

    Tuesday, November 17, 2015 7:14 PM
  • something bad is hapenning for sure, this is why i am asking

    Not really if the Web solution is a high usage solution, because the solution is not idle enough for it to be rolled out of scope, and it is staying in memory.

    Tuesday, November 17, 2015 10:15 PM
  • there is only me developping on my Laptop Machine :) So no so much usage .

    i have tried in my dev machine :

    - the Web Service 60/120 sec

    - WebForm with a test button to get data 60/120 sec

    and the same thing in my web hosting provider (only me testing)

    the same data and same query about  2sec in desktop first time, and no time at all after ...

    i have tried also Fred Suggestion , and int take forever , i stopped it afer 5 minutes, and there is no so much infos

    in the report


    Tuesday, November 17, 2015 10:48 PM
  • can i prepare a working testable solution for you to take a look on it it please ?

    i really dont understand what's going on

    Tuesday, November 17, 2015 10:52 PM
  • can i prepare a working testable solution for you to take a look on it it please ?

    i really dont understand what's going on

    Below is where you need to post, which has an EF section and Web service sections too. :)

    http://forums.asp.net/

    Tuesday, November 17, 2015 11:19 PM
  • Problem Solved !!!!

    after preparing the new solution from scratch and testing it, things seems to work correctly .

    i get the response in no time :)

    anyway thanks a lot  for your time and assistance .


    • Edited by issam1975 Wednesday, November 18, 2015 12:26 AM
    • Marked as answer by issam1975 Wednesday, November 18, 2015 12:34 AM
    Wednesday, November 18, 2015 12:25 AM