Optimization question

• digimetic

  SSC Veteran

  Points: 253

  More actions

  June 2, 2010 at 2:32 pm

  #233316

  hi,

  i've been going through an SSIS tutorial on MSDN and came across the following query to be used in a transformation:

  select * from (select * from [dbo].[DimCurrency]) as refTable

  where [refTable].[CurrencyAlternateKey] = 'ARS'

  OR

  [refTable].[CurrencyAlternateKey] = 'AUD'

  OR

  [refTable].[CurrencyAlternateKey] = 'BRL'

  OR

  [refTable].[CurrencyAlternateKey] = 'CAD'

  OR

  [refTable].[CurrencyAlternateKey] = 'CNY'

  OR

  [refTable].[CurrencyAlternateKey] = 'DEM'

  OR

  [refTable].[CurrencyAlternateKey] = 'EUR'

  OR

  [refTable].[CurrencyAlternateKey] = 'FRF'

  OR

  [refTable].[CurrencyAlternateKey] = 'GBP'

  OR

  [refTable].[CurrencyAlternateKey] = 'JPY'

  OR

  [refTable].[CurrencyAlternateKey] = 'MXN'

  OR

  [refTable].[CurrencyAlternateKey] = 'SAR'

  OR

  [refTable].[CurrencyAlternateKey] = 'USD'

  OR

  [refTable].[CurrencyAlternateKey] = 'VEB'

  i'm wondering why this wouldn't just be written as

  select * from [dbo].[DimCurrency] c

  where [c].[CurrencyAlternateKey] = 'AFA'

  ...

  also, why not use an IN clause here rather than repetitive OR conditions?

  thanks

• WayneS

  SSC Guru

  Points: 95461

  More actions

  June 2, 2010 at 2:53 pm

  #1175586

  Both the [refTable] and [c] are table aliases... use whichever one you want to.

  The optimizer expands an IN to all those OR conditions... check the execution plan, they will be the same. However, using the repetitive "OR" does same the optimizer from having to do this, though it won't be saving very much.

  So, the answer for both of your questions is just personal preference. Either one works the same.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  June 2, 2010 at 3:01 pm

  #1175589

  This is a SSIS generated query.

  Query creation may be tricky some times.

  In this particular case I'm guessing that the developer...

  1- Left space for a WHERE condition on the outer query, appears that option was not used in this particular case so no condition is there.

  2- Found easier to add an OR line for each user parameter instead of having to deal with the IN() syntax.

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• digimetic

  SSC Veteran

  Points: 253

  More actions

  June 3, 2010 at 6:43 am

  #1175898

  PaulB-TheOneAndOnly (6/2/2010)

  ---

  This is a SSIS generated query.

  Hi guys, thanks for the quick replies. I believe the above quote is the answer i was looking for. I'd never seen a subquery used in the way presented (which seemed pointless)

  thanks again,

  ben