none
Making the Lina-to-sql transaction faster RRS feed

  • Question

  • Hi All,

     

    I hope someone can show me what I am doing  wrong.

     

    In a nutshell, I take a subset set of data from our db. based upon the month and year.

    Next I process the data to get counts and want to populate a new table.  The problem is....

    its taking a very long time.

     

     

    Here is an example

     

    Code Snippet

    FailureDataLiveDataContext dcFailure = new FailureDataLiveDataContext();

    Table<MonthlyEventCategory> tMonthlyEventCategory= dcFailure.GetTable<MonthlyEventCategory>();

     

    do

    {

    DateTime tDate = new DateTime(dtStart.Year, dtStart.Month, 1, 0, 0, 0);

    DateTime eDate = new DateTime(dtStart.Year, dtStart.AddMonths(1).Month, 1, 0, 0, 0);

    IQueryable<_FailureDataLive> 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 strEventCategory in alEventCat)

    {

    cMonthlyEventCategory[iMonthIndex].Date = dtStart;

    cMonthlyEventCategory[iMonthIndex].Region = strRegion;

    cMonthlyEventCategory[iMonthIndex].Site = strSite;

    cMonthlyEventCategory[iMonthIndex].AVRelevance = strAV;

    cMonthlyEventCategory[iMonthIndex].key = count;

     

    EventCategoryCallInEmail(tdata, ref cMonthlyEventCategory[iMonthIndex], strRegion, strSite, strAV,strEventCategory, dtStart, tempEnd);

     

    SystemFaultEventCategory(tdata, ref cMonthlyEventCategory[iMonthIndex], strRegion, strSite, strAV,strEventCategory, dtStart, tempEnd);

     

    EventCategoryNonR21(tdata, ref cMonthlyEventCategory[iMonthIndex], strRegion, strSite, strAV,strEventCategory, dtStart, tempEnd);

     

    EventCategoryBySite(tdata, ref cMonthlyEventCategory[iMonthIndex], strRegion, strSite, strAV,strEventCategory, dtStart, tempEnd);

     

    EventcategoryMinutes(tdata, ref cMonthlyEventCategory[iMonthIndex], strRegion, strSite,strAV, strEventCategory, dtStart, tempEnd);

     

    EventcategoryWeightedMinutes(tdata, ref cMonthlyEventCategory[iMonthIndex], strRegion, strSite,strAV, strEventCategory, dtStart, tempEnd);

     

    EventCategoryAIBySite(tdata, ref cMonthlyEventCategory[iMonthIndex], strRegion, strSite,strAV, strEventCategory, dtStart, tempEnd);

     

    dcFailure.MonthlyEventCategories.InsertOnSubmit(cMonthlyEventCategory[iMonthIndex]);

     

    dcFailure.SubmitChanges();

     

    iMonthIndex++;

    count++;

    }

    }

    }

     

    dtStart = dtStart.AddMonths(1);

    } while (dtStart <= dtEnd);

     

     

    Inside the method calls, I'm actually doing the query on tdata to extract the infomration.

     

    I've tried te InsertAllOnSubmit also, but it did not seem to speed up anything.

    Is there any way to speed up this transaction, it is taking hours to do, where it should take proabaly less than 2 minutes.
     
    Thanks
     
     

     

    Thursday, August 7, 2008 3:46 PM

Answers

  • Do you really need to pull all of this data off of the server onto the client and then push it back to the server? I would suspect that this may be a case where you would want to perform the work directly on the server through stored procs or SSIS rather than worrying with the business tier. LINQ to SQL is good for hydrating object structures, but in this case, I'm not sure that is the best fit for your need.

     

    If there is significant .NET processing that you need to do with your data, consider adding some LoadOptions to prefetch child data with your parent data. You will still experience issues with the network bandwidth and object hydration, but the interaction with your database will be less chatty. Try using SQL Profiler to watch the TSQL that is generated and when it is issued to start identifying ways of improving your performance.

     

    Jim Wooley

    www.ThinqLinq.com

    Thursday, August 7, 2008 6:30 PM
    Moderator

All replies

  • It is hard to make informed suggestions without any data on exactly why/where the program is performing poorly. I see that you have four nested loops...that might be something to could investigate.

     

    However, if you determine that data access/update is the cause of the slow performance, you might consider batching updates into a stored procedure.

     

    Thanks,

     

    --Samir

     

    Thursday, August 7, 2008 4:39 PM
  • Do you really need to pull all of this data off of the server onto the client and then push it back to the server? I would suspect that this may be a case where you would want to perform the work directly on the server through stored procs or SSIS rather than worrying with the business tier. LINQ to SQL is good for hydrating object structures, but in this case, I'm not sure that is the best fit for your need.

     

    If there is significant .NET processing that you need to do with your data, consider adding some LoadOptions to prefetch child data with your parent data. You will still experience issues with the network bandwidth and object hydration, but the interaction with your database will be less chatty. Try using SQL Profiler to watch the TSQL that is generated and when it is issued to start identifying ways of improving your performance.

     

    Jim Wooley

    www.ThinqLinq.com

    Thursday, August 7, 2008 6:30 PM
    Moderator