SQL Server Performance

How to optimize this JOIN?

Discussion in 'General Developer Questions' started by Bredsox, Oct 25, 2005.

  1. Bredsox New Member

    Hi all,
    I have a complex(messy)join condition in this select query for a stored procedure which is taking forever to execute. How can I rewrite this query to get the same resultset without using many likes and wild characters? Any help will be greatly appreciated. Thanks.

    Dan
  2. Luis Martin Moderator

    I'm not developer but, did you try execution plan to see if any indexes are neccesary?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  3. Bredsox New Member

    Yes, I tried estimated Exec Plan and it shows 48% cost on bookmark lookup and 7% parallelism/Gather streams and 11% parallelism/repartition stream. All the table refering to this query have indeces created already. I don't know how can I get rid of these conditions without getting different resultset. Hopefully, someone will give me an idea on this. Thanks for your reply.

    Dan
  4. mmarovic Active Member

    How many rows do you have in each table involved?
  5. Ray D New Member

    And what indexes do you have?

    WBR, Vlad A. Scherbinin
  6. Bredsox New Member

    Sorry for the late response.

    Each table has appromately 5 to 8 million rows but I am returning only 1001 rows to display. And they have Clusterred(On most PKs) and Non Clusterred and Composite indexes on some of the columns. I don't know If I need to create new indices on some of the tables.

    Dan

  7. Ray D New Member

    Show us tables' structures, indexes and execution plan.

    WBR, Vlad A. Scherbinin
  8. ranjitjain New Member

    quote:Originally posted by Bredsox
    I don't know If I need to create new indices on some of the tables.
    Dan

    From one of SSP Article:

    SELECT name
    FROM sysindexes
    WHERE (name LIKE '%_WA_Sys%')

    This query will return all of the columns from the tables in your database that have column statistics on them that have been added automatically by the Query Optimizer. This information provide you a starting point from which to explore whether or not adding indexes to these columns will be useful or not.
  9. Adriaan New Member

    Like you say, it IS a messy query.

    Do you really need that DISTINCT? Not sure that your TOP clause can speed it up - doesn't DISTINCT require that the complete data must be resolved?

    I get the impression that you have a few tables in the FROM clause that you only use for filtering: they're not mentioned in the SELECT clause, only in the WHERE. They may also be causing the duplicates that you are suppressing with the DISTINCT clause ...

    If you can join the main tables without using those extra tables, then change the JOINed extra tables and criteria into subqueries in the WHERE clause - preferably as part of EXISTS or NOT EXISTS clauses.
  10. Bredsox New Member

    Adrian,
    I am not sure if distinct will help to filter only top 1001 rows OR it goes to all the records in table? But, certainly i will try using your idea to use subquery. Can I import all these data from realtor_roster rr(this is the main table) to temp table and join it with other tables? Does it help on query execution time or it hurts? Thanks everyone for your time and still waiting to get to the concrete point here.

    ---Never mind about temp table, it takes longer.

    Dan
  11. Adriaan New Member

    Dan,

    The main point was that you have the DISTINCT keyword. Moving the additional tables to subqueries may already remove duplicate rows in the resultset, and after that you would no longer need DISTINCT - which is a known slower-downer.

    And to answer my own question: "Doesn't DISTINCT require that the complete data must be resolved?" - no, it doesn't.

    The reason for poor performance with a temp table may well be that your temp table definition doesn't include a PK and/or appropriate indexes.
  12. kpayne New Member

    quote:Originally posted by Bredsox

    Yes, I tried estimated Exec Plan and it shows 48% cost on bookmark lookup and 7% parallelism/Gather streams and 11% parallelism/repartition stream. All the table refering to this query have indeces created already. I don't know how can I get rid of these conditions without getting different resultset. Hopefully, someone will give me an idea on this. Thanks for your reply.

    Dan

    Dan,

    First, update all of the statistics.
    Next, run the query and look at the Actual Execution Plan.

    If it still has the bookmark lookup, you will have to create a non-clustered index that contains the columns that are being gathered from the bookmark lookup. This will eliminate the bookmark lookup in your query plan and give you a large boost in performance.

    If you want to "clean up" the query, think about creating some indexed views - especially for the joins between PK & FK without any other criteria.

    Keith Payne
    Technical Marketing Solutions
    www.tms-us.com
  13. Bredsox New Member

    Thanks Keith and everyone for your time. Actually I did try updating the statistics on all the referencing tables but still didn't get difference in performance. FYI... there is a composite index on realtor_roster(main table) for nine(9) columns inlcuded. I am thinking to drop this index and create a new index on some of these columns. Any suggestions? Please keep posting. Thanks again.


    Dan
  14. Luis Martin Moderator

    What kind of update statistics do you run?
    Full?.

    Also did you defrag indexes?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  15. Bredsox New Member

    I ran full Update statistics on all the tables(not specific on index name) that has indeces but didn't do the indexdefrag. Will that help? How about creating some non clusterred indeces on these columns like RealtorName and OfficeName (in realtor_roster table)since it is being used many times in the query? Thanks for the reply.


    Dan
  16. Luis Martin Moderator

    One of the reason to loose performance, in general, is high fragmentation. As part of maintenance plan you should defrag indexes at least once a week.

    Specific with your problem I agree about non clustered.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  17. Bredsox New Member

    quote:Originally posted by Bredsox

    Hi all,
    I have a complex(messy)join condition in this select query for a stored procedure which is taking forever to execute. How can I rewrite this query to get the same resultset without using many likes and wild characters? Any help will be greatly appreciated. Thanks.

    Dan

    Removed sample query for specific reason. Thanks

Share This Page