Avoid conditional logic?
in [Programming SQL]
|
From: Marty McDonald on 23 Jul 2010 11:09 I've heard that SQL Server can create a better execution plan if the query has unconditional logic as opposed to conditional logic. So where I might want to do this (conditional logic)... IF @Test = 1 SELECT This, That From Table WHERE This = 'aaa' ELSE SELECT This, That From Table WHERE This = 'bbb' ....I will do this instead (unconditional logic)... SELECT This, That From Table WHERE This = 'aaa' AND @Test = 1 UNION ALL SELECT This, That From Table WHERE This = 'bbb' AND @Test = 2 Can SQL Server really handle the 2nd style better? Thanks Marty
From: Plamen Ratchev on 23 Jul 2010 11:51 Using IF to conditionally execute queries will be more efficient. Take a look at the query plans in both cases and that will show you the difference. -- Plamen Ratchev http://www.SQLStudio.com
From: Gert-Jan Strik on 23 Jul 2010 12:32 Marty McDonald wrote: > > I've heard that SQL Server can create a better execution plan if the > query has unconditional logic as opposed to conditional logic. So > where I might want to do this (conditional logic)... > IF @Test = 1 > SELECT This, That From Table WHERE This = 'aaa' > ELSE > SELECT This, That From Table WHERE This = 'bbb' > > ...I will do this instead (unconditional logic)... > SELECT This, That From Table WHERE This = 'aaa' AND @Test = 1 > UNION ALL > SELECT This, That From Table WHERE This = 'bbb' AND @Test = 2 > > Can SQL Server really handle the 2nd style better? > Thanks > Marty If there is any performance difference, then the "conditional logic" version is faster. If you are unlucky, and the engine "forgets" to shortcut the execution, the UNION ALL query does an unnecessary table access. -- Gert-Jan
From: Tony Rogerson on 23 Jul 2010 12:30 If you are on SQL 2008 or SQL 2008 R2 and on the latest SP and CU then you can use OPTION( RECOMPILE ) to good effect because of embedded parameter optimisation. However, why don't you just do this instead... declare @this_lookup char(3); set @this_lookup = case when @test = 1 then 'aaa' when @test = 2 then 'bbb' else null end SELECT This, That From Table WHERE This = @this_lookup Tony. "Marty McDonald" <mp4mcd(a)gmail.com> wrote in message news:755eab5e-d938-485a-b5be-872378c89c79(a)s24g2000pri.googlegroups.com... > I've heard that SQL Server can create a better execution plan if the > query has unconditional logic as opposed to conditional logic. So > where I might want to do this (conditional logic)... > IF @Test = 1 > SELECT This, That From Table WHERE This = 'aaa' > ELSE > SELECT This, That From Table WHERE This = 'bbb' > > ...I will do this instead (unconditional logic)... > SELECT This, That From Table WHERE This = 'aaa' AND @Test = 1 > UNION ALL > SELECT This, That From Table WHERE This = 'bbb' AND @Test = 2 > > Can SQL Server really handle the 2nd style better? > Thanks > Marty
|
Pages: 1 Prev: SQL Server 2005 CE Tools for VS2005 SP1. Next: jameelmossly@yahoo.com |