觉得自己的ETL过程很慢（存储过程实现） 来讨论一下 大家有什么高招?

[Reasoncool]

我大概说一下我的ETL，用SQL编程实现的，我做过一个网站流量统计项目，比如我想获得每个访问者来源地区，我的处理方式是利用游标去处理上一个小时的数据，遍历每一条数据查询IP数据库获取地区编号。现在数据量每小时3、4万，处理时间3分钟。如果数据量再大，我估计我的处理方式就太慢了，不能应付。

大家有什么好办法？

班主 使编程实现 讲讲经验好吗？ 万分感谢！！！！

[pxboy]

你说的几个例子基本都可以这样实现。
数据转换的应用有很多，方式也有很多，到底采用哪种方式效率最高，要看具体情况，函数并不能实现所有的转换，但你这样用游标绝对是效率非常低的。许多时候ETL工具可以完成大多数功能，有时就只能自己写应用程序来实现了。

[Reasoncool]

群里人 给的评价:
张骞<[e-mail]> 09:44:03
典型的数据挖掘个案，你现在做的还只是基于关系数据库的报表查询；
我觉得应该是将数据抓过来后，做成多维数据库（如果是SQL SERVER，可以安装OLAP来实现），借助前端工具，速度肯定很快

谢谢!

[yuhr61]

听起来有些空洞，3-4万数据要3分钟好像是有点慢，说得详细点，大伙可也好交流，群里人 说得很对
，ETL 只是中间过程，对数据处理和清洗，不是直接的结果。你们应该只是做数据集市而非数据仓库吧。

[pxboy]

一条查询能完成的事情，为何用游标？直接通过IP连接得到地区编号，做一个聚合就行了，为何要一条一条地遍历？

[Reasoncool]

处理IP使这个项目中的一个维度处理.我用SQL SERVER 得AS做出的立方体多维数据,不算是数据仓库? 大家事实表的维度键不是这么处理来的?

[Reasoncool]

[Quote=引用 3 楼 pxboy 的回复:]
一条查询能完成的事情，为何用游标？直接通过IP连接得到地区编号，做一个聚合就行了，为何要一条一条地遍历？
[/Quote]

不太明白
group by 吗 我的IP库是根据IP段确定地区的. 大于最小IP小与最大IP

我要把它记到事实表的

[pxboy]

就是用group by，用一个函数得到地区码，用游标是最慢的方式了

[Reasoncool]

嗯 多谢 你提醒了我 这样可以节省到四分之一的时间 呵呵

但是其他操作 例如: 我要根据URL分析出比较特殊的维度,是不是指定监控的访问页面或叶面参数分析或页面类型(内容\列表\频道) 就得用游标了吧 ?


用select 函数(url) as 维度 from tb ? 效率 差不多吧? 还有什么好方法?

[Reasoncool]

我测试过了 30万数据

SQL查询方法:11秒
SELECT [dbo].[FN_GetNewsId] ([url_join]) as newsid
FROM TempOpenPV

游标方法:28秒

DECLARE @i int
set @i=0;
DECLARE @ipno varchar(500)
--定义游标
DECLARE Infosss CURSOR FOR

SELECT [url_join]
FROM [dbo].[TempOpenPV]
OPEN Infosss

FETCH NEXT FROM Infosss
INTO @ipno
WHILE @@FETCH_STATUS = 0
BEGIN
set @i=[dbo].[FN_GetNewsId] (@ipno)
FETCH NEXT FROM infosss
INTO @ipno
END
--关闭游标
CLOSE infosss
DEALLOCATE infosss

[owx]

做一个地区编码IP映射表,从事实表中根据IP,直接得到,组合成视图

[华芸智森]

1。将IP全部处理成整数。

ALTER    FUNCTION DBO.FUN_IP2INT( 
@IP CHAR(20) 
)RETURNS BIGINT 
AS 
BEGIN 
DECLARE @RE BIGINT 
SET @RE=0 
SELECT @RE=@RE+LEFT(@IP,CHARINDEX('.',@IP+'.')-1)*[ID] 
,@IP=STUFF(@IP,1,CHARINDEX('.',@IP+'.'),'') 
FROM( 
SELECT [ID]=CAST(16777216 AS BIGINT) 
UNION ALL SELECT 65536 
UNION ALL SELECT 256 
UNION ALL SELECT 1)A 
RETURN(@RE) 
END 

2。有一个开始的IP列（整型）int_begip，一个结束的IP列（整形）int_endip 。还有一个 DiffIp(两IP整形差值）int_diffip
在记录IP时，将IP全部转化，并计算出差值。

3。一般情况下，一个IP有可能匹配多个IP段的。如：
段一 128.5.6.1 128.5.6.230
段二 128.5.6.8 128.5.6.30
一个值是 128.5.6.20 ,应该匹配的是 128.5.6.8 128.5.6.30 ,而不是 128.5.6.1 128.5.6.230 .
要取出最匹配的IP,必须要用到IP差值.

  DECLARE @INT_IP INT 
  SET @INT_IP=DBO.FUN_IP2INT('128.5.6.20') 
  SELECT top 1 Var_IP from ( 
      select Var_IP,Int_DiffIp from IPTABLE WHERE @INT_IP>= INT_BEGIP AND @INT_IP <=INT_ENDIP 
  ) as t order by  Int_DiffIp 

[Reasoncool]

感谢yuhr61，pxboy，owx，MSTOP 地回答。 我受益匪浅！

大家都在说 我举的例子从这个例子 改变了我以前编程的习惯，是程序的效率提高了很多。

大家都是用存储过程、自定义函数在原始数据中分解出各维度值吗？ 我就是这么做的 不知道是否正确 是否合理 希望大家提点意见 帮助成长 赫赫

[zhangyanxxxx]

在做ETL的时候,如果涉及到了大量的计算或复杂的判断逻辑等内容时,最好编写外部应用程序,而不使用TSQL存储过程,尤其是游标,性能非常低下,因为CLR程序更适合处理复杂计算和多分支判断情况,你说这种情况我以前也遇见过,你不防试一下

[zhangyanxxxx]

另外利用MOLAP+ROLAP这种解决方案也可以解决你说的问题,这是有成功案例的