WCF/WF - Speed up linq query ?

Asked By miho stumi
09-Nov-11 04:26 AM
Hi may U try help me to optimize this query, because it work very slowly:

public List<SzukajPremium> SzukajPremium(string kod, string rok, string numer)
        {
            DataContext db = new DataContext();                                
            var result = from z in db.Zeszyts
                        join p112 in db.ZeszytP112s on z.IdZeszyt equals p112.IdZeszyt
                        join dok in db.Doks on z.IdZeszyt equals dok.IdZeszyt
                        select new SzukajPremium { P182 = dok.P180, Tytul = dok.Tytul, IDPUB = string.Format("{0} [dodatek: {1}] - {2}.{3}", z.P101, z.P133,dok.P180.Substring(4,12),dok.P180.Substring(16,7).Replace("0","")), KodDodatku=z.Nazwa.Substring(13,3), Rok=z.P110, Numer=p112.P112 };
             if (!string.IsNullOrEmpty(kod))
                result = result.Where(k => k.KodDodatku == kod);
            if (!string.IsNullOrEmpty(rok))
                result = result.Where(r => r.Rok == rok);
            if (!string.IsNullOrEmpty(numer))
                result = result.Where(n => n.Numer == numer);            
            return result.ToList();
        }
  Web Star replied to miho stumi
09-Nov-11 04:29 AM

There is no doubt that tehre are some drawbacks to Linq to Sql. One of them is that the Sql statement is built dynamically so it is needed to be parsed and compiled each time you run it. Fortunately .Net 3.5 has a solution for this problem. System.Data.Linq namespace includes a class named CompiledQuery which is responsible for caching the compiled version of a Linq to Sql query. This class has a static method called Compile which takes a Func<T,S,R> delegate. In this signature, T is the type of a DataContext (i.e. HRMDataContext) , S is the type of a predicate to filter the query and R is the type of returned result. Needless to say that it must be IQueryable<T>.

In this article we will see how to pre-compile a query, its limitations and how it really improves the speed of a Linq query.
More details here
http://aspguy.wordpress.com/2008/08/15/speed-up-linq-to-sql-with-compiled-linq-queries/
http://weblogs.asp.net/wesleybakker/archive/2009/07/30/how-to-make-a-linq-to-sql-query-twice-as-fast.aspx

  miho stumi replied to miho stumi
09-Nov-11 04:47 AM
So that should look like this?

public static Func<DataContext , SearchCriteria, IQueryble<SzukajPremium>> FilteredResult =
System.Data.Linq.CompiledQuery.Compile(
(DataContext dc , SearchCriteria criteria ) =>
var result = from z in db.Zeszyts
                        join p112 in db.ZeszytP112s on z.IdZeszyt equals p112.IdZeszyt
                        join dok in db.Doks on z.IdZeszyt equals dok.IdZeszyt
                        select new SzukajPremium { P182 = dok.P180, Tytul = dok.Tytul, IDPUB = string.Format("{0} [dodatek: {1}] - {2}.{3}", z.P101, z.P133,dok.P180.Substring(4,12),dok.P180.Substring(16,7).Replace("0","")), KodDodatku=z.Nazwa.Substring(13,3), Rok=z.P110, Numer=p112.P112 };
             if (!string.IsNullOrEmpty(kod))
                result = result.Where(k => k.KodDodatku == kod);
            if (!string.IsNullOrEmpty(rok))
                result = result.Where(r => r.Rok == rok);
            if (!string.IsNullOrEmpty(numer))
                result = result.Where(n => n.Numer == numer);            
            return result.ToList();

);
Create New Account
help
feature request] Array.IsNullOrEmpty .NET Framework starting from .NET 2.0 there is a nice IsNullOrEmpty static method on a System.String class which saves the time so instead of string theString; if ( theString ! = null && theString.Length > 0 ) you write if ( !string.IsNullOrEmpty( theString ) ) I belive that the same pattern could be adopted for arrays so that instead of T[] array; if ( array ! = null && array.Length > 0 ) you could write if ( !Array.IsNullOrEmpty( array ) ) Regards, Wiktor Zychla .NET Framework Discussions IsNullOrEmpty (1) Array (1) I'm not sure what your point it. If you want to valuable update idea. https: / / connect.microsoft.com / availableconnections.aspx Robin S. - -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- keywords: [feature, request], Array.IsNullOrEmpty description: starting from .NET 2.0 there is a nice IsNullOrEmpty static method on a System.String class which saves the time so instead of string
or B? A) list_of_strings[0] = = "" B) list_of_strings[0].Length.Equals(0) .NET Performance Discussions String.IsNullOrEmpty (1) IsNullOrEmpty (1) AppDomain (1) ASP.NET (1) String.Empty (1) Section (1) Correct (1) Equals (1 jsh02_nova schrieb: Depending on what exactly you want to check, you could also use String.IsNullOrEmpty(list_of_strings[0]) or list_of_strings[0] = = string.Empty This avoids constructing a temporary string object that
4 ultimas palabras por * Por ejemplo HOLA1234 a HOLA* ** * Gracias VB.NET - Spanish Discussions String.IsNullOrEmpty (1) IsNullOrEmpty (1) Strings.StrDup (1) String.Format (1) Strings (1) Math (1) Substring (1) Format (1 ltimos cuatro caracteres te puede servir esto: Dim cadena As String = "Hola1234" If Not String.IsNullOrEmpty(cadena) Then cadena = cadena.Substring(0, Math.Max(0, cadena.Length - 4)) & _ Strings.StrDup