[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

How to avoid Redundant use of Costly View in Derived Tables

steventhrasher42

3/31/2007 4:24:00 AM

I have a query that references a costly view as well as two derived
tables that also need to reference the same costly view. The question
is - is there a way to get this information back in one query while
avoiding having to use the view multiple times? Perhaps SQL does not
support this, and I just need to resort to denormalizing some data or
such.

Sample of problem SQL below, plus schema and data. Any ideas
appreciated. Thanks.

/* THE PROBLEM - Complex/costly view vwDepartment must be repeated
multiple times. */
select D.Name, B.Amount, S.Amount
from vwDepartment D
left join (
select D.DepartmentID, sum(B.Amount) as Amount
from vwDepartment D
join tblBudget B on B.DepartmentID = D.DepartmentID
--where ...some criteria based upon D and B...
group by D.DepartmentID
) B on B.DepartmentID = D.DepartmentID
left join (
select D.DepartmentID, sum(S.Amount) as Amount
from vwDepartment D
join tblSpending S on S.DepartmentID = D.DepartmentID
--where ...some criteria based upon D and S...
group by D.DepartmentID
) S on S.DepartmentID = D.DepartmentID
where (B.Amount > 1000000 or S.Amount > 1000000)
--and ...some criteria based upon D...

/* Wish this would work - ability to reference outer columns in
derived table. */
select D.Name, B.Amount, S.Amount
from vwDepartment D
left join (
select sum(B.Amount) as Amount
from tblBudget B
where B.DepartmentID = D.DepartmentID
--and ...some criteria based upon D and B...
) B on 1 = 1
left join (
select sum(S.Amount) as Amount
from tblSpending S
where S.DepartmentID = D.DepartmentID
--and ...some criteria based upon D and S...
) S on 1 = 1
where B.Amount > 1000000 or S.Amount > 1000000
--and ...some criteria based upon D...

/* Create schema */
create table tblDepartment (
DepartmentID int not null, Name varchar(100) not null,
constraint pkDepartment primary key (DepartmentID))
create table tblBudget (
DepartmentID int not null, Year int not null, Amount int not
null,
constraint pkBudget primary key (DepartmentID, Year))
create table tblSpending (
DepartmentID int not null, Year int not null, Amount int not
null,
constraint pkSpending primary key (DepartmentID, Year))
go

/* Pretend this view is very complex/costly */
create view vwDepartment as select * from tblDepartment
go

/* Create data */
insert into tblDepartment values (1, 'A')
insert into tblDepartment values (2, 'B')
insert into tblBudget values (1, 2000, 1000000)
insert into tblBudget values (1, 2001, 1000000)
insert into tblBudget values (1, 2002, 1000000)
insert into tblBudget values (2, 2000, 1000000)
insert into tblBudget values (2, 2001, 1000000)
insert into tblBudget values (2, 2002, 1000000)
insert into tblSpending values (1, 2000, 1000000)
insert into tblSpending values (1, 2001, 1000000)
insert into tblSpending values (1, 2002, 1000000)
insert into tblSpending values (2, 2000, 1000000)
insert into tblSpending values (2, 2001, 1000000)
insert into tblSpending values (2, 2002, 1000000)

/* Clean up */
drop table tblDepartment
drop table tblBudget
drop table tblSpending
drop view vwDepartment
9 Answers

Tibor Karaszi

3/31/2007 8:55:00 AM

0

Possibly Comon Table Expression will help you (assuming 2005). Not sure how it will be optimizer,
though. Basic principle (totally meaningless except for getting the idea across):

WITH myTab
AS
(
SELECT c1, c2 FROM tbl
)
SELECT * FROM myTab
UNION
SELECT * FROM myTab
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://sqlblog.com/blogs/tib...


<steventhrasher42@hotmail.com> wrote in message
news:1175315056.052104.315000@p15g2000hsd.googlegroups.com...
>I have a query that references a costly view as well as two derived
> tables that also need to reference the same costly view. The question
> is - is there a way to get this information back in one query while
> avoiding having to use the view multiple times? Perhaps SQL does not
> support this, and I just need to resort to denormalizing some data or
> such.
>
> Sample of problem SQL below, plus schema and data. Any ideas
> appreciated. Thanks.
>
> /* THE PROBLEM - Complex/costly view vwDepartment must be repeated
> multiple times. */
> select D.Name, B.Amount, S.Amount
> from vwDepartment D
> left join (
> select D.DepartmentID, sum(B.Amount) as Amount
> from vwDepartment D
> join tblBudget B on B.DepartmentID = D.DepartmentID
> --where ...some criteria based upon D and B...
> group by D.DepartmentID
> ) B on B.DepartmentID = D.DepartmentID
> left join (
> select D.DepartmentID, sum(S.Amount) as Amount
> from vwDepartment D
> join tblSpending S on S.DepartmentID = D.DepartmentID
> --where ...some criteria based upon D and S...
> group by D.DepartmentID
> ) S on S.DepartmentID = D.DepartmentID
> where (B.Amount > 1000000 or S.Amount > 1000000)
> --and ...some criteria based upon D...
>
> /* Wish this would work - ability to reference outer columns in
> derived table. */
> select D.Name, B.Amount, S.Amount
> from vwDepartment D
> left join (
> select sum(B.Amount) as Amount
> from tblBudget B
> where B.DepartmentID = D.DepartmentID
> --and ...some criteria based upon D and B...
> ) B on 1 = 1
> left join (
> select sum(S.Amount) as Amount
> from tblSpending S
> where S.DepartmentID = D.DepartmentID
> --and ...some criteria based upon D and S...
> ) S on 1 = 1
> where B.Amount > 1000000 or S.Amount > 1000000
> --and ...some criteria based upon D...
>
> /* Create schema */
> create table tblDepartment (
> DepartmentID int not null, Name varchar(100) not null,
> constraint pkDepartment primary key (DepartmentID))
> create table tblBudget (
> DepartmentID int not null, Year int not null, Amount int not
> null,
> constraint pkBudget primary key (DepartmentID, Year))
> create table tblSpending (
> DepartmentID int not null, Year int not null, Amount int not
> null,
> constraint pkSpending primary key (DepartmentID, Year))
> go
>
> /* Pretend this view is very complex/costly */
> create view vwDepartment as select * from tblDepartment
> go
>
> /* Create data */
> insert into tblDepartment values (1, 'A')
> insert into tblDepartment values (2, 'B')
> insert into tblBudget values (1, 2000, 1000000)
> insert into tblBudget values (1, 2001, 1000000)
> insert into tblBudget values (1, 2002, 1000000)
> insert into tblBudget values (2, 2000, 1000000)
> insert into tblBudget values (2, 2001, 1000000)
> insert into tblBudget values (2, 2002, 1000000)
> insert into tblSpending values (1, 2000, 1000000)
> insert into tblSpending values (1, 2001, 1000000)
> insert into tblSpending values (1, 2002, 1000000)
> insert into tblSpending values (2, 2000, 1000000)
> insert into tblSpending values (2, 2001, 1000000)
> insert into tblSpending values (2, 2002, 1000000)
>
> /* Clean up */
> drop table tblDepartment
> drop table tblBudget
> drop table tblSpending
> drop view vwDepartment
>

steventhrasher42

3/31/2007 2:29:00 PM

0

I should have mentioned that I tried that. The optimizer treats a CTE
just like a view or derived table. In other words, it expands it out
completely each time you reference it. Thanks.

On Mar 31, 3:55 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@hotmail.nomail.com> wrote:
> Possibly Comon Table Expression will help you (assuming 2005). Not sure how it will be optimizer,
> though. Basic principle (totally meaningless except for getting the idea across):
>
> WITH myTab
> AS
> (
> SELECT c1, c2 FROM tbl
> )
> SELECT * FROM myTab
> UNION
> SELECT * FROM myTab
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tib...
>
> <steventhrashe...@hotmail.com> wrote in message
>
> news:1175315056.052104.315000@p15g2000hsd.googlegroups.com...
>
>
>
> >I have a query that references a costly view as well as two derived
> > tables that also need to reference the same costly view. The question
> > is - is there a way to get this information back in one query while
> > avoiding having to use the view multiple times? Perhaps SQL does not
> > support this, and I just need to resort to denormalizing some data or
> > such.
>
> > Sample of problem SQL below, plus schema and data. Any ideas
> > appreciated. Thanks.
>
> > /* THE PROBLEM - Complex/costly view vwDepartment must be repeated
> > multiple times. */
> > select D.Name, B.Amount, S.Amount
> > from vwDepartment D
> > left join (
> > select D.DepartmentID, sum(B.Amount) as Amount
> > from vwDepartment D
> > join tblBudget B on B.DepartmentID = D.DepartmentID
> > --where ...some criteria based upon D and B...
> > group by D.DepartmentID
> > ) B on B.DepartmentID = D.DepartmentID
> > left join (
> > select D.DepartmentID, sum(S.Amount) as Amount
> > from vwDepartment D
> > join tblSpending S on S.DepartmentID = D.DepartmentID
> > --where ...some criteria based upon D and S...
> > group by D.DepartmentID
> > ) S on S.DepartmentID = D.DepartmentID
> > where (B.Amount > 1000000 or S.Amount > 1000000)
> > --and ...some criteria based upon D...
>
> > /* Wish this would work - ability to reference outer columns in
> > derived table. */
> > select D.Name, B.Amount, S.Amount
> > from vwDepartment D
> > left join (
> > select sum(B.Amount) as Amount
> > from tblBudget B
> > where B.DepartmentID = D.DepartmentID
> > --and ...some criteria based upon D and B...
> > ) B on 1 = 1
> > left join (
> > select sum(S.Amount) as Amount
> > from tblSpending S
> > where S.DepartmentID = D.DepartmentID
> > --and ...some criteria based upon D and S...
> > ) S on 1 = 1
> > where B.Amount > 1000000 or S.Amount > 1000000
> > --and ...some criteria based upon D...
>
> > /* Create schema */
> > create table tblDepartment (
> > DepartmentID int not null, Name varchar(100) not null,
> > constraint pkDepartment primary key (DepartmentID))
> > create table tblBudget (
> > DepartmentID int not null, Year int not null, Amount int not
> > null,
> > constraint pkBudget primary key (DepartmentID, Year))
> > create table tblSpending (
> > DepartmentID int not null, Year int not null, Amount int not
> > null,
> > constraint pkSpending primary key (DepartmentID, Year))
> > go
>
> > /* Pretend this view is very complex/costly */
> > create view vwDepartment as select * from tblDepartment
> > go
>
> > /* Create data */
> > insert into tblDepartment values (1, 'A')
> > insert into tblDepartment values (2, 'B')
> > insert into tblBudget values (1, 2000, 1000000)
> > insert into tblBudget values (1, 2001, 1000000)
> > insert into tblBudget values (1, 2002, 1000000)
> > insert into tblBudget values (2, 2000, 1000000)
> > insert into tblBudget values (2, 2001, 1000000)
> > insert into tblBudget values (2, 2002, 1000000)
> > insert into tblSpending values (1, 2000, 1000000)
> > insert into tblSpending values (1, 2001, 1000000)
> > insert into tblSpending values (1, 2002, 1000000)
> > insert into tblSpending values (2, 2000, 1000000)
> > insert into tblSpending values (2, 2001, 1000000)
> > insert into tblSpending values (2, 2002, 1000000)
>
> > /* Clean up */
> > drop table tblDepartment
> > drop table tblBudget
> > drop table tblSpending
> > drop view vwDepartment- Hide quoted text -
>
> - Show quoted text -

mesaalejandro

3/31/2007 4:29:00 PM

0

steventhrasher42@hotmail.com,

Can you try:

select
D.DepartmentID, D.Name, sum(B.Amount) as B_Amount, sum(S.Amount) as
S_Amount
from
dbo.tblDepartment as D
left join
dbo.tblBudget as B
on B.DepartmentID = D.DepartmentID
and ...some criteria based upon D and B...
left join
dbo.tblSpending as S
on S.DepartmentID = D.DepartmentID
and ...some criteria based upon D and S...
group by
D.DepartmentID, D.Name
having
sum(B.Amount) > 1000000 or sum(S.Amount) > 1000000
go

Compare execution plans.

AMB

<steventhrasher42@hotmail.com> wrote in message
news:1175315056.052104.315000@p15g2000hsd.googlegroups.com...
>I have a query that references a costly view as well as two derived
> tables that also need to reference the same costly view. The question
> is - is there a way to get this information back in one query while
> avoiding having to use the view multiple times? Perhaps SQL does not
> support this, and I just need to resort to denormalizing some data or
> such.
>
> Sample of problem SQL below, plus schema and data. Any ideas
> appreciated. Thanks.
>
> /* THE PROBLEM - Complex/costly view vwDepartment must be repeated
> multiple times. */
> select D.Name, B.Amount, S.Amount
> from vwDepartment D
> left join (
> select D.DepartmentID, sum(B.Amount) as Amount
> from vwDepartment D
> join tblBudget B on B.DepartmentID = D.DepartmentID
> --where ...some criteria based upon D and B...
> group by D.DepartmentID
> ) B on B.DepartmentID = D.DepartmentID
> left join (
> select D.DepartmentID, sum(S.Amount) as Amount
> from vwDepartment D
> join tblSpending S on S.DepartmentID = D.DepartmentID
> --where ...some criteria based upon D and S...
> group by D.DepartmentID
> ) S on S.DepartmentID = D.DepartmentID
> where (B.Amount > 1000000 or S.Amount > 1000000)
> --and ...some criteria based upon D...
>
> /* Wish this would work - ability to reference outer columns in
> derived table. */
> select D.Name, B.Amount, S.Amount
> from vwDepartment D
> left join (
> select sum(B.Amount) as Amount
> from tblBudget B
> where B.DepartmentID = D.DepartmentID
> --and ...some criteria based upon D and B...
> ) B on 1 = 1
> left join (
> select sum(S.Amount) as Amount
> from tblSpending S
> where S.DepartmentID = D.DepartmentID
> --and ...some criteria based upon D and S...
> ) S on 1 = 1
> where B.Amount > 1000000 or S.Amount > 1000000
> --and ...some criteria based upon D...
>
> /* Create schema */
> create table tblDepartment (
> DepartmentID int not null, Name varchar(100) not null,
> constraint pkDepartment primary key (DepartmentID))
> create table tblBudget (
> DepartmentID int not null, Year int not null, Amount int not
> null,
> constraint pkBudget primary key (DepartmentID, Year))
> create table tblSpending (
> DepartmentID int not null, Year int not null, Amount int not
> null,
> constraint pkSpending primary key (DepartmentID, Year))
> go
>
> /* Pretend this view is very complex/costly */
> create view vwDepartment as select * from tblDepartment
> go
>
> /* Create data */
> insert into tblDepartment values (1, 'A')
> insert into tblDepartment values (2, 'B')
> insert into tblBudget values (1, 2000, 1000000)
> insert into tblBudget values (1, 2001, 1000000)
> insert into tblBudget values (1, 2002, 1000000)
> insert into tblBudget values (2, 2000, 1000000)
> insert into tblBudget values (2, 2001, 1000000)
> insert into tblBudget values (2, 2002, 1000000)
> insert into tblSpending values (1, 2000, 1000000)
> insert into tblSpending values (1, 2001, 1000000)
> insert into tblSpending values (1, 2002, 1000000)
> insert into tblSpending values (2, 2000, 1000000)
> insert into tblSpending values (2, 2001, 1000000)
> insert into tblSpending values (2, 2002, 1000000)
>
> /* Clean up */
> drop table tblDepartment
> drop table tblBudget
> drop table tblSpending
> drop view vwDepartment
>


steventhrasher42

3/31/2007 4:38:00 PM

0

That'll result in a cross join between Spending and Budget amounts
causing them to be counted more than once, or in my example they come
out to 9,000,000 instead of 3,000,000.


On Mar 31, 11:29 am, "Alejandro Mesa" <mesaalejan...@msn.com> wrote:
> steventhrashe...@hotmail.com,
>
> Can you try:
>
> select
> D.DepartmentID, D.Name, sum(B.Amount) as B_Amount, sum(S.Amount) as
> S_Amount
> from
> dbo.tblDepartment as D
> left join
> dbo.tblBudget as B
> on B.DepartmentID = D.DepartmentID
> and ...some criteria based upon D and B...
> left join
> dbo.tblSpending as S
> on S.DepartmentID = D.DepartmentID
> and ...some criteria based upon D and S...
> group by
> D.DepartmentID, D.Name
> having
> sum(B.Amount) > 1000000 or sum(S.Amount) > 1000000
> go
>
> Compare execution plans.
>
> AMB
>
> <steventhrashe...@hotmail.com> wrote in message
>
> news:1175315056.052104.315000@p15g2000hsd.googlegroups.com...
>
>
>
> >I have a query that references a costly view as well as two derived
> > tables that also need to reference the same costly view. The question
> > is - is there a way to get this information back in one query while
> > avoiding having to use the view multiple times? Perhaps SQL does not
> > support this, and I just need to resort to denormalizing some data or
> > such.
>
> > Sample of problem SQL below, plus schema and data. Any ideas
> > appreciated. Thanks.
>
> > /* THE PROBLEM - Complex/costly view vwDepartment must be repeated
> > multiple times. */
> > select D.Name, B.Amount, S.Amount
> > from vwDepartment D
> > left join (
> > select D.DepartmentID, sum(B.Amount) as Amount
> > from vwDepartment D
> > join tblBudget B on B.DepartmentID = D.DepartmentID
> > --where ...some criteria based upon D and B...
> > group by D.DepartmentID
> > ) B on B.DepartmentID = D.DepartmentID
> > left join (
> > select D.DepartmentID, sum(S.Amount) as Amount
> > from vwDepartment D
> > join tblSpending S on S.DepartmentID = D.DepartmentID
> > --where ...some criteria based upon D and S...
> > group by D.DepartmentID
> > ) S on S.DepartmentID = D.DepartmentID
> > where (B.Amount > 1000000 or S.Amount > 1000000)
> > --and ...some criteria based upon D...
>
> > /* Wish this would work - ability to reference outer columns in
> > derived table. */
> > select D.Name, B.Amount, S.Amount
> > from vwDepartment D
> > left join (
> > select sum(B.Amount) as Amount
> > from tblBudget B
> > where B.DepartmentID = D.DepartmentID
> > --and ...some criteria based upon D and B...
> > ) B on 1 = 1
> > left join (
> > select sum(S.Amount) as Amount
> > from tblSpending S
> > where S.DepartmentID = D.DepartmentID
> > --and ...some criteria based upon D and S...
> > ) S on 1 = 1
> > where B.Amount > 1000000 or S.Amount > 1000000
> > --and ...some criteria based upon D...
>
> > /* Create schema */
> > create table tblDepartment (
> > DepartmentID int not null, Name varchar(100) not null,
> > constraint pkDepartment primary key (DepartmentID))
> > create table tblBudget (
> > DepartmentID int not null, Year int not null, Amount int not
> > null,
> > constraint pkBudget primary key (DepartmentID, Year))
> > create table tblSpending (
> > DepartmentID int not null, Year int not null, Amount int not
> > null,
> > constraint pkSpending primary key (DepartmentID, Year))
> > go
>
> > /* Pretend this view is very complex/costly */
> > create view vwDepartment as select * from tblDepartment
> > go
>
> > /* Create data */
> > insert into tblDepartment values (1, 'A')
> > insert into tblDepartment values (2, 'B')
> > insert into tblBudget values (1, 2000, 1000000)
> > insert into tblBudget values (1, 2001, 1000000)
> > insert into tblBudget values (1, 2002, 1000000)
> > insert into tblBudget values (2, 2000, 1000000)
> > insert into tblBudget values (2, 2001, 1000000)
> > insert into tblBudget values (2, 2002, 1000000)
> > insert into tblSpending values (1, 2000, 1000000)
> > insert into tblSpending values (1, 2001, 1000000)
> > insert into tblSpending values (1, 2002, 1000000)
> > insert into tblSpending values (2, 2000, 1000000)
> > insert into tblSpending values (2, 2001, 1000000)
> > insert into tblSpending values (2, 2002, 1000000)
>
> > /* Clean up */
> > drop table tblDepartment
> > drop table tblBudget
> > drop table tblSpending
> > drop view vwDepartment- Hide quoted text -
>
> - Show quoted text -

Roy Harvey

3/31/2007 5:00:00 PM

0

In theory this one should only use the view once, but the optimizer
will do what it will do, so there are no guarantees. Worth a try
though.

SELECT *
FROM (SELECT D.Name,
(select sum(B.Amount)
from tblBudget B
where B.DepartmentID = D.DepartmentID
--and ...some criteria based upon D and B...
) AS AmountB,
(select sum(S.Amount)
from tblSpending S
where S.DepartmentID = D.DepartmentID
--and ...some criteria based upon D and S...
) AS AmountS
FROM vwDepartment D
--where ...some criteria based upon D...
) AS X
WHERE (AmountB > 1000000 or AmountS > 1000000)

We might have been able to get away without the surrounding SELECT by
using a HAVING clause to test AmountB and AmountS, but I was not sure
that would work. There was no question that putting it all in a
derived table and performing that test in the outer query would work,
so that is what I did.

Roy Harvey
Beacon Falls, CT

On 30 Mar 2007 21:24:16 -0700, "steventhrasher42@hotmail.com"
<steventhrasher42@hotmail.com> wrote:

>I have a query that references a costly view as well as two derived
>tables that also need to reference the same costly view. The question
>is - is there a way to get this information back in one query while
>avoiding having to use the view multiple times? Perhaps SQL does not
>support this, and I just need to resort to denormalizing some data or
>such.
>
>Sample of problem SQL below, plus schema and data. Any ideas
>appreciated. Thanks.
>
>/* THE PROBLEM - Complex/costly view vwDepartment must be repeated
>multiple times. */
> select D.Name, B.Amount, S.Amount
> from vwDepartment D
> left join (
> select D.DepartmentID, sum(B.Amount) as Amount
> from vwDepartment D
> join tblBudget B on B.DepartmentID = D.DepartmentID
> --where ...some criteria based upon D and B...
> group by D.DepartmentID
> ) B on B.DepartmentID = D.DepartmentID
> left join (
> select D.DepartmentID, sum(S.Amount) as Amount
> from vwDepartment D
> join tblSpending S on S.DepartmentID = D.DepartmentID
> --where ...some criteria based upon D and S...
> group by D.DepartmentID
> ) S on S.DepartmentID = D.DepartmentID
> where (B.Amount > 1000000 or S.Amount > 1000000)
> --and ...some criteria based upon D...
>
>/* Wish this would work - ability to reference outer columns in
>derived table. */
> select D.Name, B.Amount, S.Amount
> from vwDepartment D
> left join (
> select sum(B.Amount) as Amount
> from tblBudget B
> where B.DepartmentID = D.DepartmentID
> --and ...some criteria based upon D and B...
> ) B on 1 = 1
> left join (
> select sum(S.Amount) as Amount
> from tblSpending S
> where S.DepartmentID = D.DepartmentID
> --and ...some criteria based upon D and S...
> ) S on 1 = 1
> where B.Amount > 1000000 or S.Amount > 1000000
> --and ...some criteria based upon D...
>
>/* Create schema */
> create table tblDepartment (
> DepartmentID int not null, Name varchar(100) not null,
> constraint pkDepartment primary key (DepartmentID))
> create table tblBudget (
> DepartmentID int not null, Year int not null, Amount int not
>null,
> constraint pkBudget primary key (DepartmentID, Year))
> create table tblSpending (
> DepartmentID int not null, Year int not null, Amount int not
>null,
> constraint pkSpending primary key (DepartmentID, Year))
> go
>
> /* Pretend this view is very complex/costly */
> create view vwDepartment as select * from tblDepartment
> go
>
>/* Create data */
> insert into tblDepartment values (1, 'A')
> insert into tblDepartment values (2, 'B')
> insert into tblBudget values (1, 2000, 1000000)
> insert into tblBudget values (1, 2001, 1000000)
> insert into tblBudget values (1, 2002, 1000000)
> insert into tblBudget values (2, 2000, 1000000)
> insert into tblBudget values (2, 2001, 1000000)
> insert into tblBudget values (2, 2002, 1000000)
> insert into tblSpending values (1, 2000, 1000000)
> insert into tblSpending values (1, 2001, 1000000)
> insert into tblSpending values (1, 2002, 1000000)
> insert into tblSpending values (2, 2000, 1000000)
> insert into tblSpending values (2, 2001, 1000000)
> insert into tblSpending values (2, 2002, 1000000)
>
>/* Clean up */
> drop table tblDepartment
> drop table tblBudget
> drop table tblSpending
> drop view vwDepartment

Alejandro Mesa

3/31/2007 5:40:00 PM

0

steventhrasher42@hotmail.com,

You are right.

;with cte_1
(
select
D.DepartmentID, sum(B.Amount) as B_Amount
from
dbo.tblDepartment as D
left join
dbo.tblBudget as B
on B.DepartmentID = D.DepartmentID
where
...some criteria based upon D and B...
group by
D.DepartmentID
),
cte_2
(
select
D.DepartmentID, sum(S.Amount) as S_Amount
from
dbo.tblDepartment as D
left join
dbo.tblSpending as S
on S.DepartmentID = D.DepartmentID
where
...some criteria based upon D and S...
group by
D.DepartmentID
)
select
D.DepartmentID,
D.Name,
b.B_Amount,
s.S_Amount
from
dbo.tblDepartment as D
left join
cte_1 as b
on D.DepartmentID = b.DepartmentID
left join
cte_2 as s
on D.DepartmentID = s.DepartmentID
where
b.B_Amount > 1000000 or s.S_Amount > 1000000;
go


AMB


"steventhrasher42@hotmail.com" wrote:

> That'll result in a cross join between Spending and Budget amounts
> causing them to be counted more than once, or in my example they come
> out to 9,000,000 instead of 3,000,000.
>
>
> On Mar 31, 11:29 am, "Alejandro Mesa" <mesaalejan...@msn.com> wrote:
> > steventhrashe...@hotmail.com,
> >
> > Can you try:
> >
> > select
> > D.DepartmentID, D.Name, sum(B.Amount) as B_Amount, sum(S.Amount) as
> > S_Amount
> > from
> > dbo.tblDepartment as D
> > left join
> > dbo.tblBudget as B
> > on B.DepartmentID = D.DepartmentID
> > and ...some criteria based upon D and B...
> > left join
> > dbo.tblSpending as S
> > on S.DepartmentID = D.DepartmentID
> > and ...some criteria based upon D and S...
> > group by
> > D.DepartmentID, D.Name
> > having
> > sum(B.Amount) > 1000000 or sum(S.Amount) > 1000000
> > go
> >
> > Compare execution plans.
> >
> > AMB
> >
> > <steventhrashe...@hotmail.com> wrote in message
> >
> > news:1175315056.052104.315000@p15g2000hsd.googlegroups.com...
> >
> >
> >
> > >I have a query that references a costly view as well as two derived
> > > tables that also need to reference the same costly view. The question
> > > is - is there a way to get this information back in one query while
> > > avoiding having to use the view multiple times? Perhaps SQL does not
> > > support this, and I just need to resort to denormalizing some data or
> > > such.
> >
> > > Sample of problem SQL below, plus schema and data. Any ideas
> > > appreciated. Thanks.
> >
> > > /* THE PROBLEM - Complex/costly view vwDepartment must be repeated
> > > multiple times. */
> > > select D.Name, B.Amount, S.Amount
> > > from vwDepartment D
> > > left join (
> > > select D.DepartmentID, sum(B.Amount) as Amount
> > > from vwDepartment D
> > > join tblBudget B on B.DepartmentID = D.DepartmentID
> > > --where ...some criteria based upon D and B...
> > > group by D.DepartmentID
> > > ) B on B.DepartmentID = D.DepartmentID
> > > left join (
> > > select D.DepartmentID, sum(S.Amount) as Amount
> > > from vwDepartment D
> > > join tblSpending S on S.DepartmentID = D.DepartmentID
> > > --where ...some criteria based upon D and S...
> > > group by D.DepartmentID
> > > ) S on S.DepartmentID = D.DepartmentID
> > > where (B.Amount > 1000000 or S.Amount > 1000000)
> > > --and ...some criteria based upon D...
> >
> > > /* Wish this would work - ability to reference outer columns in
> > > derived table. */
> > > select D.Name, B.Amount, S.Amount
> > > from vwDepartment D
> > > left join (
> > > select sum(B.Amount) as Amount
> > > from tblBudget B
> > > where B.DepartmentID = D.DepartmentID
> > > --and ...some criteria based upon D and B...
> > > ) B on 1 = 1
> > > left join (
> > > select sum(S.Amount) as Amount
> > > from tblSpending S
> > > where S.DepartmentID = D.DepartmentID
> > > --and ...some criteria based upon D and S...
> > > ) S on 1 = 1
> > > where B.Amount > 1000000 or S.Amount > 1000000
> > > --and ...some criteria based upon D...
> >
> > > /* Create schema */
> > > create table tblDepartment (
> > > DepartmentID int not null, Name varchar(100) not null,
> > > constraint pkDepartment primary key (DepartmentID))
> > > create table tblBudget (
> > > DepartmentID int not null, Year int not null, Amount int not
> > > null,
> > > constraint pkBudget primary key (DepartmentID, Year))
> > > create table tblSpending (
> > > DepartmentID int not null, Year int not null, Amount int not
> > > null,
> > > constraint pkSpending primary key (DepartmentID, Year))
> > > go
> >
> > > /* Pretend this view is very complex/costly */
> > > create view vwDepartment as select * from tblDepartment
> > > go
> >
> > > /* Create data */
> > > insert into tblDepartment values (1, 'A')
> > > insert into tblDepartment values (2, 'B')
> > > insert into tblBudget values (1, 2000, 1000000)
> > > insert into tblBudget values (1, 2001, 1000000)
> > > insert into tblBudget values (1, 2002, 1000000)
> > > insert into tblBudget values (2, 2000, 1000000)
> > > insert into tblBudget values (2, 2001, 1000000)
> > > insert into tblBudget values (2, 2002, 1000000)
> > > insert into tblSpending values (1, 2000, 1000000)
> > > insert into tblSpending values (1, 2001, 1000000)
> > > insert into tblSpending values (1, 2002, 1000000)
> > > insert into tblSpending values (2, 2000, 1000000)
> > > insert into tblSpending values (2, 2001, 1000000)
> > > insert into tblSpending values (2, 2002, 1000000)
> >
> > > /* Clean up */
> > > drop table tblDepartment
> > > drop table tblBudget
> > > drop table tblSpending
> > > drop view vwDepartment- Hide quoted text -
> >
> > - Show quoted text -
>
>
>

steventhrasher42

3/31/2007 6:32:00 PM

0

Roy - That seems to work great! I hadn't thought of subquery's as
expressions in the select. And SQL Server seems to optimize pretty
well for it. Thx!

On Mar 31, 12:00 pm, Roy Harvey <roy_har...@snet.net> wrote:
> In theory this one should only use the view once, but the optimizer
> will do what it will do, so there are no guarantees. Worth a try
> though.
>
> SELECT *
> FROM (SELECT D.Name,
> (select sum(B.Amount)
> from tblBudget B
> where B.DepartmentID = D.DepartmentID
> --and ...some criteria based upon D and B...
> ) AS AmountB,
> (select sum(S.Amount)
> from tblSpending S
> where S.DepartmentID = D.DepartmentID
> --and ...some criteria based upon D and S...
> ) AS AmountS
> FROM vwDepartment D
> --where ...some criteria based upon D...
> ) AS X
> WHERE (AmountB > 1000000 or AmountS > 1000000)
>
> We might have been able to get away without the surrounding SELECT by
> using a HAVING clause to test AmountB and AmountS, but I was not sure
> that would work. There was no question that putting it all in a
> derived table and performing that test in the outer query would work,
> so that is what I did.
>
> Roy Harvey
> Beacon Falls, CT
>
> On 30 Mar 2007 21:24:16 -0700, "steventhrashe...@hotmail.com"
>
>
>
> <steventhrashe...@hotmail.com> wrote:
> >I have a query that references a costly view as well as two derived
> >tables that also need to reference the same costly view. The question
> >is - is there a way to get this information back in one query while
> >avoiding having to use the view multiple times? Perhaps SQL does not
> >support this, and I just need to resort to denormalizing some data or
> >such.
>
> >Sample of problem SQL below, plus schema and data. Any ideas
> >appreciated. Thanks.
>
> >/* THE PROBLEM - Complex/costly view vwDepartment must be repeated
> >multiple times. */
> > select D.Name, B.Amount, S.Amount
> > from vwDepartment D
> > left join (
> > select D.DepartmentID, sum(B.Amount) as Amount
> > from vwDepartment D
> > join tblBudget B on B.DepartmentID = D.DepartmentID
> > --where ...some criteria based upon D and B...
> > group by D.DepartmentID
> > ) B on B.DepartmentID = D.DepartmentID
> > left join (
> > select D.DepartmentID, sum(S.Amount) as Amount
> > from vwDepartment D
> > join tblSpending S on S.DepartmentID = D.DepartmentID
> > --where ...some criteria based upon D and S...
> > group by D.DepartmentID
> > ) S on S.DepartmentID = D.DepartmentID
> > where (B.Amount > 1000000 or S.Amount > 1000000)
> > --and ...some criteria based upon D...
>
> >/* Wish this would work - ability to reference outer columns in
> >derived table. */
> > select D.Name, B.Amount, S.Amount
> > from vwDepartment D
> > left join (
> > select sum(B.Amount) as Amount
> > from tblBudget B
> > where B.DepartmentID = D.DepartmentID
> > --and ...some criteria based upon D and B...
> > ) B on 1 = 1
> > left join (
> > select sum(S.Amount) as Amount
> > from tblSpending S
> > where S.DepartmentID = D.DepartmentID
> > --and ...some criteria based upon D and S...
> > ) S on 1 = 1
> > where B.Amount > 1000000 or S.Amount > 1000000
> > --and ...some criteria based upon D...
>
> >/* Create schema */
> > create table tblDepartment (
> > DepartmentID int not null, Name varchar(100) not null,
> > constraint pkDepartment primary key (DepartmentID))
> > create table tblBudget (
> > DepartmentID int not null, Year int not null, Amount int not
> >null,
> > constraint pkBudget primary key (DepartmentID, Year))
> > create table tblSpending (
> > DepartmentID int not null, Year int not null, Amount int not
> >null,
> > constraint pkSpending primary key (DepartmentID, Year))
> > go
>
> > /* Pretend this view is very complex/costly */
> > create view vwDepartment as select * from tblDepartment
> > go
>
> >/* Create data */
> > insert into tblDepartment values (1, 'A')
> > insert into tblDepartment values (2, 'B')
> > insert into tblBudget values (1, 2000, 1000000)
> > insert into tblBudget values (1, 2001, 1000000)
> > insert into tblBudget values (1, 2002, 1000000)
> > insert into tblBudget values (2, 2000, 1000000)
> > insert into tblBudget values (2, 2001, 1000000)
> > insert into tblBudget values (2, 2002, 1000000)
> > insert into tblSpending values (1, 2000, 1000000)
> > insert into tblSpending values (1, 2001, 1000000)
> > insert into tblSpending values (1, 2002, 1000000)
> > insert into tblSpending values (2, 2000, 1000000)
> > insert into tblSpending values (2, 2001, 1000000)
> > insert into tblSpending values (2, 2002, 1000000)
>
> >/* Clean up */
> > drop table tblDepartment
> > drop table tblBudget
> > drop table tblSpending
> > drop view vwDepartment- Hide quoted text -
>
> - Show quoted text -

--CELKO--

3/31/2007 6:48:00 PM

0

>> I should have mentioned that I tried that. The optimizer treats a CTE just like a view or derived table. In other words, it expands it out completely each time you reference it. <<

This is a weakness in the current optimizer. Other products see if
the CTE should be materialized or expanded in line. They are also
smart enmough to factor out common table expressions instead of
blindly adding them to the parse tree. SQL Server will eventually
catch up (I hope), so put that CTE code into a comment for the guy who
has to maintain this after you are gone.

Steve Dassin

3/31/2007 8:05:00 PM

0

Show that you can APPLY yourself -:)

"Roy Harvey" <roy_harvey@snet.net> wrote in message
news:an4t031pnda25j8dul65397i99rd7uq2n4@4ax.com...
> In theory this one should only use the view once, but the optimizer
> will do what it will do, so there are no guarantees. Worth a try
> though.
>
> SELECT *
> FROM (SELECT D.Name,
> (select sum(B.Amount)
> from tblBudget B
> where B.DepartmentID = D.DepartmentID
> --and ...some criteria based upon D and B...
> ) AS AmountB,
> (select sum(S.Amount)
> from tblSpending S
> where S.DepartmentID = D.DepartmentID
> --and ...some criteria based upon D and S...
> ) AS AmountS
> FROM vwDepartment D
> --where ...some criteria based upon D...
> ) AS X
> WHERE (AmountB > 1000000 or AmountS > 1000000)
>
> We might have been able to get away without the surrounding SELECT by
> using a HAVING clause to test AmountB and AmountS, but I was not sure
> that would work. There was no question that putting it all in a
> derived table and performing that test in the outer query would work,
> so that is what I did.
>
> Roy Harvey
> Beacon Falls, CT
>
> On 30 Mar 2007 21:24:16 -0700, "steventhrasher42@hotmail.com"
> <steventhrasher42@hotmail.com> wrote:
>
>>I have a query that references a costly view as well as two derived
>>tables that also need to reference the same costly view. The question
>>is - is there a way to get this information back in one query while
>>avoiding having to use the view multiple times? Perhaps SQL does not
>>support this, and I just need to resort to denormalizing some data or
>>such.
>>
>>Sample of problem SQL below, plus schema and data. Any ideas
>>appreciated. Thanks.
>>
>>/* THE PROBLEM - Complex/costly view vwDepartment must be repeated
>>multiple times. */
>> select D.Name, B.Amount, S.Amount
>> from vwDepartment D
>> left join (
>> select D.DepartmentID, sum(B.Amount) as Amount
>> from vwDepartment D
>> join tblBudget B on B.DepartmentID = D.DepartmentID
>> --where ...some criteria based upon D and B...
>> group by D.DepartmentID
>> ) B on B.DepartmentID = D.DepartmentID
>> left join (
>> select D.DepartmentID, sum(S.Amount) as Amount
>> from vwDepartment D
>> join tblSpending S on S.DepartmentID = D.DepartmentID
>> --where ...some criteria based upon D and S...
>> group by D.DepartmentID
>> ) S on S.DepartmentID = D.DepartmentID
>> where (B.Amount > 1000000 or S.Amount > 1000000)
>> --and ...some criteria based upon D...
>>
>>/* Wish this would work - ability to reference outer columns in
>>derived table. */
>> select D.Name, B.Amount, S.Amount
>> from vwDepartment D
>> left join (
>> select sum(B.Amount) as Amount
>> from tblBudget B
>> where B.DepartmentID = D.DepartmentID
>> --and ...some criteria based upon D and B...
>> ) B on 1 = 1
>> left join (
>> select sum(S.Amount) as Amount
>> from tblSpending S
>> where S.DepartmentID = D.DepartmentID
>> --and ...some criteria based upon D and S...
>> ) S on 1 = 1
>> where B.Amount > 1000000 or S.Amount > 1000000
>> --and ...some criteria based upon D...
>>
>>/* Create schema */
>> create table tblDepartment (
>> DepartmentID int not null, Name varchar(100) not null,
>> constraint pkDepartment primary key (DepartmentID))
>> create table tblBudget (
>> DepartmentID int not null, Year int not null, Amount int not
>>null,
>> constraint pkBudget primary key (DepartmentID, Year))
>> create table tblSpending (
>> DepartmentID int not null, Year int not null, Amount int not
>>null,
>> constraint pkSpending primary key (DepartmentID, Year))
>> go
>>
>> /* Pretend this view is very complex/costly */
>> create view vwDepartment as select * from tblDepartment
>> go
>>
>>/* Create data */
>> insert into tblDepartment values (1, 'A')
>> insert into tblDepartment values (2, 'B')
>> insert into tblBudget values (1, 2000, 1000000)
>> insert into tblBudget values (1, 2001, 1000000)
>> insert into tblBudget values (1, 2002, 1000000)
>> insert into tblBudget values (2, 2000, 1000000)
>> insert into tblBudget values (2, 2001, 1000000)
>> insert into tblBudget values (2, 2002, 1000000)
>> insert into tblSpending values (1, 2000, 1000000)
>> insert into tblSpending values (1, 2001, 1000000)
>> insert into tblSpending values (1, 2002, 1000000)
>> insert into tblSpending values (2, 2000, 1000000)
>> insert into tblSpending values (2, 2001, 1000000)
>> insert into tblSpending values (2, 2002, 1000000)
>>
>>/* Clean up */
>> drop table tblDepartment
>> drop table tblBudget
>> drop table tblSpending
>> drop view vwDepartment