Hi all,

This is a subjective question for MS SQL Server 2005. I have been doing some consultancy work for a big organization for past few months. Working mainly on SSRS, SSAS and writing ad-hoc queries for them dolts.

I was supposed to get data (most of the time from these 2 tables, 1 is Sales / Transactions and other is Inventory). Sales has almost 10 crores (100 millions, if I am not wrong because 1 crore = 100000000) records while Inventory has 1 crore (10 millions) records.

I am supposed to get data from them between specified dates. Say I am pulling data from Sales from 1 Jan 2009 to 31 Jan 2009. Then I need to join this sub selected data to Inventory data and get final desired output.

All is fine.

But I noticed something. We used to first sub-select relevant data from Sales (selecting data between 2 dates) and then join to Inventory. Last time, I joined the tables first (100 millions into 10 millions) and then applying dates filter. And this seemed to run faster than our first approach.

Why and how is this happening? I believed that joining and sub selecting will create temp tables in master DB. Am I right in saying this? We are either LEFT JOINING or INNER JOINING the tables.