0

Recently I have a small headache with Linq. I have one big table(more than 100 fields and for the test purpose more than 40000 rows), lets call it Products.

There is about 60 queries to Products table - that I have to show on Grid - queries are IQueryable objects, and I'm doing Count() on every of them.

Every query takes about 100ms(about 7s for all of them), so it takes lots of time to show the result to the user. Should I use StoredProcedure, or is there any other solution to this issue?

Best regards

EDIT: One of my query: var result = (from f in this.Context.Products where f.Period == currPeriod && (f.TYPE == 1 || f.TYPE == 2) && f.FLAGA > 0 && f.DATE_OK != null && UsersList.Contains(f.UsersId) select new ProductLista {});

In select new ProductList I take 10 fields from Products table. Also I know that Contains gives me big issue performance

michal
  • 71
  • 1
  • 8
  • and can you please share the queries with us? – Ashkan Mobayen Khiabani Feb 03 '17 at 09:46
  • How many items does `UsersList` typically contain? By the way, a table with > 100 fields sounds like an unhealthy db design, you probably need some normalization steps. – Gert Arnold Feb 03 '17 at 10:16
  • Users list is like from 100 to 4000. Is hard to say realy Unfortunately I dont have any influence on table design:( – michal Feb 03 '17 at 10:21
  • With 100 this shouldn't be a problem. If it is, you have to look at proper indexing. With larger numbers you must get the data in chunks: http://stackoverflow.com/q/24534217/861716. – Gert Arnold Feb 03 '17 at 11:26

1 Answers1

0

In your scenario it is better moving to Sp it will be more efficient also you can try only use Count In LINQ Except Select.

Damith Asanka
  • 934
  • 10
  • 12
  • I use count but on IQueryable result object, the reason is that i will use this query when user clicks on this counter result in grid. I dont want to have the same query twice in my project, that is way I went this way. – michal Feb 03 '17 at 09:59
  • If you want only get count please avoid select section in query. that will be performance issue. since if you use IQueryable you can get count without execute Select statement. you can it later when you need display all data – Damith Asanka Feb 03 '17 at 10:05
  • So I should make 2 queries one for count one for select? I thought IQueryable is nice to deal with count and select at one time. – michal Feb 03 '17 at 10:11
  • Please use Lambda Expression that will be help you to resolve this issue var reuslt = Context.Products where(f=> f.Period == currPeriod && (f.TYPE == 1 || f.TYPE == 2) && f.FLAGA > 0 && f.DATE_OK != null && UsersList.Contains(f.UsersId)).count(); This is Deffered Query – Damith Asanka Feb 03 '17 at 10:11
  • no need 2 query just put main selection into IQueyable then you can execute count and Select method separately like below e var reuslt = Context.Products where(f=> f.Period == currPeriod && (f.TYPE == 1 || f.TYPE == 2) && f.FLAGA > 0 && f.DATE_OK != null && UsersList.Contains(f.UsersId)); if you need count : result.Count(); If you need select : result.select(a=> new ProductLista { name = a.name}); – Damith Asanka Feb 03 '17 at 10:14
  • So, I changed all my queries just like you sad...there's no significant improve. I think I will go with stored procedure. The biggest influence on query performance gives UsersList.Contains. Without it, there's 40ms inmprove on each query – michal Feb 03 '17 at 11:17