其中 Tweeter,Tweets,TweetsCount都存在大量数据，最低千万级的
加了 nolock不行，大家还有什么好的解决办法啊
ALTER PROC [dbo].[TweetsAddOrUpdate]
@WebSiteId int,
@TweeterId bigint,
@WebTweetsId nvarchar(50),
@SourceId bigint,
@WebSourceId nvarchar(50),
@Content nvarchar(4000),
@CutWordStatus bit,
@OriginalText nvarchar(4000),
@ForwardCount int,
@CommentCount int,
@TweeterPostType nvarchar(100),
@TweeterPostTime datetime,
@TweetsType int,
@Location nvarchar(50),
@UpdateTime datetime,
@TweetsImportance int,
@ProvinceName nvarchar(50),
@CityName nvarchar(50)
as
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
declare @ProvinceId int
declare @cityId int
select @ProvinceId=CityId from City(nolock) where Name=@ProvinceName
select @cityId=CityId from City(nolock) where Name=@CityName and ParentId=@ProvinceId
DECLARE @TweetsId bigint
IF not exists (select TweetsId from Tweets(nolock) where WebTweetsId=@WebTweetsId and WebSiteId=@WebSiteId)
begin
INSERT INTO [Tweets]
([WebSiteId],[TweeterId],[WebTweetsId],[SourceId]
,[WebSourceId],[Content],[OriginalText]
,[ForwardCount],[CommentCount],[TweeterPostType]
,[TweeterPostTime],[TweetsType],[Location]
,[UpdateTime],TweetsImportance,ProvinceId,CityId)
VALUES
(@WebSiteId,@TweeterId,@WebTweetsId,@SourceId
,@WebSourceId,@Content,@OriginalText
,@ForwardCount,@CommentCount,@TweeterPostType
,@TweeterPostTime,@TweetsType,@Location
,getDate(),@TweetsImportance,@ProvinceId,@cityId)
SET @TweetsId=@@identity
end
else
begin
select @TweetsId=TweetsId from Tweets(nolock) where WebTweetsId=@WebTweetsId and WebSiteId=@WebSiteId
update Tweets set ForwardCount=@ForwardCount,CommentCount=@CommentCount,
UpdateTime=getDate(),TweetsImportance=@TweetsImportance where TweetsId=@TweetsId
end
update Tweets set SourceId=@TweetsId where WebSourceId=@WebTweetsId
if exists (select TweetsId from TweetsCounts(nolock)
where TweetsId=@TweetsId and CONVERT(nvarchar(10),UpdateTime,112)
=cONVERT(nvarchar(10),GETDATE(),112)
)
begin
update TweetsCounts set ForwardCount=@ForwardCount,CommentCount=@CommentCount where
CONVERT(nvarchar(10),UpdateTime,112)
=cONVERT(nvarchar(10),GETDATE(),112)
and TweetsId=@TweetsId
end
else
begin
INSERT INTO [TweetsCounts]
([TweetsId]
,[ForwardCount]
,[CommentCount]
,[UpdateTime])
VALUES
(@TweetsId,@ForwardCount,@CommentCount,GETDATE())
end
select @TweetsId
COMMIT TRANSACTION
