SQL存储过程 循环VS游标 ●请教●
特价图书:bookname,publishername,isbn,codeprice,publishdate,authors字段
books里面:bookid,bookname,isbn,authors,publishername,codeprice,discount,publishdate,gettimes字段
目的是把特价图书表里面的isbn和价格相同的图书导入到books表里面
要求:
books表里面有相同的isbn 和价格 则更新books的一条记录
books表里面有不相同的isbn 和价格 或者没有则插入新数据
特价图书 几万条数据
books 200万条数据
现在有现有的存储过程 是用游标写的.但是很慢
我用循环直接写个 但是比游标还要慢~
请各位高手 帮忙设计一下 高分送出
下面是游标代码
- SQL code
ALTER procedure [dbo].[导入图书数据_存储过程]
as
begin ---------------------存储过程开始
declare mycursor cursor for
select bookname,publishername,isbn,codeprice,publishdate,authors from 特价图书
open mycursor
declare @bookid int,@bookname nvarchar(200),@publishername nvarchar(50),@isbn nvarchar(50), @isbn9 nvarchar(50),
@codeprice money,@publishdate datetime,@authors nvarchar(200),@isbncount int,
@bookid1 int,@countfrombookid int
fetch next from mycursor into @bookname,@publishername,@isbn,@codeprice,@publishdate,@authors
select @bookid=max(bookid) from books
insert WangMingtao(bookid) values(1)
while @@fetch_status = 0
begin ------------------------while循环开始
declare @charstr varchar(11) ----检验ISBN的合法性开始
set @charstr='1234567890X'
declare @i int
declare @isbnstr nvarchar(20)
select @i=1
while @i <=len(@isbn)
begin
set @isbnstr=right(left(@isbn,@i),1)
if charindex(@isbnstr, @charstr)=0
begin
set @isbn=replace(@isbn,@isbnstr,'#')
end
set @i=@i+1
end
set @isbn=replace(@isbn,'#','')
set @isbn9=@isbn
if len(@isbn)=13
begin
set @isbn9=substring(@isbn,4,9)
end
if len(@isbn)=10
begin
set @isbn9=left(@isbn,9)
end
----检验ISBN的合法性结束
print('11')
set @bookid=@bookid+1 ----------------------bookid加一
select @isbncount=count(isbn) from books where left(isbn,9)=@isbn9 and codeprice=@codeprice
print('1')
if @isbncount <> 0
begin ---------如果在books中存在该isbn
update books set status=1,isbn=@isbn where left(isbn,9)=@isbn9 and codeprice=@codeprice
select @bookid1=bookid from books where left(isbn,9)=@isbn9 and codeprice=@codeprice
insert WangMingtao(bookid) values(@bookid1)
select @countfrombookid=count(bookid) from frombook where bookid=@bookid1 and [from]=11
if @countfrombookid <> 0
begin
update frombook set discount=80 where bookid=@bookid1 and [from]=11
end
else
begin
insert into frombook ([from],bookid,stocknum,seat,isupdated,discount) values
(11,@bookid1,5,'0',0,80)
end
end ---------如果在books中存在该isbn
else
begin ---------不存在
insert books(bookid,bookname,isbn,authors,publishername,codeprice,discount,publishdate,gettimes)
values
(@bookid,@bookname,@isbn,@authors,@publishername,@codeprice,80,@publishdate,getdate())
insert WangMingtao(bookid) values(@bookid)
insert frombook([from],bookid,stocknum,seat,isupdated,discount) values
(11,@bookid,5,'0',0,80)
end ---------不存在
fetch next from mycursor into @bookname,@publishername,@isbn,@codeprice,@publishdate,@authors
end------------------------while循环结束
close mycursor
deallocate mycursor
end -----------------存储过程结束
-----------------------
下面是我写的循环代码
- SQL code
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-----创建存储过程ALTER procedure [dbo].[导入新书]asbegin ---------------------存储过程开始declare @bookid int,@bookname nvarchar(200),@publishername nvarchar(50),@isbn varchar(50), @isbn9 nvarchar(50), @codeprice money,@publishdate datetime,@authors nvarchar(200),@isbncount int,@booksisbn int,@bookid1 int,@countfrombookid intselect @bookid=max(bookid) from booksdeclare @i
- SQL code
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo------自定义函数,检验ISBN的合法性ALTER function [dbo].[ChkIsbn](@Aisbn nvarchar(50)) returns nvarchar(50)asbegindeclare @charstr varchar(11),@isbn9 nvarchar(50)set @charstr='1234567890X'declare @i intdeclare @isbnstr nvarchar(20)select @i=1while @i<=len(@Aisbn)beginset @isbnstr=right(left(@Aisbn,@i),1) if charindex(@isbnstr, @charstr)=0 begin set @Aisbn=replace(@Aisbn,@isbnstr,'#') endset @i=@i+1endset @Aisbn=replace(@Aisbn,'#','')set @isbn9=@Aisbnif len(@Aisbn)=13set @isbn9=substring(@Aisbn,4,9)if len(@Aisbn)=10 set @isbn9=left(@Aisbn,9) return @isbn9end
[解决办法]
要那么麻烦么?直接:
这是更新的:
update books set 列1=a.列1,列2=a.列2,列3=a.列 from books b inner jion from book a on a.isbn=b.isbn and ...
这是插入的:
insert into books select 列s from bookfrom a where not exits(select 1 from book where isbn=a.isbn and ...
不行吗?
[解决办法]
既然比较都是用isbn9，为什么不在表放一个isbn9字段？
典型的设计影响性能的例子，用临时表而不用游标和循环能提高一些速度，但是很难达到最优
[解决办法]
上面的from book 和 bookfrom 在楼主的程序里好像是什么 frombook.写快了.
[解决办法]
这两个逻辑干什么硬要集中在一个过程中
实在不爽你可以放在一个sp中，没必要用循环或者游标吧
update books set 列1=a.列1,列2=a.列2,列3=a.列 from books b inner jion from book a on a.isbn=b.isbn
insert into books select 列s from book a where not exits(select 1 from book where isbn=a.isbn)
这就差不多了
[解决办法]
帮顶。
[解决办法]
[解决办法]
同意一楼的，你为什么不直接更新呢？
另外你的ISDN和价格是索引不？如果是索引，相信会更快吧？
insert into books (isbn,价格) where isbn+价格 not in ( select isbn+价格 from 特价图书 ）
update books set ...(需要更新的字段) from books a,特价图书 b where a.isbn=b.isbn and a.价格=b.价格
我觉得这样应该就可以了吧？
[解决办法]
数据库批量处理的时候，尽可能不使用循环或者游标，
你可以部分处理到临时表，然后再进行相关操作。
微软看到这样的处理代码，估计会觉得SQLServer的执行速度还是不够快哦。呵呵。
照这样的存储过程，放在老版本的Informix里，那还真是要命啊。
------解决方案--------------------
随便写了一下，没有SQL我调试，你将就看着吧。
- SQL code
create function checkISBN(@isbn nvarchar库
[解决办法]
建立了数据函数[dbo].[ChkIsbn]后，不妨这样来处理：
（请检查一下books, 世纪书缘库存这两张表是否有对isbn,codeprice的索引，如果没有的话，建立索引后速度上会提高很多）
--1)books表里面有相同的isbn 和价格 则更新books的一条记录
update t1
set status=1,isbn=t2.isbn
from books t1, 世纪书缘库存 t2
where left(t1.isbn,9)=dbo.ChkIsbn(t2.isbn)
and t1.codeprice=t2.codeprice
--2)books表里面有不相同的isbn 和价格 或者没有则插入新数据
declare @intMaxBookid int
select @intMaxBookid=max(bookid) from books
declare @tblSpecialBooks table (bookid int identity(1,1),isbn nvarchar(50))
--假定isbn为“世纪书缘库存”的主键，如果是主键为其他字段，则改为其他字段关联
insert into @tblSpecialBooks(isbn)
select isbn
from 世纪书缘库存
where dbo.ChkIsbn(t2.isbn) not in (select distinct left(t1.isbn,9) from books)
insert books(bookid,bookname,isbn,authors,publishername,codeprice,discount,publishdate,gettimes,status)
select @intMaxBookid+t2.bookid,bookname,isbn,authors,publishername,codeprice,discount,publishdate,gettimes,status
from 世纪书缘库存 t1, @tblSpecialBooks t2
where t1.isbn=t2.isbn
[解决办法]