Removing HASH Match / Inner Join

Hi,

I'm at my wits end! I have two large tables one with 1.2mill one with 2.3 mill and they are very wide tables. I have a select with an inner join. All columns used in the join are contained in indexes. But it does an index scan and a massive hashmatch. Why is this? both tables have columns in the index ordered the same, all datatypes of the indexes are ints. The code looks like this.

SELECT TblActivities.*
FROM TblActivities
INNER JOIN TblBookingsCraig
ON
TblActivities.clientID = TblBookingsCraig.clientid AND
TblActivities.campaignID = TblBookingsCraig.campaignid AND
TblActivities.SupplierID = TblBookingsCraig.SupplierID and
TblActivities.CreativeVersion = TblBookingsCraig.CreativeVersion

Does anyone know why it won't perform an index seek?

Cheers
CYou should have the indexes on the fields you want to join, do you have them like that?

TblActivities.clientID = TblBookingsCraig.clientid AND
TblActivities.campaignID = TblBookingsCraig.campaignid AND
TblActivities.SupplierID = TblBookingsCraig.SupplierID and
TblActivities.CreativeVersion = TblBookingsCraig.CreativeVersion|||Hi,

Thanks for the reply. Yes I do have the indexes identically setup on both tables. Could it be ignoring the indexes because of the size difference of the two tables? One has 1millish and the other 2.5mill ish?

Cheers
C|||Did you check the values of these columns? if they contain duplicate values or highly identical values for number of records; say for 100 records at least the indexes have same values. In this case, the index scan would be the decision that SQL optimizer might take!

Removing Duplicates rows from Inner Join

i have two tables, tab1 having N1 col and tab2 N2 col. now N1 is subset
of N2.
I need the information from tab2 (having N2) of all rows having the
matching entry in N1 in tab1.
For this i am using Inner Join on cols N1 and n2. But result is giving
duplicate rows. Can anyone suggest how do u i remove those duplicate
rows? or may be a better way to do the above work... Thanks
Sounds like you should be using EXISTS
rather than a join

SELECT * FROM tab2
WHERE EXISTS (SELECT * FROM tab1 WHERE tab1.N1=tab2.N2)|||thanks mark
but i have tables with large data and running this querry is taking a
lot of time.
can u suggest a better method or optimize this querry?|||Did you try to use DISTINCT to remove the duplicates?
SELECT DISTINCT tab2.* FROM tab2 INNER JOIN tab1 ON tab2.N2 = tab1.N1|||Can you post your DDL including indexes|||asgars (asgars@.gmail.com) writes:
> thanks mark
> but i have tables with large data and running this querry is taking a
> lot of time.
> can u suggest a better method or optimize this querry?

Which query? It's very difficult to suggest optimizations to a query
without seeing it, and without knowledge of the tables.

Please post:

o The query you are using now.
o CREATE TABLE and CREATE INDEX statements for the inolved tables.
o Some indication on number of rows in the table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx