Last post Sep 08, 2014 08:48 PM by slen
Aug 28, 2014 02:52 AM|slen|LINK
I have a function which will do the filter-ation of all records in the database using linq.
Sometimes it gave me time out error when I tried to filter in a foreign table..
List<Func<Person, bool>> predicatePerson = null;
predicatePerson = new List<Func<Person, bool>>();
IQueryable<int> lstPersonID = GeneralAction.GetQueryable<DynamicDataControllerModel.Email>(p => !string.IsNullOrEmpty(p.Email1)).Select(p => p.Person_id);
predicatePerson.Add(p => lstPersonID.Contains(p.Id));
IQueryable<Person> listPerson = GeneralAction.GetQueryable<Person>();
foreach (var a in predicatePerson)
listPerson = listPerson.Where(a).AsQueryable();
var finallst = (from a in listPerson
join b in listMember on a.Id equals b.Person_id
join c in GeneralAction.GetQueryable<DynamicDataControllerModel.Address>(p => p.IsDeleted == 0) on ((a != null) ? a.AddressMailing_id : 0) equals c.Id into clst
from c in clst.DefaultIfEmpty()
join d in GeneralAction.GetQueryable<DynamicDataControllerModel.Postcode>(p => p.IsDeleted == 0) on ((c != null) ? c.Postcode_id : 0) equals d.Id into dlst
from d in dlst.DefaultIfEmpty()
join e in GeneralAction.GetQueryable<DynamicDataControllerModel.State>(p => p.IsDeleted == 0) on ((d != null) ? d.State_id : 0) equals e.Id into elst
from e in elst.DefaultIfEmpty()
join f in GeneralAction.GetQueryable<DynamicDataControllerModel.Country>(p => p.IsDeleted == 0) on ((e != null) ? e.Country_id : 0) equals f.Id into flst
from f in flst.DefaultIfEmpty()
join g in GeneralAction.GetQueryable<>(p => p.IsDeleted == 0) on b.Id equals g.Member_id into glst
from g in glst.DefaultIfEmpty()
select new GeneralCommonFunctions.MemberDetailSumary
If there any approach to do this ...extracting fast ....
Aug 28, 2014 09:27 PM|sdg|LINK
you can set the commandtimeout property of your datacontext,
db.CommandTimeOut = 60;
var query = db.Query();
You can refer this link below:
Aug 30, 2014 05:43 AM|PatriceSc|LINK
It seems also your query is perhaps more complex than needed and could be perhaps simplified (navigation properties rarher than join into, defaultifempty really needed,, just adding criteria you do need, are those join usefull in your final result or just
for testing the criteria?). Watch the generated underlying SQL and how it performs (needed index are there).
Sep 03, 2014 12:03 AM|slen|LINK
Thanks for the suggestion. I reduced most of the columns I do not need. But, the process is still very slow.
One question, isn't join will be faster process than navigating properties? If navigating, I will have more than 19000 records to navigate.
currently, I have no idea using any approach to fasten this process yet.
it takes more than 10 minutes to exporting more than 19000 records to excel
Sep 03, 2014 04:21 AM|Kevin Shen - MSFT|LINK
I think the process is so slow is not much related with your retrieving data from database.
I guess most of the time are consumed on exporting records to excel.
I suggest that you can set a breakpoint on your code which you retrieved data from database.
Or computed the time are consumed on the two process.
DateTime begin= DateTime.Now;
//retrieve record from database
DateTime end = DateTime.Now;
DateTime begin1= DateTime.Now;
//export records to excel;
DateTime end1 = DateTime.Now;
You can compare the two TimeSpan check if it is the time is consumed in exporting to excel.
Sep 03, 2014 09:00 AM|PatriceSc|LINK
Not really IMO but it seems better to think about EF from a C# rather from a SQL perspective (ie you query a main object wiht child collections, you don't do SQL joins, would have to confirm this).
The "export to Excel" is the direction i would go. What is your approach? Note that Excel interop is slow (and won"t work well on a true web server). If this is what you use this is more likely the cullprit (and if still using interop, assing an array to
an excel range in one go is far quicker than filling individual cells).
Sep 08, 2014 08:48 PM|slen|LINK
Thanks for the suggestion on EF (Navigation Properties) It is a lot faster now. But this added another problem in my another function. I will add the problem in another thread. It is about DataContractSerializer.
Yes. I am using the Excel Interop.
I have no idea what else that I can use other than interop. I tried the using the HTTPContext.Current.Respone by creating the HTML tables. but this doesn't work on PageMethods function. I needed to use this because I needed to have an loading and unloading
screen loading for the process of exporting data to excel.