CODEWITHSUNDEEP
sql-server-2008
SaiBand
SaiBand

Reputation: 5355

INNER JOIN taking up too much time

In my database, I have a table called FinanceProfile. It has a column called FinanceProfileId.

I have another table called FinanceProfileState. This table has a foreign key called FinanceProfileId that references to the record in FinanceProfile Table.

I have stored proc which does the following:-

I have a temp table called @MatchingContactFinancialProfiles.

DECLARE @MatchingContactFinancialProfiles TABLE
( 
   FinanceProfileId int,
   ContactId uniqueidentifier
);

@MatchingContactFinancialProfiles contains subset of FinanceProfile table. It contains about 100 rows.

FinanceProfileState table has about 4500 matching rows with @MatchingContactFinancialProfiles.

I am doing a join like this:

INSERT INTO @FinanceProfileStates
       SELECT distinct fpState.FinanaceProfileId, fpState.StateId
          FROM @MatchingContactFinancialProfiles fp inner JOIN FinanaceProfileState fpState
          on fp.FinanceProfileId = fpState.FinanaceProfileId

I ran the profiler and it says from right to left: 

Step 1: Table Scan @MatchingContactFinancialProfiles  - Cost 5%
Step 2:  A) Hash Match Aggregate- 19%
         B) Clustered Index Scan - Table - FinanaceProfileState  PK - FinanaceProfileStateId - 15%
Step 3: Hash Match - Inner Join - 38%
Step 4: Distinct Sort - 15%
Step 5: Insert @FinanceProfileStates - 8%

This query takes up most time in the stored procedure. What can I do to make this query run faster ?

Any ideas and suggestions are welcome !!!!

Upvotes: 0

Views: 150

Answers (1)

DeanOC
DeanOC

Reputation: 7262

You don't have any indexes on your @MatchingContactFinancialProfiles table variable.

Try using 

DECLARE @MatchingContactFinancialProfiles TABLE
( 
   FinanceProfileId int PRIMARY KEY,
   ContactId uniqueidentifier
);

According to this link you can also get better performance by using a temp table rather than the table variable that you are currently using.

Upvotes: 1

Related Questions