Click here to Skip to main content
15,887,262 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SQL
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
Posted
Comments
Naveen.Sanagasetti 7-Oct-15 6:57am    
Everything is Ok.

Have you check all conditions.?

Keep in mind while join the tables first you need to call master then child, because master contains few records but child contains lakh of records.
CHill60 7-Oct-15 9:48am    
The OP has used inner joins so order of tables would only be for reading clarity not performance
[no name] 7-Oct-15 10:48am    
hi naveen thanks for your suggestions. one ,more thing is if i created indexes on joining columns and primary key columns the performance will be improved or not?because indexes will increase performance for large data table , not for small data tables.
[no name] 7-Oct-15 7:33am    
And don't Forget: In MSQL defining a foreign key relation does not means MSSQL creates an index for the foreign key column. A lot of time this is the reason for poor performance while using JOIN.
CHill60 7-Oct-15 9:49am    
Good point!

1 solution

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.
 
Share this answer
 
v2
Comments
CHill60 7-Oct-15 9:47am    
Your first link was broken - I've fixed it.
[no name] 7-Oct-15 10:33am    
Thanks a lot..

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900