SubQuery Vs UDF | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SubQuery Vs UDF

Can Somebody tell me which of this two query methods have better performance: select
Id,
(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
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |