首页新闻找找看学习计划

EF 调用存储过程的问题

0
悬赏园豆:20 [已关闭问题] 关闭于 2017-10-24 09:50

问题:EF 调用存储过程,超时的问题,在真实的项目中时间间隔大概是三四天将会出现一次,调用超时的问题;本人的临时解决方案,就是在数据库找到存储过程右键执行修改;这样就OK了。

错误提示:

执行的存储过程:

ALTER PROCEDURE [dbo].[P_GetSceneryList]
 (--排序参数
@orderby INT,
--搜索关键字
@key nvarchar(10),
--地区编号
@areaId INT,
--是否选择主题
@isChooseTheme BIT,
--主题编号列表
 @themeIds nvarchar(500), -- 1,2,3,4,
--今日可订
 @todayCanBook BIT,
 --当前时间
@now datetime,
--今天编号
@dayID INT,
 --经纬度
@lng nvarchar(50),
@lat nvarchar(50),
@pageIndex int,
@pageSize INT,
@totalCount INT OUTPUT

)
 AS

 BEGIN

 declare @themeTable table(themeid int)

  INSERT into @themeTable
  SELECT Value from dbo.fn_SplitString(@themeIds, ',', 1)

   ;with theme as(

     select * from ( select *, ROW_NUMBER() over(partition by EtSceneryID ORDER BY EtSceneryID) as rowNum from dbo.[_Et_SceneryTheme] ) ranked where ranked.rowNum = 1

   )
   , imag AS (
    select * from ( select *, ROW_NUMBER() over(partition by EtSceneryID ORDER BY EtSceneryID) as rowNum from dbo._Et_SceneryImage ) ranked where ranked.rowNum = 1
   )

   , cte as (

    --排序条件 见EnumHelper.ScenicOrderBy枚举
    -- 1.智能排序 2.距离由近至远 3.销量由高至低排序 4.景点级别由高至低排序
    select row_number() over( order BY     
        (case @orderby when 1 then A.LocationPoint.STDistance(geography::STGeomFromText('POINT('+@lng+' '+@lat+')', 4326)) end ) asc,   
        (case @orderby when 1 then A.Grade end ) desc,
        (case @orderby when 1 then A.SalesVolume end) desc,
        
        (case @orderby when 2 then A.LocationPoint.STDistance(geography::STGeomFromText('POINT('+@lng+' '+@lat+')', 4326)) end ) asc,
        --(case @orderby when 2 then A.Grade end) desc,
        --(case @orderby when 2 then A.Name end) asc,

        (case @orderby when 3 then A.SalesVolume end) desc,
        (case @orderby when 4 then A.Grade  end) desc,
    
        (case @orderby when 0 then A.LocationPoint.STDistance(geography::STGeomFromText('POINT('+@lng+' '+@lat+')', 4326)) end ) asc
     ) as px,
     A.LocationPoint.STDistance(geography::STGeomFromText('POINT('+@lng+' '+@lat+')', 4326)) as dis,
     a.EtSceneryID,a.SceneryID,a.Address,a.Name,a.Grade,a.LocationPoint,
     c.AreaID,b.PartnerAreaName,
     e.ImagePath,e.ImageRemark,
     n.PartnerThemeName,n.ThemeID,
     a.Longitude,
     a.Latitude,
     s.TcAmountPrice ,
     s.MarketPrice,     
     a.PinYin,
     ISNULL(SS.PriceCalendarID,0) AS TodayBook

      from [_Et_Scenery] as A

        inner join dbo.[_Et_PartnerArea] as B
            on a.ProvinceAreaID = B.PartnerAreaID
        inner join dbo.[_Et_PartnerArea] as C
            on a.CityAreaID = C.PartnerAreaID
        inner join dbo.[_Dim_Area] as D
            on D.AreaID = C.AreaID

        LEFT JOIN imag AS E
            ON A.EtSceneryID = E.EtSceneryID

        left join theme as M
            on M.EtSceneryID = A.EtSceneryID
        left join dbo.[_Et_PartnerTheme] N
            on N.PartnerThemeID = M.PartnerThemeID
        left join dbo.[_Dim_Theme] O
            on O.ThemeID = N.ThemeID
      
        INNER JOIN (
        SELECT b.EtSceneryID, MIN(b.TcAmountPrice) TcAmountPrice ,MIN(b.MarketPrice) MarketPrice FROM  [dbo].[_Et_SceneryTicket] b WHERE b.IsOffline = 0
        GROUP BY b.EtSceneryID ) AS  S
          ON A.EtSceneryID = S.EtSceneryID
         
         left join (
            select ROW_NUMBER() over(partition by b.SceneryID order by b.SceneryID) pm, b.SceneryID, c.PriceCalendarID, c.DayID from [dbo].[_Et_SceneryTicket] as b INNER join dbo.[_Et_SceneryTicket_Calendar] as c on b.SceneryTicketID=c.SceneryTicketID
                WHERE b.IsOffline = 0 and c.DayID=@dayID AND c.SceneryTicketID = b.SceneryTicketID) as SS
        ON A.SceneryID=SS.SceneryID and SS.pm = 1

    where

     a.IsAudit = 1 AND  
    
    ( @key is null or (A.Name like'%'+@key+'%' OR A.PinYin LIKE REPLACE('%'+@key+'%',' ','') or B.PartnerAreaName like '%'+@key+'%' or C.PartnerAreaName like '%'+@key+'%' ) ) and
    ( @areaId is null or ( D.AreaID = @areaId ) )  and
    ( @isChooseTheme is null or @isChooseTheme = 0 or ( @isChooseTheme = 1 and  N.ThemeID in (select themeid from @themeTable)  ) )
 
)

select * from cte where px > @pageIndex * @pageSize and px <= (@pageIndex + 1) * @pageSize

set @totalCount = (

      select count(*)
      from [_Et_Scenery] as A

        inner join dbo.[_Et_PartnerArea] as B
            on a.ProvinceAreaID = B.PartnerAreaID
        inner join dbo.[_Et_PartnerArea] as C
            on a.CityAreaID = C.PartnerAreaID
        inner join dbo.[_Dim_Area] as D
            on D.AreaID = C.AreaID

        left join dbo.[_Et_SceneryTheme] as M
            on M.EtSceneryID = A.EtSceneryID
        left join dbo.[_Et_PartnerTheme] N
            on N.PartnerThemeID = M.PartnerThemeID
        left join dbo.[_Dim_Theme] O
            on O.ThemeID = N.ThemeID

    where

    a.IsAudit = 1 and

    --or A.AliasName like'%'+@key+'%'
    ( @key is null or ( A.Name like'%'+@key+'%'  or B.PartnerAreaName like '%'+@key+'%' or C.PartnerAreaName like '%'+@key+'%' ) ) and
    ( @areaId is null or ( D.AreaID = @areaId ) )  and
    ( @isChooseTheme is null or @isChooseTheme = 0 or ( @isChooseTheme = 1 and  N.ThemeID in (select themeid from @themeTable)  ) )
)


select @totalCount
 END
 

 

IT小伙儿的主页 IT小伙儿 | 初学一级 | 园豆:11
提问于:2017-08-29 16:21
< >
分享
所有回答(2)
0

肯定不是EF的问题了,你这么多join,干嘛不用临时表。

数据酷软件 | 园豆:128 (初学一级) | 2017-08-29 16:25

指教一下呗

支持(0) 反对(0) IT小伙儿 | 园豆:11 (初学一级) | 2017-08-29 16:26
0

很简单,超时。

1、升级硬件,这是最简单的操作,只要有钱就行。

2、优化查询,这是稍微需要用点脑子的。

3、优化设计,这个比较麻烦,但是是长远之策。

爱编程的大叔 | 园豆:30471 (高人七级) | 2017-08-30 09:28
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册