Question Performance issue - LEFT OUTER JOIN in query showing RIGHT OUTER JOIN in Execution

Question

Question Performance issue - LEFT OUTER JOIN in query showing RIGHT OUTER JOIN in Execution

Kari Suresh

Hall of Fame

Points: 3712
More actions
February 4, 2009 at 5:08 am

#383863

Can anybody suggest me regarding performance in following query.
SELECT DISTINCT temp_assoc_obj.object_skey AS project_skey,
prof_security.account_skey
FROM #TempAssociateObjects temp_assoc_obj
INNER JOIN dim_project_secure proj_sec
ON temp_assoc_obj.object_skey = proj_sec.project_skey
AND proj_sec.project_key > 0
INNER JOIN profile_security prof_security
ON temp_assoc_obj.assoc_object_skey = prof_security.profile_skey
LEFT OUTER JOIN project_security proj_security
ON prof_security.account_skey = proj_security.account_skey
AND temp_assoc_obj.object_skey = proj_security.project_skey
WHERE proj_security.account_skey IS NULL

#TempAssociateObjects, project_security and profile_security tables are having one crore of records each.

It is showing RIGHT OUTER JOIN in Execution Plan for the following LEFT OUTER JOIN

LEFT OUTER JOIN project_security proj_security
ON prof_security.account_skey = proj_security.account_skey
AND temp_assoc_obj.object_skey = proj_security.project_skey

And it is taking much time to execute.

And profile_security table has IDX_ProfSecurity_ProfSkey(non-unique,non-clustered) index also project_security has PK_ProjSecurity_Skeys(Clustered) index.

it is urgent. Suggestions please.

Suresh.

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Posting the DDL and the execution plans will be helpful and get you better responses. Here are some tips on how to post:

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 2

Please find attachment of execution plan.

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Grant Fritchey SSC Guru Points: 398716 More actions · Answer 3

Looking at the XML, the query is still showing up as a LEFT JOIN. This is straight out of the XML from the .sqlplan file:

LEFT OUTER JOINproject_securityproj_security &#xA

The optimizer swapped the order of the processing becuase the data coming out of project_security is smaller than the other data. That means the hash probe table is smaller. So the RIGHT join in the case is pointing towards the same data stream as the LEFT join you defined. No worries.

However, you are getting nothing but index and table scans. Are you sure you have the right indexes in place and you're using them appropriately or is it just because you're moving so much data that the indexes are not being used at all.But going from 26million rows down to 8000 in the result set is usually an indication of a problem.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 4

Hi Grant Fritchey,

"Are you sure you have the right indexes in place and you're using them appropriately or is it just because you're moving so much data that the indexes are not being used at all."

I am not sure about that actually.

Is it possible to increase the performance for this query.

I am beginner to the SQL. Thanks for reply.

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Grant Fritchey SSC Guru Points: 398716 More actions · Answer 5

The first question I would ask is, do you really need 8000+ rows? You don't really have anything filtering the data down to human readable size. I'd push back on the requirements a bit to start.

After that, you want to look at the index or table scans to see if a different index would help (it might not). For example, you're getting a scan on dim_project_secure's clustered index. It's matching on the key value and has a predicate where project_key is greater than zero. How many zero values do you have? How many non-zero? It might be possible to create an index on the field and see a huge increase in performance, depending on how selective the data within the field is.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 6

Hi Grant Fritchey,

The first question I would ask is, do you really need 8000+ rows?

Yes, I want all these 8000 rows.

For example, you're getting a scan on dim_project_secure's clustered index. It's matching on the key value and has a predicate where project_key is greater than zero. How many zero values do you have? How many non-zero? It might be possible to create an index on the field and see a huge increase in performance, depending on how selective the data within the field is.

I have totally 11864 rows in the table dim_project_secure. And project_key is greater than zero rows is 10019, remaining 1845 rows which are project_key less than zero.

I have an index which is having columns project_id, client_skey, project_skey, owner_account_skey and company_skey on the table dim_project_secure. All these columns has sort order Ascending and project_skey identity is yes.

Thanks.

Suresh.

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Grant Fritchey SSC Guru Points: 398716 More actions · Answer 7

KSB (2/4/2009)
Hi Grant Fritchey,
The first question I would ask is, do you really need 8000+ rows?
Yes, I want all these 8000 rows.
For example, you're getting a scan on dim_project_secure's clustered index. It's matching on the key value and has a predicate where project_key is greater than zero. How many zero values do you have? How many non-zero? It might be possible to create an index on the field and see a huge increase in performance, depending on how selective the data within the field is.
I have totally 11864 rows in the table dim_project_secure. And project_key is greater than zero rows is 10019, remaining 1845 rows which are project_key less than zero.
I have an index which is having columns project_id, client_skey, project_skey, owner_account_skey and company_skey on the table dim_project_secure. All these columns has sort order Ascending and project_skey identity is yes.
Thanks.
Suresh.

Well, the bad news is, selecting 8000 out of 11000 rows, you're going to get a table scan (or a clustered index scan). No real way around it. What are you doing with 8000 rows. You look at all 8000, right? If not, then you don't really need 8000.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning