How to optimize this JOIN?

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

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

How many rows do you have in each table involved?

And what indexes do you have?

WBR, Vlad A. Scherbinin

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

Show us tables' structures, indexes and execution plan.

WBR, Vlad A. Scherbinin

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.

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.

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

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.

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

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

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

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