Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #169

Re: Use External value and use it from some views

Show all headers | View raw

On 2 מרץ, 10:55, Erland Sommarskog <esq...@sommarskog.se> wrote:
> omtechguy (omtech...@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, esq...@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

Can you please share an example?

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

Re: Use External value and use it from some views omtechguy <omtechguy@gmail.com> - 2011-04-12 23:15 -0700
  Re: Use External value and use it from some views Erland Sommarskog <esquel@sommarskog.se> - 2011-04-13 23:54 +0200

csiph-web