Professional Web Applications Themes

JOIN clause slows things down? - Microsoft SQL / MS SQL Server

I have a co-worker that is a supposed to be a long-time SQL guru. He claims that when you use the JOIN keyword, that this causes the query to be executed slowly. Instead, he advocates using the (old) style where the join conditions are specified in the where clause, with the old =, *= and =* notations. He was mentioning things like loading up all indexes on tables in memory, exceeding a certain amount of memory, paging of data, SQL getting mixed up with memory pointers, not returning all the rows it's supposed to, etc. Another thing that he is ...

  1. #1

    Default JOIN clause slows things down?

    I have a co-worker that is a supposed to be a long-time SQL guru.
    He claims that when you use the JOIN keyword, that this causes the query to
    be executed slowly.
    Instead, he advocates using the (old) style where the join conditions are
    specified in the where clause, with the old =, *= and =* notations.

    He was mentioning things like loading up all indexes on tables in memory,
    exceeding a certain amount of memory, paging of data, SQL getting mixed up
    with memory pointers, not returning all the rows it's supposed to, etc.


    Another thing that he is advocating is sending a comma-delimited string of
    values to a sp, where these are pd out and put into a temp table,
    instead of using a normalized flat table. I have two questions about this
    approach:
    1. Is this really going to make a difference in performance?
    2. Am I correct in that this violates 1st normal form of data normalization,
    when dealing with atomic units?

    He is claiming, with these code modifications, a performance increase of
    200% - 800%.
    Comments please?

    Thanks,
    Wayne


    Wayne Guest

  2. #2

    Default Re: JOIN clause slows things down?

    There is not difference in performance between the old style and the ANSI-92 join syntaxes. You can avoid the comma-delimited string by using XML:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01c5.asp

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Wayne Sheffield" <com> wrote in message news:phx.gbl...
    I have a co-worker that is a supposed to be a long-time SQL guru.
    He claims that when you use the JOIN keyword, that this causes the query to
    be executed slowly.
    Instead, he advocates using the (old) style where the join conditions are
    specified in the where clause, with the old =, *= and =* notations.

    He was mentioning things like loading up all indexes on tables in memory,
    exceeding a certain amount of memory, paging of data, SQL getting mixed up
    with memory pointers, not returning all the rows it's supposed to, etc.


    Another thing that he is advocating is sending a comma-delimited string of
    values to a sp, where these are pd out and put into a temp table,
    instead of using a normalized flat table. I have two questions about this
    approach:
    1. Is this really going to make a difference in performance?
    2. Am I correct in that this violates 1st normal form of data normalization,
    when dealing with atomic units?

    He is claiming, with these code modifications, a performance increase of
    200% - 800%.
    Comments please?

    Thanks,
    Wayne



    Tom Guest

  3. #3

    Default Re: JOIN clause slows things down?

    Hi Wayne,

    I think your colleague disqualifies himself as a SQL guru with the statement
    that the old style joins are faster than using the JOIN keyword. As Tom
    (who is a real SQL guru) pointed out, they are identical in performance. The
    difference is that there are things possible with OUTER JOIN that are not
    possible with *= and =*.

    I have basically not seen SQL not return the rows it was supposed to. I have
    seen it not return the rows I expected, but that was always due to a fault
    in my code :-) If SQL Server mixed up it's memory pointers on a regular
    basis Microsoft wouldn't sell for more than $1 billion a year of it.

    There is always a simple solution to discussions about performance: get some
    test data, write your queries, run them and check them with SQL Profiler and
    the query execution plans.

    "Wayne Sheffield" <com> wrote in message
    news:phx.gbl... 
    to 
    normalization, 


    Jacco Guest

Similar Threads

  1. Replies: 2
    Last Post: September 18th, 09:59 PM
  2. what join to emulate Not In clause?
    By Vlad in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: August 13th, 04:05 PM
  3. outer join, cross join, union? DDL/sample data incl.
    By Kevin in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 5th, 08:01 PM
  4. Rewrite Query using Inner Join, Left Join e.t.c
    By Vishal in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 23rd, 08:02 AM
  5. help on join in from clause
    By Jen in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 3rd, 06:16 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139