I have two queries which return same output, except that query 1 runs in less than 1 second and query 2 runs in 26 minutes.
select OIdA into #test from A where A.User = 123456
SELECT AD.* FROM AD where AD.OIdA in (select OIdA from #test)
SELECT AD.* FROM AD, A WHERE A.OIdA = AD.OIdA AND A.User = 123456
1. SQL Server 2016.
2. A and AD are large tables with millions of rows. Some records from tables A and AD are deleted on daily basis.
3. OIdA is primary key on A.
4. OIdAD is primary key on AD.
5. AD has an index on columns OIdA, OIdAD.
Thank you for your help.