为什么我的查询这么慢？ [英] Why is my query so slow?

问题描述

我尝试调整我的查询，但我不知道我可以改变：

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

• 这两个表的截图： http://abload.de/image.php？ IMG = 1plkyg.jpg


• 的关系是：1 UserPM（私人讯息），具有1发送者（用户，SenderID - > User.SenderID）和1收件人（用户，RecipientID - > User.UserID）和1个用户具有点¯xUserPMs作为收件人和X UserPMs作为发件人。


• 的intial负荷大约需要200毫秒，只需要前20行，并显示它们。在此之后将显示一个JavaScript PageMethod的获取 GetAllPMsAsReciepient 方法并加载数据的其余部分


• 这 GetAllPMsAsReciepient 方法需要大约每次大约250行运行4.5秒至5.0秒

• 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

我的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;
        }
    }

我不知道如何来调整这个查询，我的意思是250 PM被什么都没有，对我有5000左右的其他网站或其他东西收件箱和它不需要一秒钟加载...

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...

我尝试设置时间戳的指标，以减少排序依据的时间，但没有到目前为止happend。

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

任何想法吗？

我试图去模仿它LinqPad：
如果没有DataLoadOptions，在LinqPad查询需要300毫秒，与DataLoadOptions 1秒左右。

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

所以，这意味着：

• 我可以节省时间60％左右，如果我能避免这个查询，但如何在加载用户表？


• 为什么Linqpad只需要1个在同一连接上第二个，从同一计算机，我的code为4.5-5.0需要秒？


• 下面是执行计划： http://abload.de/image.php？ IMG = 54rjw​​q.jpg


• 下面是SQL Linqpad给我：

选择[T0]。[PMID]，[T0]。[文字]，[T0]。[RecieverID]，[T0]。[SenderID]，[T0]。[标题]，[T0]。[时间戳]，[T0]。[IsDeletedRec]，[T0]。[IsRead]，[T0]。[IsDeletedSender]，[T0]。[IsAnswered]，[T1]。[用户名]，[T1]。[用户名] [T1]。[密码]，[T1] [邮件]，[T1]。[RegisterDate]，[T1]。[LastLogin]，[T1]。[RegisterIP]，[T1]。[RefreshPing]，[T1 ]。[管理员]，[T1]。[请将isDeleted]，[T1]。[DeletedFrom]，[T1]。[IsBanned]，[T1]。[BannedReason]，[T1]。[BannedFrom]，[T1]。 [BannedAt]，[T1]。[NowPlay]，[T1]。[AcceptAGB]，[T1]。[AcceptRules]，[T1]。[主档次]，[T1]。[SetShowHTMLEditorInRPGPosts]，[T1]。[年龄]，[T1]。[SetIsAgePublic]，[T1] [城市]，[T1]。[SetIsCityShown]，[T1] [已核实]，[T1] [设计]，[T1]。[SetRPGCountPublic] [T1]。[SetLastLoginPublic]，[T1]。[SetRegisterDatePublic]，[T1]。[SetGBActive]，[T1]。[性别]，[T1]。[IsGenderVisible]，[T1]。[OnlinelistHidden]，[T1 ]。[生日]，[T1]。[SetIsMenuHideable]，[T1]。[SetColorButtons]，[T1]。[SetIsAboutMePublic]，[T1]。[名]，[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]。[通知]，[T1 ]。[ShowTutorial]，[T1]。[主档次preVIEW]，[T1]。[SetSound]，[T1]。[EmailNotification]，[T1]。[UsernameOld]，[T1]。[UsernameChangeDate]
FROM [UserPM] AS [T0]
INNER JOIN [用户] AS [T1] ON [T1]。[用户名] = [T0]。[RecieverID]
WHERE（[T0]。[RecieverID] = @ P0）AND（NOT（[T0]。[IsDeletedRec] = 1））
ORDER BY [T0]。[时间戳] DESC

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

推荐答案

我找到了解决方法：

起初它似乎与DataLoadOptions东西不行，在第二次的不聪明加载表30 Coloumns时，你只需要1。

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.

要解决这个问题，我做它涵盖了所有领域nececeery，当然是一个视图加入。

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

它减少了5.0秒的时间230ms！

It reduces the time from 5.0 seconds to 230ms!

这篇关于为什么我的查询这么慢？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！