我由客戶檔撈一些屬於等級1的資料放入temp暫存檔,然後再由temp一筆筆去取得客戶的累積消費額度.總共有數萬多筆,需花2分30秒(很慢) 請教諸位前輩能否撥空幫小弟想想有無其它的節省時間處理資料方法, 謝謝您們!

declare @yymm char(6)
set @yymm='200405'
declare @result TABLE (  csno char(9)   primary key , name char(30),mount  int)
BEGIN
  declare @level char(1)
  declare @temp TABLE(csno char(9))
  declare @CusNo char(11)
  set @level='1'
  declare book_cursor cursor for 
     select CusNo from custom where xlevel=@level
  open book_cursor
  FETCH NEXT FROM book_cursor into @CusNO
  while (@@FETCH_STATUS=0)
  begin
    insert @result
    select @CusNo as csno,
              (Select Name from custom where CusNo=@CusNo) as name,
              ( select sum(mount) as score from GetConsumeList(@CusNo,@yymm) ) as mount
    --print @CusNo
    FETCH NEXT FROM book_cursor into @CusNO
  end
  close book_cursor
  DEALLOCATE book_cursor
END    select * from @result

發表人 - pedro 於 2004/06/03 18:39:59

將資料庫切n 個等份 開n個執行緒去找 想過沒作過~^ ^ 不知道可不可行 -------------------------------------- 搞不懂!搞不懂!永遠都搞不懂!! 發表人 - mine 於 2004/06/03 19:08:57

Hi, 應該用一個 sql command 就可以取出了吧, 試試:    

select a.cusno, a.name, b.mount from 
  custom a, 
  (select CusNo, sum(GetConsumeList(CusNo, '200405')) as mount from custom where xlevel = '1' group by CusNo) b
where a.cusno = b.cusno

謝謝timhuang大大,經思量後改成這樣

--取得這個月某個等級客戶累計消費額
--0001234 王小村   123
--0004321 陳丁山   321
(1) declare @yymm char(6)
(2) declare @level char(1)
(3) set @yymm='200404'
(4) set @level='1'
(5) select a.CusNo, a.Name, b.amount from 
(6)  custom a, 
(7)   (select sum(amount) as amount from GetConsumeList(CusNo, @yymm)) where xlevel = @level and csno=CusNo) b
(8) where a.CusNo = b.csno

第7行的GetConsumeList(CusNo,@yymm)會產生'CusNo' is not a recognized OPTIMIZER LOCK HINTS option. 不知道如何更改? 再請教....謝謝 如果前面挑出來, 可以帶給後面當參數, 那該多好 發表人 - pedro 於 2004/06/04 09:32:07 發表人 - pedro 於 2004/06/04 12:31:32

1.紅字是對效能比較有關鍵的地方，我的建議是你所用的搜尋都先Create temp table，撈資料撈一次就好，後續運作都用temp table，觀念是在於減少對資料庫存取。    2.cursor不一定跑得比純select語法慢喔，不過兩者還是要試試。    

declare @yymm char(6)
set @yymm='200405'
declare @result TABLE (  csno char(9)   primary key , name char(30),mount  int)
BEGIN
  declare @level char(1)
  declare @temp TABLE(csno char(9))
  declare @CusNo char(11)
  declare @name varchar(10)
  set @level='1'
  declare book_cursor cursor for 
     select CusNo,Name from custom where xlevel=@level
  open book_cursor
  FETCH NEXT FROM book_cursor into @CusNO,@Name
  while (@@FETCH_STATUS=0)
  begin
    insert @result
    select @CusNo as csno,
              @Name as name,
              ( select sum(mount) as score from GetConsumeList(@CusNo,@yymm) ) as mount
    --print @CusNo
    FETCH NEXT FROM book_cursor into @CusNO,@Name      end
  close book_cursor
  DEALLOCATE book_cursor
END

發表人 - StrongLemon 於 2004/06/04 11:32:55

請教你, GetConsumeList 是回傳什麼性質的資料呢? 是不是資料集呢?

GetConsumeList傳回的是該客戶累計到某個月的消費清單

OrderNo   amount xlevel 
200302001 10    1 
.
.
200402001 7000  2

200302該客戶以200302001訂單等級1消費, 到200402時, 該客戶以等級2消費,累計消費額為7000元 發表人 - pedro 於 2004/06/04 12:28:43 發表人 - pedro 於 2004/06/04 12:30:12

StrongLemon大大的辦法,可節省大約五分之一的時間(30秒) 不過還是要二分鐘, client端會逾時 不知道產生這份清單還有沒有其它的辦法? user要枯等(沒有進度顯示)二分鐘那是很慘的... 像這類的分析報表大家都是怎麼做的? 請提供小弟意見,謝謝..

若是回傳為結果集的 function , 應該就是要配合 cursor 來進行才行, 否則最好是將 function 展開, 再組合回你要分析的 query !! 至於效能要如何提昇, 得看你的 index 及實體資料的狀況!

引言: StrongLemon大大的辦法,可節省大約五分之一的時間(30秒) 不過還是要二分鐘, client端會逾時 不知道產生這份清單還有沒有其它的辦法? user要枯等(沒有進度顯示)二分鐘那是很慘的... 像這類的分析報表大家都是怎麼做的? 請提供小弟意見,謝謝..

剛才自己隨機建立資料50000筆..100個客戶.. 測試結果2s..差別沒有GetConsumeList(@CusNo,@yymm) 我想會慢的原因應該在於GetConsumeList(@CusNo,@yymm)這邊吧.. 你試試跑單一客戶GetConsumeList(@CusNo,@yymm)要花費多少時間.. 還是說你肯給這部分的Table結構(SQL script)跟測試資料(Txt File).. 讓大家來試試看.. 另外你可以一個客戶一個客戶跑..一個客戶跑一次SQL.. 雖然會更慢..但可以弄出個ProgressBar給客戶看.. 也不至於time out 另外一點..MSSQL的DB檔如果達到10G以上的話.. 那效能會變的非常低..

若您 GetConsumeList 沒做其他特別的處理 是不是不大需要逐cursor處理 不知類似這樣的感覺合不合用.. 若那堆交易紀錄處理起來複雜耗時又運算模式固定的話 說不定可弄個類似月結的table把處理時間分散掉.. 個人想法.. < class="code">select l.cusno,c.cusname,sum(l.amount) from consumeList l,custom c where l.cusno=c.cusno and c.xlevel=@level and left(l.orderno,6)=@yymm and l.xlevel=@level group by l.cusno,c.cusname

感謝timhuang、StrongLemon、peipei36大大的回應