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

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

Re: Use External value and use it from some views

Show all headers | View raw

On Feb 28, 3:10 pm, omtechguy <omtech...@gmail.com> wrote:
> Hi,
>
> 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?
>
> Thanks.

First of all, if you are really talking about a single value, I think
it may optimize better if the query is written on that assumpriont:

          SELECT * FROM MyTable where TheValue = (SELECT TOP 1
myGlobalvalue FROM MySettingTable)

second, if you really need mulitple values, my experience is that a
join is generally faster than IN

          SELECT * FROM MyTable t INNER JOIN MySettingTable s ON
t.TheValue=s.myGlobalValue

particularly if myGlobalValue is the primary key.

Fred.

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

Thread

Re: Use External value and use it from some views "Fred." <ghrno-google@yahoo.com> - 2011-04-13 10:36 -0700

csiph-web