SQL Server Performance Forum – Threads Archive
SubQuery Vs UDF
Can Somebody tell me which of this two query methods have better performance: selectId,
(select Name from TableOne where IdName = Id)
From TableTwo Or Select
Id,
dbo.SelectNameFromTableOneFunction(Id) — (same query of the subquery above)
From TableTwo as a example this is the query that generate a performance problem: Select
A.CaseID,
dbo.BataFormatDateFN(IsNull(A.CaseDate,A.ReferralDate),’W’) as CaseDates,
isnull(dbo.BataGetCaseClientDescFN(A.CaseID,’W’),’Other Case Type’) CaseClientDesc,
dbo.BataGetStaffDescFN(A.CasePersonAsgID),
dbo.BataGetCaseStatusNameFN(A.CaseStatusID),
dbo.BataGetCaseTypeNameFN(A.CaseID) As CaseTypeName,
dbo.BataGetCasePrimaryClientIdFN(A.CaseID) As PrimaryClientId,
dbo.BataGetContactReferralIdFN(dbo.BataGetCasePrimaryClientIdFNA.CaseID),
dbo.BataGetCaseTypeNameFN(A.CaseID)),
B.TimeUnit,
A.TimeLogId,
A.CaseID CCaseId,
dbo.BataCheckUserRecordRightFN(@UserId,@RightName,A.OwnerId)
From Cases A, TimeLogs B
Where B.Id = A.TimeLogId
And IsNull(Convert(Varchar,A.CaseStatusID),’%’) Like IsNull(Convert(Varchar,@StatusId),’%’)
And IsNull(Convert(Varchar,dbo.BataGetCaseDistrictFN(A.CaseId)),’%’) Like IsNull(Convert(Varchar,@DistrictId),’%’)
And IsNull(Convert(Varchar,A.CasePersonAsgID),’%’) Like IsNull(Convert(Varchar,@AssignedTo),’%’)
Order by CaseClientDesc, A.CaseDate Desc
Thank You very much
Why don’t you join the third table (what you call TableOne) to the query here, instead of running a function for each row?
I note there is another function. If it is doing something similar, than the same goes for that function. Bambola.
I would always try to write a query as a regular join, without either subqueries or functions whenever possible,
in your example, there is no apparent reason for the subquery, unless t1 : t2 is a one-to-many relation and you want a 1-1 select t2.Id, t1.Name
From TableTwo t2
INNER JOIN TableOne t1 ON t1.IdName = t2.Id
the reason for use UDf is that in the real procedure there are more than 10 tables involved and tryng to join those tables it’s seems very dificult to me check the code below: ############################################################################### Select
A.CaseID,
dbo.BataFormatDateFN(IsNull(A.CaseDate,A.ReferralDate),’W’) as CaseDates,
isnull(dbo.BataGetCaseClientDescFN(A.CaseID,’W’),’Other Case Type’) CaseClientDesc,
dbo.BataGetStaffDescFN(A.CasePersonAsgID),
dbo.BataGetCaseStatusNameFN(A.CaseStatusID),
dbo.BataGetCaseTypeNameFN(A.CaseID) As CaseTypeName,
dbo.BataGetCasePrimaryClientIdFN(A.CaseID) As PrimaryClientId,
dbo.BataGetContactReferralIdFN(dbo.BataGetCasePrimaryClientIdFNA.CaseID),
dbo.BataGetCaseTypeNameFN(A.CaseID)),
B.TimeUnit,
A.TimeLogId,
A.CaseID CCaseId,
dbo.BataCheckUserRecordRightFN(@UserId,@RightName,A.OwnerId)
From Cases A, TimeLogs B
Where B.Id = A.TimeLogId
And IsNull(Convert(Varchar,A.CaseStatusID),’%’) Like IsNull(Convert(Varchar,@StatusId),’%’)
And IsNull(Convert(Varchar,dbo.BataGetCaseDistrictFN(A.CaseId)),’%’) Like IsNull(Convert(Varchar,@DistrictId),’%’)
And IsNull(Convert(Varchar,A.CasePersonAsgID),’%’) Like IsNull(Convert(Varchar,@AssignedTo),’%’)
Order by CaseClientDesc, A.CaseDate Desc ############################################################################### every function in the select retrive data fror two or three tables Joined within
the function
thank you guys
If u are using a group by clause in the subquery then subqueries are preferred
in terms of performance otherwise a JOIN is sufficient. Rushendra
]]>