Results 1 to 5 of 5
  1. #1
    Join Date
    May 2008
    Posts
    3

    Unanswered: SQL Server short-circuits WHERE condition evaluation

    Hi I am trying to create stored proc and having some performance issue.

    Here is the requirement,

    Stored Proc parameters A,B
    A is required.
    B is optional. The default value for B is 0.

    Here is the code inside stored proc:

    IF (B = 0)
    SET B = 1


    SELECT * FROM TABLE1
    INNER JOIN TABLE2 ON TABLE1.Column1 = TABLE2.Column1
    WHERE A= 2008
    AND (table2.ColumnB = B or 1 = B)

    If I am passing B as 20 I am having performance issue to pull the data.
    If I replace B with any other number the query is working fine.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    What is the datatype of table2ColumnB? Is there an index on it?

    What is the comparative distribution of the value 20 with respect to the other values? Is the value 20 represented significantly more than the other values?

  3. #3
    Join Date
    May 2008
    Posts
    3
    table2ColumnB datatype is numeric, yes I have index on that column.
    table2.ColumnB it can be 20,30,40, 1001,10000,100001,1000001 etc, upto numeric 12.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    How many rows in the table, and what percentage of these rows have columnB = 20?

  5. #5
    Join Date
    May 2008
    Posts
    3
    70,000 Rows 10% Of The Rows Has 20.

Posting Permissions

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