Alex Rivera | Logout

SQL - Filtering large tables with joins - best practices

Asked 2011-03-31T08:06:35.623
11

I have a table with a lot of data and I need to join it with some other large tables.

Only a small portion of my table is actually relevant for me each time.

When is it best to filter my data?

  1. In the where clause of the SQL.

  2. Create a temp table with the specific data and only then join it.

  3. Add the predicate to the first inner join ON clause.

  4. Some other idea.

1.

Select * 
From RealyBigTable
Inner Join AnotherBigTable On …
Inner Join YetAnotherBigTable On …
Where RealyBigTable.Type = ?

2.

Select * 
Into #temp
From RealyBigTable
Where RealyBigTable.Type = ?

Select * 
From #temp
Inner Join AnotherBigTable On …
Inner Join YetAnotherBigTable On …

3.

Select * 
From RealyBigTable
Inner Join AnotherBigTable On RealyBigTable.type = ? And … 
Inner Join YetAnotherBigTable On …

Another question: What happens first? Join or Where?

Edit
Report

1 Answer

0

In a decent cost based query planner what happens is (your case)

  1. join conditions and where conditions are parsed at same level

  2. the type of join and statistics determines the path (what happens first) - in such a way that the smallest intermediate results are retrieved (least I/O > fastest query)

answered 2011-03-31T08:22:18.753

Your Answer