inner join give me huge data when package id equal package id from another table ?

Question

I work on sql server 2014 i face issue when join two tables as inner join
from table package it have 100000 rows
and table package2 100000 rows
so it will give result as
100000 * 100000
and it take too much time
so how to handle that issue of big data
are there are any way to get data quickly

create table #package
(
packageId int,
PartId int
)
insert into #package(packageId,PartId)
values
(1121,2311),
(1121,9911),
(1121,2020),
(1121,5052),
(1194,4311),
(1194,9812),
(1194,2391),
(1194,5541)


create table #package2
(
packageId int,
PartId int
)
insert into #package2(packageId,PartId)
values
(1121,2377),
(1121,2111),
(1121,3420),
(1121,5057),
(1194,4388),
(1194,9912),
(1194,1091),
(1194,6441)

select p.partid as partc,p2.partid as partx  from #package p
inner join #package2 p2 on p.packageId=p2.packageId

Answer 1

Hi @ahmed salah ，

Try:

 CREATE INDEX index1 ON package2 (packageId);  

A tip to increase JOIN performance: in SQL you should always put a non-clustered index on a foreign key column.

So if you have a table package, it probably has a primary key something like packageId. SQL Server will put an index on that automatically.

However, if you have a table packageId2 that has a foreign key relationship with packageId， there is by default no index on the column packageId2.packageId. But such an index is very useful and helpful for joins and lookups, so that's a best practice : put an index on all your foreign key columns in a table.

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

so it will give result as 100000 * 100000

You JOIN both tables over column packageId and you have e.g. packageId = 1121 in both tables 4 times => 4 x 4 = 16 rows as result just for this one id; what else do you expect?

Answer 3

You gain best performance for the tables with large data, with using clustered column-stored index.

Covering Row-Stored index is usefull too.