Linq query causing performance Issue

Question

    foreach (DictionaryEntry ID in IDs)  
      {  
         int qWorkID = int.Parse(ID.Value.ToString());   
     var QWF = (from qwf in Model.QWorkflowSet  
         where qwf.QWorkID == qWorkID  
          select qwf).FirstOrDefault();  
          QWF.ClaimedBy = ClaimedBy;  
          QWF.ReleaseTypeID = 177;  
           QWF.ClaimedDt = ClaimedDt;      
    	   }  
        Model.SaveChanges();  

Above Linq is causing performance Issue...Not sure FirstOrDefault is causing this..

var QWF = (from qwf in Model.QWorkflowSet
where qwf.QWorkID == qWorkID
select qwf).FirstOrDefault();

Answer 1

Maybe the problem is caused by multiple SQL queries that are performed inside the loop. Check if it is possible to do something like this:

var ids = IDS.Values.Select( id => Convert.ToInt32( id)).ToList( );  
foreach( var qwf in Model.QWorkflowSet.Where( w => ids.Contains( w.QWorkID))  
{  
   qwf.ClaimedBy = ClaimedBy;  
   qwf.ReleaseTypeID = 177;  
   qwf.ClaimedDt = ClaimedDt;      
}  
Model.SaveChanges();  

Adjust it according to details.

There is an alternative: to define a stored procedure in SQL and pass all of the values to execute a single UPDATE statement.

Answer 2

This should be a comment but it's too long. In Visual Studio project, create a new text file with .sql extension.

Get column details, replace Customers with your table name.

SELECT c.name 'Column Name',  
       t.name 'Data type',  
       c.max_length 'Max Length',  
       c.precision,  
       c.scale,  
       c.is_nullable,  
       ISNULL(i.is_primary_key, 0) 'Primary Key'  
FROM sys.columns c  
    INNER JOIN sys.types t  
        ON c.user_type_id = t.user_type_id  
    LEFT OUTER JOIN sys.index_columns ic  
        ON ic.object_id = c.object_id  
           AND ic.column_id = c.column_id  
    LEFT OUTER JOIN sys.indexes i  
        ON ic.object_id = i.object_id  
           AND ic.index_id = i.index_id  
WHERE c.object_id = OBJECT_ID('Customers');  

Another .sql file to get table indices

SELECT   
     TableName = t.name,  
     IndexName = ind.name,  
     IndexId = ind.index_id,  
     ColumnId = ic.index_column_id,  
     ColumnName = col.name,  
     ind.*,  
     ic.*,  
     col.*   
FROM   
     sys.indexes ind   
INNER JOIN   
     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id   
INNER JOIN   
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id   
INNER JOIN   
     sys.tables t ON ind.object_id = t.object_id   
WHERE   
     ind.is_primary_key = 0   
     AND ind.is_unique = 0   
     AND ind.is_unique_constraint = 0   
     AND t.is_ms_shipped = 0   
ORDER BY   
     t.name, ind.name, ind.index_id, ic.is_included_column, ic.key_ordinal;  

These quires may help determine the problem.

Answer 3

Most likely you are missing an index. Use Sql profiler and query analyzer.

Answer 4

Hi,

I assume QWorkID is unique and you expect a single row ? You are not using eager loading ? I would use Count() to see how many rows are available from this table, would perhaps try a a quick repro of something similar and would report back to those in charge of the db.

FirstOrDefault should do a SELECT TOP 1 to get either the first row or no row at all. BTW how do you see your issue is here in particular(rather than maybe on SaveChanges ?)

Answer 5

IMO stop trying random changes. If you don't have access to the db, talk with your DBA. If you don't have an index on QWorkID, the db server must scans the whole table (which is why I wanted to understand if this table is storing 10, 100, millions or even a higher number of rows). The DBA could also try a query in this table and have tools to see if it performs as expected on his side...

Yes, it can be other stuff such as having columns with huge content(s), using eager loading for related tables etc... but it's likely best to start with usual suspects and close doors before moving to the next possible issue.

BTW I assume it happens only here and that you do have other similar queries causing no issue at all ?