SQLServer2005中的數據定時統計方法研究

王睿

摘要：在軟件系統研發過程中，數據統計匯總的效率始終是難點，如何快速有效地將零碎的業務數據統計匯總成報表數據，成為數據庫開發人員的研究重點。文章介紹了一種基于視圖+存儲過程+靜態數據表+任務的方式，對報表數據進行定時統計匯總。

關鍵詞：SQLServer2005；數據定時統計方法；業務數據；報表數據；數據庫 文獻標識碼：A

中圖分類號：TP311 文章編號：1009-2374（2016）17-0021-03 DOI：10.13535/j.cnki.11-4406/n.2016.17.009

1 概述

SQLServer數據庫是Microsoft公司推出的關系型數據庫管理系統。具有使用方便可伸縮性好與相關軟件集成程度高等優點，可跨越從運行Microsoft Windows 98的膝上型電腦到運行Microsoft Windows 2012的大型多處理器的服務器等多種平臺使用。目前最新版本是SQLServer2012。本文主要針對日常工作中比較常用版本SQLServer2005進行分析，研究其數據定時統計的相關方法，并附實例加以說明。

2 軟件項目數據統計中遇到的問題

在日常工作中，我們會經常遇到類似于這樣的問題：“一張統計報表需要從十幾張，甚至幾十張業務基礎數據表中匯總。”傳統的處理方法是直接拼接SQL查詢語句，然后將查詢結果傳遞給報表控件，這樣的處理方法看似沒有問題，但隨著業務數據的不斷增加，用戶打開報表的速度越來越慢，最后報表直接無法打開或者打開需要很長時間，從而導致整個項目的用戶體驗大打折扣。

3 數據定時統計方法的研究

面對這一問題，我們可以采用數據定時統計的方法將基礎業務數據的統計結果保存在一張業務表中，然后數據報表直接從統計結果表中獲取數據，如此一來，數據統計效率大大提升，同時也簡化了報表編碼的復雜度。

我們在SQLServer2005中編寫基礎業務數據統計視圖，然后建立與視圖結構一致的數據表，同時編寫存儲過程將視圖統計的結果性數據同步到結果表中，最后采用SQLServer2005的定時任務，定期執行同步數據的存儲過程即可，具體實現方法及步驟，如圖1所示。

案例說明：

定時統計各單位累計消費情況：

第一步：創建視圖

CREATE VIEW [V_ORG_ CONSUME]

AS

SELECT O.CODE_，O.NAME_， SUM（C.MONEY_） AS TOTALMONEY_

FROM T_ CONSUME AS C

LEFT JOIN T_ACTOR AS A ON A.ID=C.ACTORID_

LEFT JOIN T_ACTOR_ORG AS AO A.ID=AO.ACTORID_

LEFT JOIN T_ORG AS O ON AO.ORGID_=O.ID

GROUP BY O.ID，O.CODE_，O.NAME_

ORDER BY ID

第二步：創建統計結果表

CREATE TABLE [TV_ORG_ CONSUME]（

[ID] [bigint] NOT NULL，

[CODE_] [varchar]（100） NULL，

[NAME_] [varchar]（200） NULL，

[TOTALMONEY_] [float]NULL

PRIMARY KEY CLUSTERED

（[ID]ASC）WITH（PAD_INDEX=OFF，STATISTICS_NORECOMPUTE=OFF， IGNORE_DUP_KEY = OFF， ALLOW_ROW_LOCKS= ON， ALLOW_PAGE_LOCKS = ON） ON [PRIMARY]

） ON [PRIMARY]

第三步：創建數據同步存儲過程

數據同步插入存儲過程：

Create procedure [P_ORG_ CONSUME_INSERT]

@ID int，

@CODE_ varchar，

@NAME_ varchar，

@TOTALMONEY_ decimal（18， 10）

as

declare @sum int

begin tran

insert into TV_ORG_ CONSUME（ID，CODE_，NAME_， TOTALMONEY_）

values（@ID，@ CODE_，@ NAME_，@ TOTALMONEY_）

select @sum = count（*） from TV_ ORG_ CONSUME where （ID=@ID）

if（@sum>1）

begin

raiserror（‘記錄已經存在'，16，8）

rollback tran——回滾事物

end

else

commit tran——提交事務

創建返回游標的存儲過程：

Create procedure [P_ ORG_ CONSUME_GET]

@VChcekCursor Cursor Varying Output

As

Set @VChcekCursor = Cursor

For

SelectID， CODE_，NAME_， TOTALMONEY_

from V_ORG_ CONSUME

Open @VChcekCursor

執行數據同步插入的存儲過程：

Create procedure[P_ORG_ CONSUME_INSERT_EXCUTE]

As

Declare @ID int

Declare @ CODE_varchar

Declare @ NAME_varchar

Declare @ TOTALMONEY_decimal（18， 10）

Exec P_ORG_CONSUME_GET @VChcekCursor out

Fetch Next From @VChcekCursor

InTo @ID，@ CODE_，@ NAME_，@ TOTALMONEY_

While（@@Fetch_Status = 0）

Begin

exec[P_ORG_CONSUME_INSERT]

@ID，@ CODE_，@ NAME_，@ TOTALMONEY_

Fetch Next From @VChcekCursor

InTo @ID，@ CODE_，@ NAME_，@ TOTALMONEY_

End

Close @VChcekCursor

Deallocate@VChcekCursor

Go

第四步：創建任務定時執行存儲過程

DECLARE @jobId BINARY（16）

EXEC @ReturnCode=

msdb.dbo.sp_add_job @job_name=NJob_Consume_Excute_Procedure，

@enabled=1，

@notify_level_eventlog=2，

@notify_level_email=0，

@notify_level_netsend=0，

@notify_level_page=0，

@delete_level=0，

@description=N無描述。，

@category_name=N[Uncategorized （Local）]，

@owner_login_name=Nadmin， @job_id = @jobId OUTPUT--數據庫登錄帳號

IF （@@ERROR <> 0 OR @ReturnCode <> 0） GOTO QuitWithRollback

EXEC @ReturnCode =

msdb.dbo.sp_add_jobstep @job_id=@jobId， @step_name=NDELETE_DATA，

@step_id=1，

@cmdexec_success_code=0，

@on_success_action=3，

@on_success_step_id=0，

@on_fail_action=2，

@on_fail_step_id=0，

@retry_attempts=0，

@retry_interval=0，

@os_run_priority=0， @subsystem=NTSQL，

@command=N DELETE FROM TV_ORG_CONSUME ‘，——清除臨時表中結果數據

@database_name=NTESTDB， ——數據庫名稱

@output_file_name=NC：＼TEMP＼T_SQL＼DELETE_ LOG.txt，——刪除數據日志

@flags=22

IF （@@ERROR <> 0 OR @ReturnCode <> 0） GOTO QuitWithRollback

EXEC @ReturnCode =

msdb.dbo.sp_add_jobstep@job_id=@jobId， @step_name=NSYNCHRONIZATION_DATA，

@step_id=2，

@cmdexec_success_code=0，

@on_success_action=1，

@on_success_step_id=0，

@on_fail_action=2，

@on_fail_step_id=0，

@retry_attempts=0，

@retry_interval=0，

@os_run_priority=0， @subsystem=NTSQL，

@command=N

DECLARE @RC int

EXECUTE @RC=[P_ ORG_CONSUME_INSERT_EXCUTE] ——執行數據同步存儲過程

GO，

@database_name=NTESTDB， ——數據庫名稱

@output_file_name=NC：＼TEMP＼T_SQL＼SYNCHRONIZATION_lOG.txt，——日志存放目錄

@flags=22

IF （@@ERROR <> 0 OR @ReturnCode <> 0） GOTO QuitWithRollback

EXEC @ReturnCode=msdb.dbo.sp_update_job @job_id=@jobId， @start_step_id=1

IF （@@ERROR <> 0 OR @ReturnCode <> 0） GOTO QuitWithRollback

EXEC @ReturnCode=msdb.dbo.sp_add_jobschedule @job_id=@jobId， @name=NEXCUTE，

@enabled=1，

@freq_type=4，

@freq_interval=1，

@freq_subday_type=8，

@freq_subday_interval=6，

@freq_relative_interval=0，

@freq_recurrence_factor=0，

@active_start_date=20160320，

@active_end_date=99991231，

@active_start_time=203000，——每天晚上八點半定時任務開始執行

@active_end_time=210000——每天晚上九點定時任務執行結束

IF （@@ERROR <> 0 OR @ReturnCode <> 0） GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId， @server_name = N（local）

IF （@@ERROR <> 0 OR @ReturnCode <> 0） GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback：

IF （@@TRANCOUNT > 0） ROLLBACK TRANSACTION

EndSave：

4 結語

采用存儲過程定期的將視圖中的數據同步至靜態表的數據匯總方法適合與數據量較大的業務處理上，例如銀行財務扎賬、各類成本匯總等方面均有明顯的優勢，其充分利用了數據庫的數據處理能力，減少了應用程序對數據的二次處理，同時大大提高了報表的查詢效率。對于小規模的數據統計，不建議采用此方法進行數據統計匯總。

同時采用此方法也存在一些問題，例如：定時任務執行過程中有業務數據產生，業務數據不會被統計到結果中；用戶不能看到實時的統計結果等。

參考文獻

[1] 明日科技.SQL Server從入門到精通[M].北京：清華大學出版社，2012.

[2] [美]Ben Forta，鐘鳴，劉曉霞.SQL必知必會[M].北京：人民郵電出版社，2013.

[3] [美]西爾伯沙茨，楊冬青.數據庫系統概念[M].北京：機械工業出版社，2012.

（責任編輯：黃銀芳）