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

Re: Use External value and use it from some views

Path	csiph.com!x330-a1.tempe.blueboxinc.net!feeder1.hal-mli.net!nx02.iad01.newshosting.com!newshosting.com!69.16.185.11.MISMATCH!npeer01.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!postnews.google.com!l2g2000prg.googlegroups.com!not-for-mail
From	"Fred." <ghrno-google@yahoo.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: Use External value and use it from some views
Date	Wed, 13 Apr 2011 10:36:16 -0700 (PDT)
Organization	http://groups.google.com
Lines	49
Message-ID	<36595eed-92ec-4499-94dc-fa9c7c812f1e@l2g2000prg.googlegroups.com> (permalink)
References	<98788af4-5f23-4e7f-a516-9195e5b7a41a@o10g2000vbg.googlegroups.com>
NNTP-Posting-Host	198.234.82.254
Mime-Version	1.0
Content-Type	text/plain; charset=ISO-8859-1
Content-Transfer-Encoding	quoted-printable
X-Trace	posting.google.com 1302716284 5933 127.0.0.1 (13 Apr 2011 17:38:04 GMT)
X-Complaints-To	groups-abuse@google.com
NNTP-Posting-Date	Wed, 13 Apr 2011 17:38:04 +0000 (UTC)
Complaints-To	groups-abuse@google.com
Injection-Info	l2g2000prg.googlegroups.com; posting-host=198.234.82.254; posting-account=H0CbGQoAAACbl_tS1RfYwvMt_jmh18zO
User-Agent	G2/1.0
X-HTTP-UserAgent	Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; InfoPath.2; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729),gzip(gfe)
Xref	x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:172

Show key headers only | 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