这是我的标量用户定义函数，需要将相同的代码更改为子查询或其他形式 [英] this is my scalar user defined function , need to change same code into a sub query or other forms

问题描述

CREATE FUNCTION [dbo].[getVesselposition]
 (@VesselPositionId varchar(10))

RETURNS varchar(50)
AS
begin
declare @VesselPosition varchar(50)

select @VesselPosition = VesselPosition
FROM  dbo.PMS_TBL_EUIPMENT_VESSELPOSITION_MASTER  where VesselPositionId =@VesselPositionId

if @VesselPosition is null

 select @VesselPosition=''

return  @VesselPosition
end

推荐答案

加入相应的表格。例如。避免调用 GetVesselPosition

Join to the appropriate tables. E.g. to avoid calling GetVesselPosition

SELECT ROW_NUMBER()OVER (ORDER BY M.EquipmentId) AS Row,
 dbo.getEquipmentName(M.EquipmentId)EquipmentName,
 MaintananceId,
 ISNULL(VP.VesselPosition,'') AS PositionInVessal,
 ClassCode,
 MaintananceName,
 dbo.getMaintananceType(MaintanaceType)MaintanaceType,
 dbo.getFrequecyType(FrequencyType)FrequencyType,
 Frequency,
 CONVERT(VARCHAR(10), M.LastDoneDate, 105)LastDoneDate,
 LastDoneRunningHours,CONVERT(VARCHAR(10), NextDueDate, 105) NextDueDate,
 NextDueRunningHours, dbo.getResposibility(Responsibility)Responsibility,
 C.Remarks,
 C.JobCompletedAt,
 C.NameOfPort,
 C.JobCarriedOutBy,
 C.NameOfWorkShop,
 C.MaintanaceStatus
 FROM dbo.PMS_TBL_MAINTANANCE_MASTER M
 INNER JOIN PMS_TBL_MAINTANANCE_COMPLETION C ON C.MaintenanceId=M.MaintananceId
 LEFT OUTER JOIN PMS_TBL_EQUIPMENT_MASTER E ON M.EquipmentId=convert(varchar(36),E.UniqueId)
 LEFT OUTER JOIN PMS_TBL_SUB_EQUIPMENT_MASTER S ON M.EquipmentId=convert(varchar(36),S.UniqueId)
 INNER JOIN PMS_TBL_EUIPMENT_VESSELPOSITION_MASTER  VP where VP.VesselPositionId = E.PositionInVessal

为

- getMaintananceType

- getFrequecyType

- getEquipmentName

- getResponsibility



使用这些连接运行查询速度慢的原因可能是因为您要将每个连接的Id转换为varchar - 请考虑更改模式以使所有ID为int或bigint。



您是否实际从PMS_TBL_SUB_EQUIPMENT_MASTER检索数据，因为您没有在查询中的任何位置使用S别名。

Do the same for
- getMaintananceType
- getFrequecyType
- getEquipmentName
- getResponsibility

Potentially ne of the reasons you are having slow running queries with these joins is because you are converting Id's to varchar for each join - consider changing your schema so that ALL ids are int or bigint.

Are you actually retrieving data from PMS_TBL_SUB_EQUIPMENT_MASTER as you haven't use the S alias anywhere in your query.

这篇关于这是我的标量用户定义函数，需要将相同的代码更改为子查询或其他形式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！