Professional Web Applications Themes

Where to put a WHERE clause - Microsoft SQL / MS SQL Server

I want to create a SQL Server 2K stored procedure to return a recordset to my program, but am not sure what method to use, or how to determine which method is more efficient. I will pass the SP a single paramter (ExID). I'm trying to determine what is more efficient: 1) Creating a join between two tables, then using a WHERE clause to restrict the result set, or 2) Creating a subquery that restricts the data from one table, then join that to the second table. SELECT UQ.*, UR.Response, UR.Comment FROM UserQuestionnaires UQ INNER JOIN UserResponses UR ON UQ.QuestionnaireID ...

  1. #1

    Default Where to put a WHERE clause

    I want to create a SQL Server 2K stored procedure to return a
    recordset to my program, but am not sure what method to use, or how to
    determine which method is more efficient. I will pass the SP a single
    paramter (ExID).

    I'm trying to determine what is more efficient:
    1) Creating a join between two tables, then using a WHERE clause to
    restrict the result set, or
    2) Creating a subquery that restricts the data from one table, then
    join that to the second table.

    SELECT UQ.*, UR.Response, UR.Comment
    FROM UserQuestionnaires UQ
    INNER JOIN UserResponses UR
    ON UQ.QuestionnaireID = UR.QuestionnaireID
    WHERE UR.ExID = ExID

    versus

    SELECT UQ.*, UR.Response, UR.Comment
    FROM (SELECT UserQuestionnaire.*
    FROM UserQuestionnaire
    WHERE ExID = ExID) UQ
    INNER JOIN UserResponse UR
    ON UQ.QuestionnaireID =UR.QuestionnaireID

    --
    Dale Fye



    Dale Guest

  2. #2

    Default Re: Where to put a WHERE clause

    > I'm trying to determine what is more efficient:

    You can do that by running the two queries in Query yzer, with show
    execution plan, show server statistics, etc.


    Aaron Guest

  3. #3

    Default Re: Where to put a WHERE clause

    I find this one easier to read:

    SELECT UQ.*, UR.Response, UR.Comment
    FROM UserQuestionnaires UQ
    INNER JOIN UserResponses UR
    ON UQ.QuestionnaireID = UR.QuestionnaireID
    WHERE UR.ExID = ExID

    In order to determine which is more efficient, you can look at the query plans for each. You could also try executing each to determine if there is a difference in performance.

    --
    Keith, SQL Server MVP

    "Dale Fye" <dale.fye> wrote in message news:phx.gbl... 
    Keith Guest

Similar Threads

  1. #40218 [NEW]: Add Else clause to while
    By jbailey at raspberryginger dot com in forum PHP Bugs
    Replies: 2
    Last Post: January 24th, 03:08 PM
  2. Using IN in a WHERE clause
    By drmaves in forum Coldfusion Database Access
    Replies: 15
    Last Post: September 5th, 05:05 PM
  3. CF MX 6 WHERE Clause
    By Jeremy5431 in forum Macromedia ColdFusion
    Replies: 13
    Last Post: May 2nd, 02:26 PM
  4. where clause with date (asp.net - vb)
    By Yannick in forum ASP Database
    Replies: 3
    Last Post: June 23rd, 05:40 PM
  5. Using COUNT() In WHERE Clause
    By MDW in forum ASP Database
    Replies: 1
    Last Post: February 24th, 02:51 PM

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