Hello,

I am using LLBLGen Pro V3.0, LLBLGEN Framework, Self Service Model. I am facing a performance problem when using linq queries and I want to use compiled queries to accelerate my system. Is there a way to do this?

Thanks.

Could you please explain in details, what performance problems are you facing?

Walaa wrote:

Could you please explain in details, what performance problems are you facing?

        var query = (from pd in ProductDepartmentEntity.Query
                     join p in ProductEntity.Query on pd.ProductDescriptionId equals p.ProductDescriptionId
                     join pdd in ProductDescriptionEntity.Query on pd.ProductDescriptionId equals pdd.Id
                     where
                     pd.DepartmentId == departmentId &&
                     pdd.IsValid == true &&
                     p.IsValid == true &&
                     (p.Stock > pdd.CriticalStock || pdd.IsAlwaysOnShow)
                     select pdd);


        FilterQuery = query;


        var firstSpecList = (from pl in FilterQuery
                             join p in ProductEntity.Query on pl.Id equals p.ProductDescriptionId
                             join s in SpecialityItemEntity.Query on p.SpecialityItemFirstId equals s.Id
                             where (p.Stock > p.ProductDescription.CriticalStock || p.ProductDescription.IsAlwaysOnShow)
                             && p.IsValid == true && p.ProductDescription.IsValid == true
                             group s by new { PDId = pl.Id, s.Id, s.Name, s.OrderNumber } into g
                             select g.Key).GroupBy(s => new { s.Id, s.Name, s.OrderNumber })
                             .OrderBy(v => v.Key.OrderNumber).Select(vi => new { vi.Key, Count = vi.Count() }).ToList();

I have a query like this, I call this query several times and this makes my system very slow, I need to cache this query, I should be able to parameterize this, so it will not make my CPU tired.

Thanks.

Did you do any profiling to know exactly where is the bottle neck? Is it from the database or not?

Walaa wrote:

Did you do any profiling to know exactly where is the bottle neck? Is it from the database or not?

Yes, I profiled it by using sql server profiler, and there is no significant overhead on sql server, but there is a significant overhead when creating sql command at these linq expressions, when I use visual studio SP1 CPU analyze tool.

Which runtime library version (build number) are you using?

Please use a profiler like dottrace or ants to see which piece of code consumes a lot of time. Traversing expression trees can be time consuming though it shouldn't take more than 10-20 milliseconds at most.

Keep in mind that if you fetch a lot of data, and you didn't specify a limit, so it very well can be, i.e. thousands of rows, it is slow, as all the data has to be transported to the client and materialized into objects. This isn't shown in sql server's profiler. See also Performance tuning