SQL Server Performance

SubQuery Vs UDF

Discussion in 'T-SQL Performance Tuning for Developers' started by petrozzisergio, Aug 12, 2003.

  1. petrozzisergio New Member

    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


  2. bambola New Member

    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.
  3. joechang New Member

    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

  4. petrozzisergio New Member

    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
  5. rushmada New Member

    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

Share This Page