Monday, 15 March 2010

sql server - Using 'where then Union' or Using 'Union then Where' -


After

Kindly have these two types of questions in your mind:

  - QUERY1 someFields Select some from someTables where someWhereClues Association selects all someFields, select some tables where someWhereClues --query2 Select DT (someTables Association Select some from someTables from someFields someFields) * from where someMixedWhereClues  

Note:
The end result field in both queries is the same

I thought that before. The query is fast or its performance is better!
But after some research I am confused by this:

SQL Server (as a sample of RDBMS) first reads the entire data, then get the records. => Then read all records in both questions and will explore.

Help me on my misunderstanding, and if there is any other difference between QUERY1 and QUERY2 there?


Edit : Add sample plans:

  Select t.Name, t.type from sys.tables t where t Type = 'U' union all t.Name, sys.objects t where t.type = select from 'u' to select t.type (t.Name, sys.tables T. All select the union, T.type selection from t.type from sis .objects t) DT where dt.type = 'U'  

are the execution plans:

Both are same and 50%

Then after the

, optimizing both queries you get almost the same performance.


No comments:

Post a Comment