'Timeout Expired' error when buffer cache clean

I am going to sum up my problem first and then offer massive details and what I have already tried.

Summary:

I have an internal winform app that uses Linq 2 Sql to connect to a local SQL Express database. Each user has there own DB and the DB stay in sync through Merge Replication with a Central DB. All DB's are SQL 2005(sp2or3). We have been using this app for over 5 months now but recently our users are getting a Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Detailed:

The strange part is they get that in two differnt locations(2 differnt LINQ Methods) and only the first time they fire in a given time period(~5mins).

One LINQ me开发者_Python百科thod is pulling all records that match a FK ID and then Manipulating them to form a Heirarchy View for a TreeView. The second is pulling all records that match a FK ID and dumping them into a DataGridView. The only things I can find in common with the 2 are that the first IS an IEnumerable and the second converts itself from IQueryable -> IEnumerable -> DataTable...

I looked at the query's in Profiler and they 'seemed' normal. They are not very complicated querys. They are only pulling back 10 - 90 records, from one table.

Any thoughts, suggestions, hints whatever would be greatly appreciated. I am at my wit's end on this....

---

public IList<CaseNoteTreeItem> GetTreeViewDataAsList(int personID)
{
    var myContext = MatrixDataContext.Create();

    var caseNotesTree =
        from cn in myContext.tblCaseNotes
        where cn.PersonID == personID
        orderby cn.ContactDate descending,
            cn.InsertDate descending
        select new CaseNoteTreeItem
        {
            CaseNoteID = cn.CaseNoteID,
            NoteContactDate = Convert.ToDateTime(cn.ContactDate).
                ToShortDateString(),
            ParentNoteID = cn.ParentNote,
            InsertUser = cn.InsertUser,
            ContactDetailsPreview = cn.ContactDetails.Substring(0, 75)
        };

    return caseNotesTree.ToList<CaseNoteTreeItem>();            
}

AND THIS ONE

public static DataTable GetAllCNotes(int personID)
{
    using (var context = MatrixDataContext.Create())
    {
        var caseNotes =
            from cn in context.tblCaseNotes
            where cn.PersonID == personID
            orderby cn.ContactDate
            select new
            {
                cn.ContactDate,
                cn.ContactDetails,
                cn.TimeSpentUnits,
                cn.IsCaseLog,
                cn.IsPreEnrollment,
                cn.PresentAtContact,
                cn.InsertDate,
                cn.InsertUser,
                cn.CaseNoteID,
                cn.ParentNote
            };

        return caseNotes.ToList().CopyLinqToDataTable();
    }
}

---

EDIT to show generated SQL

this is GetTreeViewAsList(int personID)

SELECT [t0].[CaseNoteID], [t0].[ParentNote] AS [ParentNoteID], CONVERT(DateTime,[t0].[ContactDate]) AS [value], [t0].[InsertUser], SUBSTRING([t0].[ContactDetails], 0 + 1, 75) AS [ContactDetailsPreview]
FROM [dbo].[tblCaseNotes] AS [t0]
WHERE [t0].[PersonID] = 123456
ORDER BY [t0].[ContactDate] DESC, [t0].[InsertDate] DESC

and this is GetALlCaseNotes(int personID)

SELECT [t0].[ContactDate], [t0].[ContactDetails], [t0].[TimeSpentUnits], [t0].[IsCaseLog], [t0].[IsPreEnrollment], [t0].[PresentAtContact], [t0].[InsertDate], [t0].[InsertUser], [t0].[CaseNoteID], [t0].[ParentNote]
FROM [dbo].[tblCaseNotes] AS [t0]
WHERE [t0].[PersonID] = 123456
ORDER BY [t0].[ContactDate]

---

The lack of an index on PersonId means that both of the problem queries need to do a full table scan. When the data is not in the buffer already this means a lot of I/O which explains why you notice it particularly the first time they fire in a given time period.

Adding the following index should resolve it.

CREATE NONCLUSTERED INDEX ix_tblCaseNotes_PersonID ON tblCaseNotes (PersonID)

---

It is a bit a long shot, but perhaps the problem is not one of performance, but of connectivity. When your SQL Server configured to use Named Pipes instead of the default protocol, it can take a long time before the .NET SqlClient can establish a connection, because it tries to connect using the default protocol order.

You can read more about this, here.

Good luck.