February 4, 2009 at 11:16 pm
Hi,
Query is:
SELECT DISTINCT prof_sec.profile_skeyAS profile_skey,
acct_sec.account_skey
FROMdim_account_secureacct_sec
INNER JOINdim_profile_secureprof_sec
ONacct_sec.account_key > 0
ANDprof_sec.profile_key > 0
INNER JOIN #TempAssociateObjectstemp_assoc_obj
ONtemp_assoc_obj.assoc_object_skey = prof_sec.profile_skey
WHERE EXISTS (SELECT 'X' FROM project_securityproj_security
WHERE proj_security.account_skey = acct_sec.account_skey
ANDtemp_assoc_obj.object_skey= proj_security.project_skey
)
AND NOT EXISTS (SELECT 'X' FROM profile_security prof_security
WHERE acct_sec.account_skey = prof_security.account_skey
ANDprof_sec.profile_skey = prof_security.profile_skey)
This query is taking around 34 minutes to execute.
I have one clustered 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.
Another clustered index is on table project_secure having columns account_skey and project_skey.
Table profile_security is having one clustered index having columns account_skey and profile_skey and another non-unique, non-clustered index is having profile_skey column.
I am beginner to SQL. Please help me it is urgent. How can i increase performance for this query.
I attached execution plan for this query.
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
February 4, 2009 at 11:32 pm
hi, can u tell me exactly what you are trying to do .... from your execution plan it's show that you are trying to use select column name into and innerjoin for data i think you made wrong inner join you can't make inner join like > 0 there for must be same column with same data on both side please try to check that inner join or ....
give me in brief from which tables you want data and another information like how many columns in your table and how many rows into that table
so i can help you betterly
Raj Acharya
February 5, 2009 at 12:17 am
I think dat this query will not execute b'coz the join is incorrectly used, it should terminate with error message incorrect syntax
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 5, 2009 at 1:13 am
Suresh, your statement is syntactically correct but almost certainly won't give the results you are expecting.
This...FROM dim_account_secure acct_sec
INNER JOIN dim_profile_secure prof_sec
ON acct_sec.account_key > 0
AND prof_sec.profile_key > 0
isn't an INNER JOIN, it's a cross-join, same as this...FROM dim_account_secure acct_sec, dim_profile_secure prof_sec
WHERE acct_sec.account_key > 0
AND prof_sec.profile_key > 0
If you have 10,000 rows in dim_account_secure where account_key > 0 and 10,000 rows in dim_profile_secure where profile_key > 0, the
internal product of the cross-join will be 10,000 x 10,000 rows.
You should take a look at the entity relationship diagram for these tables and determine what the join criteria should be.
Also, the correlated subquery in your WHERE clause is unlikely to be performant. This...INNER JOIN project_security proj_security
ON proj_security.account_skey = acct_sec.account_skey
AND temp_assoc_obj.object_skey = proj_security.project_skey
will probably run faster than this...WHERE EXISTS (SELECT 'X' FROM project_security proj_security
WHERE proj_security.account_skey = acct_sec.account_skey
AND temp_assoc_obj.object_skey = proj_security.project_skey)
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 5, 2009 at 4:03 am
ChrisM,
Suresh, your statement is syntactically correct but almost certainly won't give the results you are expecting.
This...
FROM dim_account_secure acct_sec
INNER JOIN dim_profile_secure prof_sec
ON acct_sec.account_key > 0
AND prof_sec.profile_key > 0
isn't an INNER JOIN, it's a cross-join, same as this...
FROM dim_account_secure acct_sec, dim_profile_secure prof_sec
WHERE acct_sec.account_key > 0
AND prof_sec.profile_key > 0
This resolved my issue. Now it is taking around 3 min.
Thanks
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
February 5, 2009 at 4:10 am
Thanks for the feedback Suresh. Participants in this thread, including myself, would be interested to see exactly how you implemented the changes which solved your problem - can you post the code please?
Many thanks
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 5, 2009 at 5:25 am
ChrisM,
Here is my code.
SELECT DISTINCT prof_sec.profile_skeyAS profile_skey,
acct_sec.account_skey
INTOdbo.profile_assoc_security
FROMdim_account_secureacct_sec, dim_profile_secureprof_sec
--INNER JOINdim_profile_secureprof_sec
--ONacct_sec.account_key > 0
--ANDprof_sec.profile_key > 0
INNER JOIN #TempAssociateObjectstemp_assoc_obj
ONtemp_assoc_obj.assoc_object_skey = prof_sec.profile_skey
WHERE EXISTS (SELECT 'X' FROM project_securityproj_security
WHERE proj_security.account_skey = acct_sec.account_skey
ANDtemp_assoc_obj.object_skey= proj_security.project_skey
)
AND NOT EXISTS (SELECT 'X' FROM profile_security prof_security
WHERE acct_sec.account_skey = prof_security.account_skey
ANDprof_sec.profile_skey = prof_security.profile_skey)
ANDacct_sec.account_key > 0
ANDprof_sec.profile_key > 0
Many 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
February 5, 2009 at 11:41 am
Ahmad Osama (2/5/2009)
I think dat this query will not execute b'coz the join is incorrectly used, it should terminate with error message incorrect syntax
my apologies..its a cross join..I reproduced it....tho it didn't looks...:)
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 5, 2009 at 12:54 pm
KSB (2/5/2009)
ChrisM,Here is my code.
SELECT DISTINCT prof_sec.profile_skeyAS profile_skey,
acct_sec.account_skey
INTOdbo.profile_assoc_security
FROMdim_account_secureacct_sec, dim_profile_secureprof_sec
--INNER JOINdim_profile_secureprof_sec
--ONacct_sec.account_key > 0
--ANDprof_sec.profile_key > 0
INNER JOIN #TempAssociateObjectstemp_assoc_obj
ONtemp_assoc_obj.assoc_object_skey = prof_sec.profile_skey
WHERE EXISTS (SELECT 'X' FROM project_securityproj_security
WHERE proj_security.account_skey = acct_sec.account_skey
ANDtemp_assoc_obj.object_skey= proj_security.project_skey
)
AND NOT EXISTS (SELECT 'X' FROM profile_security prof_security
WHERE acct_sec.account_skey = prof_security.account_skey
ANDprof_sec.profile_skey = prof_security.profile_skey)
ANDacct_sec.account_key > 0
ANDprof_sec.profile_key > 0
Many Thanks
Suresh
Suresh, if this query works, then it's by accident, not by design. I don't mean to offend but there are many design flaws. Can I suggest that you work through this with the forum until it's resolved? It will help you to understand and to learn. The first thing you must do is to establish how the two tables dim_account_secure and dim_profile_secure are related to each other.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply