SQL Server Performance

T-SQL Performance

Discussion in 'Getting Started' started by gayatri, Dec 7, 2007.

  1. gayatri New Member

    Could you tell what is the best way compare which one of following statements is the most efficient, what would you recommend?
    I am trying to count number of cities in a region. It is not easy to compare using the execution plan. Statement 1
    Select
    RegionCode, RegionName,
    (Select Count(City.CityCode)from
    City where City.RegionCode= REgion.RegionCode)from
    Region
    Statement 2Select Region.RegionCode, Region.RegionName,
    Select Count(City.CityCode) as citycountFROM
    Region INNER JOINCity
    ON Region.RegionCode= City.RegionCodeGROUP
    BY Region.RegionCode, Region.RegionName
  2. Madhivanan Moderator

    I think it depends on the data. In General I prefer statement 2 which would be faster for large number of data. Set the execution plan and see
  3. Madhivanan Moderator

    Also trySelect
    Region.RegionCode, Region.RegionName, City.citycountFROM
    Region INNER JOIN
    (select
    regioncode,count(*) as citycount from city group by regioncode)
    as City ON
    Region.RegionCode= City.RegionCode
  4. Adriaan New Member

    In general, a quick comparison can be made by putting the two variations into a single query window, and to ask for the execution plan.
    You will get "Cost relative to the batch" for each query, and the version with the lowest percentage is very likely to be the best performing one.

Share This Page