Why is my query so slow?

Question

I try to tune my query but I have no idea what I can change:

A screenshot of both tables: http://abload.de/image.php?img=1plkyg.jpg
The relation is: 1 UserPM (a Private Message) has 1 Sender (User, SenderID -> User.SenderID) and 1 Recipient (User, RecipientID -> User.UserID) and 1 User has X UserPMs as Recipient and X UserPMs as Sender.
The intial load takes around 200ms, it only takes the first 20 rows and display them. After this is displayed a JavaScript PageMethod gets the GetAllPMsAsReciepient method and loads the rest of the data
this GetAllPMsAsReciepient method takes around 4.5 to 5.0 seconds each time to run on around 250 rows

My code:

    public static List<UserPM> GetAllPMsAsReciepient(Guid userID)
    {
        using (RPGDataContext dc = new RPGDataContext())
        {
            DateTime dt = DateTime.Now;

            DataLoadOptions options = new DataLoadOptions();
            //options.LoadWith<UserPM>(a => a.User);
            options.LoadWith<UserPM>(a => a.User1);
            dc.LoadOptions = options;

            List<UserPM> pm = (
                      from a in dc.UserPMs 
                      where a.RecieverID == userID 
                      && !a.IsDeletedRec 
                      orderby a.Timestamp descending select a
            ).ToList();

            TimeSpan ts = DateTime.Now - dt;
            System.Diagnostics.Debug.WriteLine(ts.Seconds + "." + ts.Milliseconds);

            return pm;
        }
    }

I have no idea how to tune this Query, I mean 250 PMs are nothing at all, on other inboxes on other websites I got around 5000 or something and it doesn't need a single second to load...

I try to set Indexes on Timestamp to reduce the Orderby time but nothing happend so far.

Any ideas here?

EDIT

I try to reproduce it on LinqPad: Without the DataLoadOptions, in LinqPad the query needs 300ms, with DataLoadOptions around 1 Second.

So, that means:

I could save around 60% of the time, If I can avoid to load the User-table within this query, but how?
Why Linqpad needs only 1 second on the same connection, from the same computer, where my code is need 4.5-5.0 seconds?
Here is the execution plan: http://abload.de/image.php?img=54rjwq.jpg
Here is the SQL Linqpad gives me:

SELECT [t0].[PMID], [t0].[Text], [t0].[RecieverID], [t0].[SenderID], [t0].[Title], [t0].[Timestamp], [t0].[IsDeletedRec], [t0].[IsRead], [t0].[IsDeletedSender], [t0].[IsAnswered], [t1].[UserID], [t1].[Username], [t1].[Password], [t1].[Email], [t1].[RegisterDate], [t1].[LastLogin], [t1].[RegisterIP], [t1].[RefreshPing], [t1].[Admin], [t1].[IsDeleted], [t1].[DeletedFrom], [t1].[IsBanned], [t1].[BannedReason], [t1].[BannedFrom], [t1].[BannedAt], [t1].[NowPlay], [t1].[AcceptAGB], [t1].[AcceptRules], [t1].[MainProfile], [t1].[SetShowHTMLEditorInRPGPosts], [t1].[Age], [t1].[SetIsAgePublic], [t1].[City], [t1].[SetIsCityShown], [t1].[Verified], [t1].[Design], [t1].[SetRPGCountPublic], [t1].[SetLastLoginPublic], [t1].[SetRegisterDatePublic], [t1].[SetGBActive], [t1].[Gender], [t1].[IsGenderVisible], [t1].[OnlinelistHidden], [t1].[Birthday], [t1].[SetIsMenuHideable], [t1].[SetColorButtons], [t1].[SetIsAboutMePublic], [t1].[Name], [t1].[SetIsNamePublic], [t1].[ContactAnimexx], [t1].[ContactRPGLand], [t1].[ContactSkype], [t1].[ContactICQ], [t1].[ContactDeviantArt], [t1].[ContactFacebook], [t1].[ContactTwitter], [t1].[ContactTumblr], [t1].[IsContactAnimexxPublic], [t1].[IsContactRPGLandPublic], [t1].[IsContactSkypePublic], [t1].[IsContactICQPublic], [t1].[IsContactDeviantArtPublic], [t1].[IsContactFacebookPublic], [t1].[IsContactTwitterPublic], [t1].[IsContactTumblrPublic], [t1].[IsAdult], [t1].[IsShoutboxVisible], [t1].[Notification], [t1].[ShowTutorial], [t1].[MainProfilePreview], [t1].[SetSound], [t1].[EmailNotification], [t1].[UsernameOld], [t1].[UsernameChangeDate] FROM [UserPM] AS [t0] INNER JOIN [User] AS [t1] ON [t1].[UserID] = [t0].[RecieverID] WHERE ([t0].[RecieverID] = @p0) AND (NOT ([t0].[IsDeletedRec] = 1)) ORDER BY [t0].[Timestamp] DESC

Most likely the `LoadWith` is what is slowing down the query, Linq probably makes one select statement per row to get the value. Try removing it and try it. You can use a tool such as LinqPad to analyse the SQL generated by linq queries. — Magnus, Oct 23 '13 at 13:38
LinqPad is a good suggestion, but I usually just go right to the DB and use the Profiler to see what the queries are doing. — Al W, Oct 23 '13 at 13:41
without LoadWith I cant use this query because I need the Usernames — PassionateDeveloper, Oct 23 '13 at 13:43
Are you sure that RecieverID have an index? What is the generated query? `var query = from a... DbCommand db = dc.GetCommand(q); Console.WriteLine(dc.CommandText);` In how much time this query run? — Cyril Gandon, Oct 23 '13 at 13:48
@Magnus I tried this, but gives me an Error in LKinqPad: DataLoadOptions options = new DataLoadOptions(); options.LoadWith(a => a.User1); this.LoadOptions = options (from a in UserPMs where a.RecieverID == Guid.Parse("6ed39f74-5a03-4690-8af1-e4c034570406") && !a.IsDeletedRec orderby a.Timestamp descending select a).ToList() — PassionateDeveloper, Oct 23 '13 at 13:51
@CyrilGandon Yes im Sure: http://abload.de/image.php?img=210jyf.jpg — PassionateDeveloper, Oct 23 '13 at 13:53
@Kovu "gives me an Error" is a little vague. Can you be more specific? — Magnus, Oct 23 '13 at 14:02
@Kovu change the language dropdown to "C# Statement(s)" And add a `.Dump();` after `.ToList()` — Magnus, Oct 23 '13 at 14:10
Doesn't work at all... http://abload.de/image.php?img=4ktkcy.jpg — PassionateDeveloper, Oct 23 '13 at 14:13
@Kovu I'm sure you'll figure it out. If not check the FAQ or the Linqpad Forum. — Magnus, Oct 23 '13 at 14:17
@Kovu How did the SQL look? Was it one statement or many? Put it in Management studio and analyse the executionplan. You'll see where indexing is needed — Magnus, Oct 23 '13 at 14:28
@Kovu You have a missing index suggestions in the plan, click on that and add it. — Magnus, Oct 23 '13 at 15:43

score 0 · Answer 1 · answered Oct 23 '13 at 13:53

0

If you want to get rid of the LoadWith, you can select your field explicitly :

public static List<Tuple<UserPM, User> > GetAllPMsAsReciepient(Guid userID)
{
    using (var dataContext = new RPGDataContext())
    {
        return (
            from a in dataContext.UserPMs 
            where a.RecieverID == userID 
            && !a.IsDeletedRec 
            orderby a.Timestamp descending 
            select Tuple.Create(a, a.User1)
        ).ToList();
    }
}

answered Oct 23 '13 at 13:53

Cyril Gandon

16,830
14
78
122

What is a Tuple? Can you eplain it? How to use the list after this? Can I do User.Username in a Girdviewcell after this? – PassionateDeveloper Oct 23 '13 at 13:55
[What requirement was the tuple designed to solve?](http://stackoverflow.com/questions/3089706/what-requirement-was-the-tuple-designed-to-solve) – Cyril Gandon Oct 23 '13 at 13:57

score 0 · Accepted Answer · answered Oct 23 '13 at 15:22

I found a solution:

At first it seems that with the DataLoadOptions is something not okay, at second its not clever to load a table with 30 Coloumns when you only need 1.

To Solve this, I make a view which covers all nececeery fields and of course the join.

It reduces the time from 5.0 seconds to 230ms!

Why is my query so slow?

EDIT

2 Answers2