以下是我的存储过程可以任何一个建议我改进执行时间 [英] the following is my stored procedure can any one suggest me the improvement of execution time

问题描述

CREATE PROC [dbo].[sp_GetallEqupments]  100
@SFBId int
as
begin          --V.VesselPosition as PositionInVessal,C.Capacity as Capacity,
select EquipmentId,EquipmentName,Make,Model,
Head,RPM,
--,Quantity,MakersAddress,Yard,HullNo,YearOfManufactured,CriticalEquipments,RunningHourBased,
--InheritRunningHoursfrom,NoOfUnits,E.CreatedBy,E.CreatedDate,
S.SubFunctionalBlockName,F.FunctionalBLockName,F.FunctionalBLockId
--,Sno
  from PMS_TBL_EQUIPMENT_MASTER E
inner join dbo.PMS_TBL_SUB_FUNCTIONAL_BLOCK_MASTER S on S.SubFunctionalBlockId=E.SFBId
inner join dbo.PMS_TBL_FUNCTIONAL_BLOCK_MASTER F on F.FunctionalBLockId=S.FBId

--inner join dbo.PMS_TBL_EQUIPMENT_CAPACITY_MASTER C on C.CapacityId=E.Capacity
--inner join PMS_TBL_EUIPMENT_VESSELPOSITION_MASTER V on V.VesselPositionId=E.PositionInVessal
where E.SFBId=@SFBId and E.IsActive=1 order by EquipmentId asc
end

推荐答案

您的查询看起来没问题。如果你想如此快速地执行，请遵循以下几点：



增加存储过程性能的最佳做法：

http://blog.sqlauthority.com/2010/02/16/sql- server-stored-procedure-optimization-tips-best-practices / [ ^ ]



使用SQL事件探查器捕获慢速运行的查询并重新调解它：



SQL Server Profiler一步一步：

https://www.simple-talk.com/sql/performance/how-to-identify-慢速运行查询-sql-profiler / [ ^ ]



其次，如果你要加入大表，那么创建一个非聚集索引列用于加入查询。

Your query looks like OK. If you want to execute so fast please follow points:

Best practice to increase Stored Procedure performance:
http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/[^]

Use SQL Profiler to catch your slow running queries and re-mediate it:

SQL Server Profiler Step by Step:
https://www.simple-talk.com/sql/performance/how-to-identify-slow-running-queries-with-sql-profiler/[^]

Secondly if you are joining with large table then create a non-clustered index which column is used in joining query.

这篇关于以下是我的存储过程可以任何一个建议我改进执行时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！