this is my scalar user defined function , need to change same code into a sub query or other forms

Question

1.00/5 (2 votes)

See more:

SQL

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

Posted 12-Oct-15 4:19am

Member 12037320

Add a Solution

Comments

ZurdoDev 12-Oct-15 10:25am

What is your question?

[no name] 12-Oct-15 10:28am

my question is , i want to use the same logic in the sub query or other forms for better performance ,because it is used in a joining of four tables which leads too much of time to execute...

ZurdoDev 12-Oct-15 10:30am

I only see 1 table. And sub query will not likely be faster than joining.

[no name] 12-Oct-15 10:32am

i was using the above function in the following query

SELECT ROW_NUMBER()OVER (ORDER BY M.EquipmentId) AS Row,
dbo.getEquipmentName(M.EquipmentId)EquipmentName,
MaintananceId,
dbo.getVesselposition(E.PositionInVessal)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)

so, please write the exact code which replace the function in the query i listed

ZurdoDev 12-Oct-15 10:44am

It's relatively easy. Do you understand SQL. Why are you stuck on this?

[no name] 12-Oct-15 10:45am

no because i dont want to use function there

ZurdoDev 12-Oct-15 10:51am

All the function does is take an ID and get a value from a table. So, to replace the function you just need to join to the same table using that ID and then reference the column that the function was using. It's rather easy, but if I just tell you how to do it you won't learn anything and this is pretty basic.

CHill60 12-Oct-15 10:37am

An obvious improvement here would be to have select @VesselPosition = ISNULL(VesselPosition, ''). This looks to be one of the functions you have been calling from your other "slow running" Stored Procedures. RyanDev is correct - just join to the table in your main select

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2015-10-12T04:45:00

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

SQL

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

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.