Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2005
    Posts
    85

    Unanswered: INNER JOIN query - performance

    Hi,

    Please let me know the way to increase the performance of the below query :

    SELECT DISTINCT a.* FROM a INNER JOIN #temp1 b on (a.col1 = b.col1 OR a.col1 IS NULL) INNER JOIN #temp2 c on (a.col2 = c.col1 OR a.col2 IS NULL)

    Here, there are no indexes/pk on the columns in any table. But I am sure that the table #temp1 and #temp2 has distinct/unique values in columns col1 used here. The table 'a' has redandant values in its column used here.

    Should I create pk on the columns for #temp1 and #temp2 used here. Is that enough ? Or should I also create index on the columns of the table 'a' used here.

    Also please let me know is there anyother way to increase the performance of the query.

    Please advice,
    MiraJ

  2. #2
    Join Date
    Jun 2003
    Posts
    269
    try creating a index on a.col1 and another one on a.col2
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Jun 2005
    Posts
    85
    Should I create PK on columns in #temp1 and #temp2 ?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by MiraJasmin
    Please let me know the way to increase the performance of the below query
    I believe your query could be rewritten as:
    Code:
    SELECT DISTINCT * 
    FROM a
    This would be much faster
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    I believe your query could be rewritten as
    Oops - no it couldn't fool
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try creating indexes on your temp tables.

    If the optimizer isn't using your indexes, you could possibly speed this up by create separate queries without OR clauses and UNIONing the results together. But since you have two boolean conditions, you would need four subqueries to cover it.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by MiraJasmin
    Should I create PK on columns in #temp1 and #temp2 ?
    If these temp tables are only being referenced once, then the time required to create the indexes (clustered or non-clustered) may offset any benefit you get in your SELECT statement. But if you are referencing the temp tables more than once then it is very likely worth the trade-off.
    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
  •