时间戳

首页 > 解决方案 > 程序执行时间过长

asp.net-mvc - 程序执行时间过长

问题描述

我正在使用存储过程来获取数据。我在其中使用了多个连接和内部查询，这就是让它太慢的原因。是否有任何替代方法可以避免对主从表进行内部查询？

实际上，早些时候我使用 LINQ 查询来获取数据，但执行时间也太长，然后我选择了存储过程，但执行时间也相同。那么我怎样才能让它更快呢？

这是我的存储过程：

ALTER PROCEDURE USP_GetUserDetailByID 
    @id INT
AS
BEGIN
    SELECT TOP 1
        uinfo.ID ID,
        uinfo.UniqueUserID UniqueUserID,
        uinfo.ID UserInfoID,
        uinfo.ReferredID ReferredID,
        uinfo.UserTypeID UserTypeID,
        uinfo.GroomBrideName GroomBrideName,
        uinfo.EmailID EmailID,
        uinfo.[Password] [Password], 
        uinfo.ProfileCreatedFor ProfileCreatedFor,
        uinfo.DateOfBirth DateOfBirth,
        uinfo.Gender Gender,
        uinfo.Age Age,
        uinfo.Height Height,
        uinfo.City City,
        uinfo.Country Country,
        uinfo.Phone Phone,
        uinfo.Mobile Mobile,
        uinfo.RegisteredMobileNumber RegisteredMobileNumber,
        uinfo.About About,
        uinfo.[Status] [Status],
        rel.Religion Religion,
        rel.MotherTongue MotherTongue,
        rel.Section Section,
        rel.Division Division,
        edu.EducationLevel EducationalLevel,
        edu.EducationFeild EducationalField,
        edu.GraduationDegree GraduationDegree,
        edu.MasterDegree MasterDegree,
        edu.WorkWith WorkWith,
        edu.WorkingGroup WorkingGroup,
        edu.WorkingAs WorkingAs,
        edu.AnnualIncome AnnualIncome,
        edu.InterestedInSettlingAbroad InsertedInSettingAbroad,
        hob.Hobbies Hobbies,
        hob.DressStyle,
        hob.SportsFitness SportsFitness,
        hob.FavouriteCuisine FavouriteCuisine,
        hob.FavouriteVacationDestination FavouriteVacationDestination,
        hob.FavouriteMusic FavouriteMusic,
        hob.FavouriteBooks FavouriteBooks,
        hob.PreferredMovies PreferredMovies,
        life.Diet Diet,
        life.Smoke Smoke,
        life.Drink Drink,
        life.SkinTone Complexion,
        life.BodyType BodyType,
        life.BloodGroup BloodGroup,
        life.[Weight] [Weight],
        life.ResidentialStatus ResidentialStatus,
        life.OwnHouse OwnHouse,
        life.OwnCar OwnCar,
        life.LanguageKnown LanguageKnown,
        fd.FamilyValue FamilyValue,
        (SELECT DetailData FROM MasterDetails WHERE ID = fd.FamilyValue) FamilyValueText,
        fd.FamilyType FamilyType,
        (SELECT DetailData FROM MasterDetails WHERE ID = fd.FamilyType) FamilyTypeText,
        fd.FamilyStatus FamilyStatus,
        (SELECT DetailData FROM MasterDetails WHERE ID = fd.FamilyStatus) FamilyStatusText,
        fd.Father Father,
        (SELECT DetailData FROM MasterDetails WHERE ID = fd.Father) FatherText,
        fd.Mother Mother,
        (SELECT DetailData FROM MasterDetails WHERE ID = fd.Mother) MotherText,
        fd.Brothers Brothers,
        fd.MarriedBrothers MarriedBrothers,
        fd.Sisters Sisters,
        fd.MarriedSisters MarriedSisters,
        fd.LivingWithParents LivingWithParents,
        rel.ReligiousValues ReligiousValues,
        uinfo.HightLightUserAbout HightLightUserAbout,
        uinfo.FbUserDetail FbUserDetail,
        (SELECT DetailData FROM  MasterDetails WHERE ID = uinfo.ProfileCreatedFor) ProfileCreatedText,
        (SELECT DetailData FROM  MasterDetails WHERE ID = uinfo.Height) HeightText,
        (SELECT ID FROM MasterDetails WHERE ID =  uinfo.MaritalStatus) MaritalStatus,
        (SELECT DetailData FROM  MasterDetails WHERE ID = uinfo.MaritalStatus) MaritalStatusText,
        (SELECT ID FROM MasterDetails WHERE ID = uinfo.[State]) [State],
        CASE WHEN uinfo.City IS NULL THEN '' ELSE (SELECT DetailData FROM MasterDetails WHERE ID = uinfo.City) END as CityText,
        CASE WHEN uinfo.[State] IS NULL THEN '' ELSE (SELECT DetailData FROM MasterDetails WHERE ID = uinfo.[State]) END as StateText,
        (SELECT MasterCategoryID FROM MasterDetails WHERE ID = uinfo.Country) CountryId,
        (SELECT DetailData FROM MasterDetails WHERE ID = uinfo.Country) CountryText,
        (SELECT DetailData FROM MasterDetails WHERE ID = rel.MotherTongue) MotherTongueText,
        (SELECT DetailData FROM MasterDetails WHERE ID = rel.Section) SectionText,
        (SELECT DetailData FROM MasterDetails WHERE ID = rel.Division) DivisonText,
        (SELECT DetailData FROM MasterDetails WHERE ID = edu.EducationLevel) EducationalLevelText,
        (SELECT DetailData FROM MasterDetails WHERE ID = edu.EducationFeild) EducationalFeildText,
        (SELECT DetailData FROM MasterDetails WHERE ID = edu.GraduationDegree) GraduationDegreeText,
        (SELECT DetailData FROM MasterDetails WHERE ID = edu.MasterDegree) MasterDegreeText,
        (SELECT DetailData FROM MasterDetails WHERE ID = edu.WorkWith) WOrkingWithText,
        (SELECT DetailData FROM MasterDetails WHERE ID = edu.WorkingGroup) WorkingGroupText,
        (SELECT DetailData FROM MasterDetails WHERE ID = edu.WorkingAs) WorkingAsText,
        (SELECT DetailData FROM MasterDetails WHERE ID = edu.AnnualIncome) AnnualIncomeText,
        (SELECT DetailData FROM MasterDetails WHERE ID = hob.Hobbies) HobbiesText,
        (SELECT DetailData FROM MasterDetails WHERE ID = hob.DressStyle) DressStyleText,
        (SELECT DetailData FROM MasterDetails WHERE ID = hob.SportsFitness) SportsFitnessText,
        (SELECT DetailData FROM MasterDetails WHERE ID = hob.FavouriteCuisine) FavouriteCuisineText,
        (SELECT DetailData FROM MasterDetails WHERE ID = hob.FavouriteMusic) FavouriteMusicText,
        (SELECT DetailData FROM MasterDetails WHERE ID = hob.PreferredMovies) PreferredMoviesText,
        (SELECT DetailData FROM MasterDetails WHERE ID = life.Diet) DietText,
        (SELECT DetailData FROM MasterDetails WHERE ID = life.SkinTone) ComplexionText,
        (SELECT DetailData FROM MasterDetails WHERE ID = life.BodyType) BodyTypeText
    FROM
        UsersInfo uinfo 
    LEFT JOIN 
        EducationAndOccupation edu on uinfo.ID = edu.UserID 
    LEFT JOIN 
        ReligionAndEthinicity rel on uinfo.ID = rel.UserID
    LEFT JOIN 
        HobbiesAndInterests hob on uinfo.ID = hob.UserID
    LEFT JOIN 
        LifestyleAndAttributes life on uinfo.ID = life.UserID
    LEFT JOIN 
        FamilyDetails fd on uinfo.ID = fd.UserID
    WHERE
        uinfo.ID = @id
END

标签： asp.net-mvcstored-proceduressql-server-2014-express

解决方案

您应该做的第一件事是通过 EXPLAIN 运行该语句以查看您的查询在做什么。

在 SSMS 中，确保您选择了数据库并打开一个新的查询窗口。将您的 SQL 粘贴到窗口中，然后选择查询 -> 显示估计的执行计划 (Ctrl + L)

查询计划显示在底部的窗口中。查看计划，它会告诉您事情在哪里花费时间（相对于批次的百分比）如果您不熟悉计划，请看这里...

http://www.simple-talk.com/sql/performance/execution-plan-basics/[^ ]

您还可以通过优化顾问运行查询，以查看是否推荐了任何索引。但是，如果表 titlefee 是记录最多的表，则需要考虑对它执行的操作。

1) 您正在通过字段 dtIssue 限制此表的结果。该字段是索引的一部分，还是导致表扫描（查看您的执行计划）

2）您正在按字段 curAmount 进行汇总 - 这可能会被索引。

使用执行计划 + 查询调优顾问，您应该能够索引您的表以获得最佳性能。

但是，通过引入索引，您必须权衡表的更新频率\更新数据的过程（例如用户应用程序？通宵 ETL？）。在 READ 与 WRITE 性能之间取得正确的平衡可能是一门黑暗的艺术:)

参考：How-to-make-fast-Stored-Procedure

推荐阅读