不可小视视图对效率的影响力(转)

近日为一家企业开发数据库方面的应用。有两张数据表：A和B，其中A的记录为2万条左右，而B中的数据为200万条以上，现在要求以a.id=b.id为关联进行数据筛选。

为此要求，我做了个视图View1，将a.id与b.id关联起来，运行该视图的查询，使用了2秒中就得到结果了。这时又有一特殊需求：编写存储过程，该存储过程中有一个游标的循环，该循环重复1万次，在每次循环中都使用了对View1的查询，举例如下：
循环游标体(重复1万次）
{
select @text=text from view1 where id=@ id --其中@text,@id均为变量
update form1 set text=@text where id=@id
}
执行以上的存储过程，发现竟然要花费>7分钟的运算时间，进行分析后得知效率全损耗在对view1视图的扫描上，尽管单独对该视图进行查询只需2秒，但在这种情况下即需要太多的时间。
因此改进该存储过程如下：
create table #tempTab
(
id nvarchar(20),
text nvarchar(20)
)

查询View1视图，将记录插入到#tempTab临时表中...
循环游标体(重复1万次）
{
select @text=text from #tempTab where id=@ id --其中@text,@id均为变量
update form1 set text=@text where id=@id
}
别小瞧以上的改动，此时该存储过程只花费50秒的时间。
对于上述现象，分析如下：在多次循环对某个视图进行查询时，将会耗费大量的时间，因此将该视图的数据放在内存中进行计算会大提高速度。

mydear 发表于:2007.08.08 10:36 ::分类: ( 一般分类 ) ::阅读:(0次) :: 评论 (0)

--&gt

===========================================================

分析数据库的一些方法1(转)

===========================================================

工作中，我们有时需要分析一个现有软件的数据库结构，简单的说，就是想知道两点
1 、各种数据保存在哪个表
2 、在什么情况下，表中的数据会发生更新
下面我把自己的方法写出来，如果您有更好的方法，请与我讨论。
1、为数据库中的每一个业务表建立对应的更新表
当相应业务表的数据被更新时，触发器会把更新的类型和记录写进相应的更新表
更新表的字段除了包括相应业务表的所有字段，还添加了三个字段
(1) 一个自增的ID
(2) 更新类型(I 插入;D 删除;U 更新)
(3) 更新时间

2、在数据库中建立一个总更新表
当任何一个业务表的数据被更新时，触发器会把更新的类型和表名写进总更新表，作用是快速找到当前发生数据更新的表
总更新表有四个字段
(1) 一个自增的ID
(2) 更新类型(I 插入;D 删除;U 更新)
(3) 更新的表名
(4) 更新时间
3、为每一个业务表建立三个触发器，分别对应插入、删除、修改三种操作
当业务表发生更新时，会把更新前的记录、更新后的记录、删除的记录、插入的记录写入相应更新表
为此我专门写了两个存储过程，适用于SQL Server 2000，如果您的数据库不是SQL Server 2000，也可供您参考
为了新建立的表和触发器和数据库中原有的表和触发器同名，采用了加后缀方法，比如
表名为 Users的表，相应的更新表为Users+后缀，当后缀为_1234567时，更新表的表名为Users_1234567
下面是存储过程P_Analysis和P_ClearUp的脚本
/*=========================================================================
存储过程 P_Analysis
作用
为分析建立一个总的更新表 UPDATE+后缀+后缀
为每个表建立一个更新表 原表名+后缀
为每个表建立三个触发器 TR_表名_+触发器类型(I:插入 D:删除 U:更新)+后缀
输入参数 @postfix,以免分析用表和业务表名称重复，分析用触发器和原由触发器重复
使用举例 EXEC P_Analysis ’_1234567’
============================================================================*/
CREATE PROCEDURE P_Analysis
@postfix char(8)
AS
--测试是否会和数据库原有的对象名(字段名)重复
if exists(SELECT * from sysobjects where right(name,8)=@postfix) OR exists(SELECT * from syscolumns where
right(name,8)=@postfix)
print ’对象名重复，请使用不同的后缀民名’
else
begin
--为每个表建立更新记录表
declare @TableName nvarchar(128)
declare @columns varchar(8000)
declare cur INSENSITIVE cursor
FOR
SELECT name from sysobjects where xtype=’U’ and status>0
OPEN cur
FETCH NEXT FROM cur INTO @TableName
while(@@fetch_status=0)
BEGIN
set @columns=’’
--建立更新表
EXEC(’SELECT * into ’+@TableName+@postfix+’ FROM ’+@TableName+’ WHERE 1=0’)
--为更新表增加三个字段
EXEC(’alter table ’+@TableName+@postfix + ’ add ID’+@postfix+’ INT IDENTITY(1,1),OprType’+@postfix+’
char(2),OprTime’+@postfix+’ datetime default getdate()’)
--为每个业务表建立三个触发器
SELECT @columns=@columns+’,’+name from syscolumns where ID=object_id(@TableName)
--插入触发器
EXEC(’CREATE TRIGGER TR_’+@TableName+’_I’+@postfix+’ ON ’+@TableName+’ FOR INSERT AS’+
’ INSERT UPDATE’+@postfix+@postfix+’(TableName,OprType)’+
’ VALUES(’’’+@TableName+’’’,’’I’’)’+
’ INSERT ’+@TableName+@postfix+’(OprType’+@postfix+@columns+’)’+
’ SELECT ’’I’’’+@columns+’ FROM INSERTED’)
--删除触发器
EXEC(’CREATE TRIGGER TR_’+@TableName+’_D’+@postfix+’ ON ’+@TableName+’ FOR DELETE AS’+
’ INSERT UPDATE’+@postfix+@postfix+’(TableName,OprType)’+
’ VALUES(’’’+@TableName+’’’,’’D’’)’+
’ INSERT ’+@TableName+@postfix+’(OprType’+@postfix+@columns+’)’+
’ SELECT ’’D’’’+@columns+’ FROM DELETED’)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7178747/viewspace-161528/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7178747/viewspace-161528/