Use External value and use it from some views

#1

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.

#2

omtechguy (omtechguy (AT) gmail (DOT) com) writes:

Quote:

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 (AT) sommarskog (DOT) 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

#3

On 2 מרץ, 10:55, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

omtechguy (omtech... (AT) gmail (DOT) 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... (AT) sommarskog (DOT) 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?

#4

On Feb 28, 3:10*pm, omtechguy <omtech... (AT) gmail (DOT) com> wrote:

Quote:

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.

#5

omtechguy (omtechguy (AT) gmail (DOT) com) writes:

Quote:

On 2 ???, 10:55, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
omtechguy (omtech... (AT) gmail (DOT) com) writes:
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.

Can you please share an example?

CREATE FUNCTION myfunc(@myglobbval int) RETURNS TABLE AS
RETURN (SELECT * FROM MyTable where TheValue = @myglobval)
go
SELECT @myglobvalue = myglobalvalue
FROM MySettingTable
SELECT * FROM myfunc(@myglobval)
OPTION (RECOMPILE)

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Use External value and use it from some views

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Use External value and use it from some views - 02-28-2011 , 01:10 PM

Re: Use External value and use it from some views - 03-02-2011 , 01:55 AM

Re: Use External value and use it from some views - 04-13-2011 , 01:15 AM

Re: Use External value and use it from some views - 04-13-2011 , 12:36 PM

Re: Use External value and use it from some views - 04-13-2011 , 04:54 PM