LINQ Caching help
-
I have a db probject that I am working on, needless to say, without cahcing, the transactions take a long time, like 10 hours for 6000 rows. Is there a way to cache the main query in local memory
do
{
IQueryable<_FailureData> tdata = from tempFailure in tFailureData
.Where(cBaseFilters.GetDateFilter(tDate))
select tempFailure;foreach (string strAV in alAVRelevance) { foreach (string strRegion in alRegions) { foreach (string strSite in alSites) { foreach (string strFixClass in alRestore) { RecoveryTotals(tdata, ref cMonthlyRecovery\[iMonthIndex\], strRegion, strSite, strAV, strFixClass, dtStart, tempEnd); RecoveryAITotals(tdata, ref cMonthlyRecovery\[iMonthIndex\], strRegion, strSite, strAV, strFixClass, dtStart, tempEnd); RecoveryMinutes(tdata, ref cMonthlyRecovery\[iMonthIndex\], strRegion, strSite, strAV, strFixClass, dtStart, tempEnd); RecoveryWeightedMinutes(tdata, ref cMonthlyRecovery\[iMonthIndex\], strRegion, strSite, strAV, strFixClass, dtStart, tempEnd); } } } } dtStart = dtStart.AddMonths(1);} while (dtStart <= dtEnd);
Where tdata is a subset of data based upon a date range, then in each method, I perform a query on tdata to extract the appropriate information. I'm at a loss as of what to do, any help is appreciated. Thanks
-
I have a db probject that I am working on, needless to say, without cahcing, the transactions take a long time, like 10 hours for 6000 rows. Is there a way to cache the main query in local memory
do
{
IQueryable<_FailureData> tdata = from tempFailure in tFailureData
.Where(cBaseFilters.GetDateFilter(tDate))
select tempFailure;foreach (string strAV in alAVRelevance) { foreach (string strRegion in alRegions) { foreach (string strSite in alSites) { foreach (string strFixClass in alRestore) { RecoveryTotals(tdata, ref cMonthlyRecovery\[iMonthIndex\], strRegion, strSite, strAV, strFixClass, dtStart, tempEnd); RecoveryAITotals(tdata, ref cMonthlyRecovery\[iMonthIndex\], strRegion, strSite, strAV, strFixClass, dtStart, tempEnd); RecoveryMinutes(tdata, ref cMonthlyRecovery\[iMonthIndex\], strRegion, strSite, strAV, strFixClass, dtStart, tempEnd); RecoveryWeightedMinutes(tdata, ref cMonthlyRecovery\[iMonthIndex\], strRegion, strSite, strAV, strFixClass, dtStart, tempEnd); } } } } dtStart = dtStart.AddMonths(1);} while (dtStart <= dtEnd);
Where tdata is a subset of data based upon a date range, then in each method, I perform a query on tdata to extract the appropriate information. I'm at a loss as of what to do, any help is appreciated. Thanks
Without much knowledge of the context it's hard to be specific in helping. - Is tFailureData a LINQ to SQL table? a view? - Is it millions of records? Is it indexed on the search field? - What do RecoveryTotals, RecoveryAllTotals ... do ? - Is there any lazy loading going on? Performance problems like this probably indicate an inefficient design - one that is repeatedly querying the database for lots of small amounts of data, instead of one big query. Use
DataContext.Log = Console.Outand see how much SQL is being executed. You might be better to preload all the data (depending on the total data set size) and THEN do the .Where() clause on the in-memory data. e.g.// force SQL to load *all* data first
IQueryable<_FailureData> allData = (from tempFailure in tFailureData).ToList();do{
IQueryable<_FailureData> tdata = from tempFailure in allData
.Where(cBaseFilters.GetDateFilter(tDate))If there are related tables to FailureData e.g. child tables, you may be causing lazy loading to fetch these for each row - e.g. 1000 rows = 1000 SQL queries. Use DataLoadOptions to prefetch if this is the case.
'Howard