Geek Answers Handbook

SQL WHERE on a large table & # 8594; Join a small table first or put FK directly in the WHERE clause?

What is better in the offer WHERE?

I have a large table with FK on a small table. I can search directly on FK, or I can join the FK table and set a restriction WHEREon the joined table. Which is better / preferable?

So this is:

SELECT lt.* FROM LargeTable lt 
WHERE lt.SomeId in(12,55)

Or that:

SELECT lt.* FROM LargeTable lt 
INNER JOIN SmallTable st ON lt.SomeId=st.ItemId
WHERE st.Id in(12,55)


I tested this with help Set statistics time on, but I did not expect this as a result. Who can explain what is going on here?

First test without attachment:

(946 row(s) affected)
 SQL Server Execution Times:
   CPU time = 1544 ms,  elapsed time = 1580 ms.

Second test with connection

(946 row(s) affected)
 SQL Server Execution Times:
   CPU time = 2636 ms,  elapsed time = 366 ms.

EDIT: SELECT Id SELECT *, , 25%, 75% .

+5
3

... , 50% .

largeTable.SomeId, :

SELECT lt.* FROM LargeTable lt 
WHERE lt.SomeId in(12,55)

EDIT:

, , .

, :

,

, , . , , , , .

+4

2 , :

  •  
  • ? .    
  • , , st.Id (12, 55), where , , , ,  
0

  • , . , Non clustered Index "SomeID"?

  • . . .

  • , " Non Clustered Index"

  • , . , .

    Select ColumnName From
(
 Select ColumnName, SomeID from LargeTable Where SomeID in(12,13)
)T
Inner Join SmallTable T1 on T.SomeID = T1.SomeID

  • If you need very fast results. You can do as below.

    Create table #Large
(
  Id Int,
  ColumnName Varchar(100)
)
Insert into #Large(ID, ColumnNmae)
Select ColumnName, SomeID from LargeTable Where SomeID in(12,13)

And finally connect

Select ColumnName From #Large T
Inner Join SmallTable T1 on T.SomeID = T1.SomeID

and without connection

Select ColumnName from #Large
0
source

More articles:


All Articles