Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 update queries using derived tables

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-04-28 : 14:08:53
Arthur writes "I am trying to optimize a query without using table variables / temp tables. Here is the original query. The problem is the table has > 35 million records and I have 15 more update queries just like this one......

select b.FISCAL_YEAR,
b.FISCAL_PERIOD,
b.Channel,
b.Customer,
sum(b.NET_AMOUNT) as [Comm_Sales]
into #FIN_Comm_Sales
from FIN_Comm_Detail200 b
where (b.FISCAL_YEAR * 100) + b.FISCAL_PERIOD > @YYYYMM
and (b.BILLTO_COMM != 0 or b.SHIPTO_COMM != 0 or b.SOLDTO_COMM != 0)
group by
b.FISCAL_YEAR,
b.FISCAL_PERIOD,
b.Channel,
b.Customer;

update temp_FIN_Comm_byChan200
set Comm_Sales = b.Comm_Sales
from #FIN_Comm_Sales b
where temp_FIN_Comm_byChan200.FISCAL_YEAR = b.FISCAL_YEAR
and temp_FIN_Comm_byChan200.FISCAL_PERIOD = b.FISCAL_PERIOD
and temp_FIN_Comm_byChan200.Channel = b.Channel
and temp_FIN_Comm_byChan200.Customer = b.Customer;

drop table #FIN_Comm_Sales

-- ///////////////////////////////////////////////////

here is what I thought would work

update temp_FIN_Comm_byChan200
set Comm_Sales = b.Comm_Sales

(
select
FISCAL_YEAR,
FISCAL_PERIOD,
Channel,
Customer,
sum(NET_AMOUNT) as Comm_Sales
from
FIN_Comm_Detail200
where
(FISCAL_YEAR * 100) + FISCAL_PERIOD > 200402
and (BILLTO_COMM != 0 or SHIPTO_COMM != 0 or SOLDTO_COMM != 0)
group by
FISCAL_YEAR,
FISCAL_PERIOD,
Channel,
Customer
) as b


where temp_FIN_Comm_byChan200.FISCAL_YEAR = b.FISCAL_YEAR
and temp_FIN_Comm_byChan200.FISCAL_PERIOD = b.FISCAL_PERIOD
and temp_FIN_Comm_byChan200.Channel = b.Channel
and temp_FIN_Comm_byChan200.Customer = b.Customer"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-28 : 14:46:22
Well you need a "from" before the derived table.

I take it your tables are indexed on
FISCAL_YEAR,
FISCAL_PERIOD,
Channel,
Customer,

(probably not in that order)

where (b.FISCAL_YEAR * 100) + b.FISCAL_PERIOD > @YYYYMM
isn't going to use an index
you could add a filter on just the year if that excludes a lot of data.

something like
and b.FISCAL_YEAR >= @YYYYMM/100




==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -