93ee88c0   
   From: esquel@sommarskog.se   
      
   omtechguy (omtechguy@gmail.com) writes:   
   > I have few views which returns lots of rows. i want to use a value   
   > that i will change in one place and all the views will work according   
   > to it. for example:   
   >   
   > CREATE VIEW MyView1   
   > AS   
   > SELECT * FROM MyTable where TheValue in (SELECT myGlobalvalue from   
   > MySettingTable)   
   > GO   
   >   
   > CREATE VIEW MyView2   
   > AS   
   > SELECT * FROM MyTable where TheValue in (SELECT myGlobalvalue from   
   > MySettingTable)   
   > GO   
   >   
   > In the above example i can just change the field myGlobalvalue on   
   > table MySettingTable and all the view will work according that updated   
   > value.   
   >   
   > Its working but its too slow for lots of rows. when i am using "hard   
   > coded" value its much faster.   
   >   
   > I tried function that returns table but its also slow... the only   
   > solution i found is SESSION_INFO but i really don't want to use it for   
   > that... any other option?   
      
   Are these the actual queries, or are they just sketches of the real   
   views?   
      
   If they are the real McCoy is there an index on TheValue? Is that a   
   clustered or a non-clustered index?   
      
   With a hardcoded value, the optimizer has more exact information than   
   if you have something variable.   
      
   I would try an inline-table function, and then use OPTION(RECOMPILE)   
   when I query the function.   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)