Results 1 to 5 of 5
  1. #1
    Join Date
    May 2006
    Posts
    38

    Unanswered: Joins Vs Where clause - Performance Query

    Hi There !!

    To finetune performance for some of our queries,

    I have come across suggestions to use

    - JOINS instead of WHERE clause wherever possible
    - and avoid using Aliases

    Although Avoiding aliases looks reasonable I am yet to be convinced about JOINS replacing the WHERE CLAUSE . What is the experts take on this one ????

    Also,

    I checked the estimated plan in SQL server by running the following 2 queries into my Query Designer

    tables : dba ( empid, empname )
    project ( project_empid references dba.empid, project_name )


    USING A WHERE CLAUSE and Alias
    -------------------------
    select a.emp_name from dbo.dba a, dbo.project b
    where
    a.empid =b.project_emp
    and b.project_name is not null

    USING A JOIN
    -----------------
    select emp_name from dbo.dba
    as
    a inner JOIN dbo.project
    ON empid = dbo.project.project_emp
    AND dbo.project.project_name is not NULL

    ******

    I find from the Estimated plan that both the queries give the same amount of cost ( I/O, CPU, et all )

    Any comments/ suggestions.

    Thanks,

    Have a great time
    -Ranjit.

    -------------------------------------
    It pays to be honest to your DBA

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    my experience is that 99% of the time, the optimizer is smart enough to generate the same plan regardless of whether you use the ansi join syntax or not. I prefer the ansi syntax just for purity's sake however.

    If you haven't already, you should measure first (using profiler) to find where the bottlenecks are. Only after you have measured can you begin to address perf issues.

    Finally, I am fairly certain that changing from one join syntax to another is not going to fix any perf issues you may have.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jezemine
    Finally, I am fairly certain that changing from one join syntax to another is not going to fix any perf issues you may have.
    Agreed - ANSI syntax is merely convention (although of course you can do more than an inner join with ANSI).

    Quote Originally Posted by RanjitSHans
    and avoid using Aliases
    Nope again - this is just a convention too. Some people think aliases make code easier to read, blindman does not.

    I don't know where you stand in performance tuning experience but everyone of any level can find something of use here:
    http://www.sql-server-performance.co...erformance.asp

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    avoiding aliases is not "reasonable"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by pootle flump
    Some people think aliases make code easier to read, blindman does not.
    My reputation preceeds me.
    But even I don't claim the aliases hurt performance.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •