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.
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_Salesfrom FIN_Comm_Detail200 bwhere (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_byChan200set Comm_Sales = b.Comm_Salesfrom #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 workupdate temp_FIN_Comm_byChan200set Comm_Sales = b.Comm_Sales(select FISCAL_YEAR, FISCAL_PERIOD, Channel, Customer, sum(NET_AMOUNT) as Comm_Salesfrom FIN_Comm_Detail200where (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 bwhere temp_FIN_Comm_byChan200.FISCAL_YEAR = b.FISCAL_YEARand temp_FIN_Comm_byChan200.FISCAL_PERIOD = b.FISCAL_PERIODand temp_FIN_Comm_byChan200.Channel = b.Channeland 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 onFISCAL_YEAR, FISCAL_PERIOD, Channel, Customer,(probably not in that order)where (b.FISCAL_YEAR * 100) + b.FISCAL_PERIOD > @YYYYMMisn't going to use an indexyou could add a filter on just the year if that excludes a lot of data.something likeand 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. |
|
|
|
|
|
|
|