I am in need of getting data from multiple select queries. So I thought of creating two views and to inner join them to get my data.
My questions is: which option is better
- creating a view,
- creating temporary table
-
3If a join satisfies the query you want to run, why do you need a view or a temporary table? How do you define "better"?– Aaron BertrandCommented Jun 18, 2012 at 12:55
-
@Aaron..the problem is when i use a single join query i won't get the result because the conditions are different for the select query. so i have to select the data in two ways...– SivajithCommented Jun 18, 2012 at 16:09
-
Then please provide more details. How are you going to "inner join the two joins" as you stated in your question? Can you show some sample data and desired results, so we have some clue what you're talking about?– Aaron BertrandCommented Jun 18, 2012 at 16:14
Add a comment
|
1 Answer
Using two VIEWs in your SELECT statement will always be better because it can combine both of the views query plans into a single optimised query. Very neat solution which is also fast.
BTW. Using an in-memory temp table > 100 rows generally poor performance because the query plan optimiser assumes there is only 1 row in the temp table.
answered Jun 18, 2012 at 15:22
-
1When you say "in-memory temp table" are you referring to a table variable?– JNKCommented Jun 18, 2012 at 15:37
-
@Simon Hughes..thank you Simon..its a great information to me..(by temp table i mean #tables. i think u also mean it).– SivajithCommented Jun 18, 2012 at 16:05
-
i also want to know what you mean by 'in-memory temp table'?.– SivajithCommented Jun 18, 2012 at 16:11
-
1I don't know if I would agree that joining two views will be faster than a single join between the same tables. Can you show an example? Commented Jun 18, 2012 at 16:13
-
There are table variables and there are temp tables. Comparing Table Variables with Temporary Tables. Both may be stored in memory or on disk. Commented Jun 18, 2012 at 16:20