由于一个表中的数据量为2800多万，而且每天都在递增，即使建立了索引，那么你查找数据也和大海捞针一样，速度非常慢。那么有一个很好的思路就是把这个大表拆分为几个小表，最后用union语句合并，即使这样可以提高检索速度好几倍。
下面是这个存储过程：
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
----Writer : WangXY 2006-03-15
----Function: 生成近五天来每天的新匹配放入一个有日期命名的表中
----Table Name :如 [20060315]
ALTER PROCEDURE [dbo].[USP_GenerateNewMatchCurrentDay]
AS
BEGIN
Declare @Day0 nvarchar(8),
@Day1 nvarchar(8),
@Day2 nvarchar(8),
@Day3 nvarchar(8),
@Day4 nvarchar(8),
@Day5 nvarchar(8),
@Sql0 nvarchar(500),
@Sql1 nvarchar(500),
@Sql2 nvarchar(500),
@Sql3 nvarchar(500),
@Sql4 nvarchar(500),
@Sql5 nvarchar(500)
set @Day0=convert(nvarchar(8),getdate()-5,112)
set @Day1=convert(nvarchar(8),getdate()-4,112)
set @Day2=convert(nvarchar(8),getdate()-3,112)
set @Day3=convert(nvarchar(8),getdate()-2,112)
set @Day4=convert(nvarchar(8),getdate()-1,112)
set @Day5=convert(nvarchar(8),getdate(),112)
--Select convert(nvarchar(8),getdate()-5,112)
set @Sql0 ='if Exists(select * from dbo.sysobjects where id = object_id('+
@Day0+') and OBJECTPROPERTY(id, ''IsUserTable'') = 1) drop table ['+@Day0+']'
exec(@Sql0)
set @Sql1='if Not Exists(select * from dbo.sysobjects where id = object_id('+@Day1+
') and OBJECTPROPERTY(id, ''IsUserTable'') = 1) '+
'select a.UserID into ['+@Day1+'] from baihe..match_list a '+
'inner join baihe..match_candidate b '+
'on a.matchid=b.matchid where datediff(dd,b.GenerateTime,'''+@Day1+''')=0'
--print @Sql1
exec(@Sql1)
set @Sql2='if Not Exists(select * from dbo.sysobjects where id = object_id('+@Day2+
') and OBJECTPROPERTY(id, ''IsUserTable'') = 1) '+
'select a.UserID into ['+@Day2+'] from baihe..match_list a '+
'inner join baihe..match_candidate b '+
'on a.matchid=b.matchid where datediff(dd,b.GenerateTime,'''+@Day2+''')=0'
exec(@Sql2)
set @Sql3='if Not Exists(select * from dbo.sysobjects where id = object_id('+@Day3+
') and OBJECTPROPERTY(id, ''IsUserTable'') = 1) '+
'select a.UserID into ['+@Day3+'] from baihe..match_list a '+
'inner join baihe..match_candidate b '+
'on a.matchid=b.matchid where datediff(dd,b.GenerateTime,'''+@Day3+''')=0'
exec(@Sql3)
set @Sql4='if Not Exists(select * from dbo.sysobjects where id = object_id('+@Day4+
') and OBJECTPROPERTY(id, ''IsUserTable'') = 1) '+
'select a.UserID into ['+@Day4+'] from baihe..match_list a '+
'inner join baihe..match_candidate b '+
'on a.matchid=b.matchid where datediff(dd,b.GenerateTime,'''+@Day4+''')=0'
exec(@Sql4)
set @Sql5='if Not Exists(select * from dbo.sysobjects where id = object_id('+@Day5+
') and OBJECTPROPERTY(id, ''IsUserTable'') = 1) '+
'select a.UserID into ['+@Day5+'] from baihe..match_list a '+
'inner join baihe..match_candidate b '+
'on a.matchid=b.matchid where datediff(dd,b.GenerateTime,'''+@Day5+''')=0'
exec(@Sql5)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
下面这个是相关的存储过程，做个备份与本文无关。
ALTER PROCEDURE [dbo].[USP_Email_ExportNewMatchInfo]
AS
BEGIN
-- 0. 最终需要发送邮件的用户
create table #FinalUserID ( UserID bigint )
create table #F1 ( UserID bigint )
--1.取出五天内未登录，并且五天内有新匹配产生的所有接受邮件提醒的UserID
declare @day1 nvarchar(8),@day2 nvarchar(8),@day3 nvarchar(8),@day4 nvarchar(8),@day5 nvarchar(8),
@matchStr nvarchar(500),@str nvarchar(500)
set @day1=convert(nvarchar(8),getdate()-4,112)
set @day2=convert(nvarchar(8),getdate()-3,112)
set @day3=convert(nvarchar(8),getdate()-2,112)
set @day4=convert(nvarchar(8),getdate()-1,112)
set @day5=convert(nvarchar(8),getdate(),112)
set @matchStr='Select UserID From ['+@day1+'] union '+
'Select UserID From ['+@day2+'] union '+
'Select UserID From ['+@day3+'] union '+
'Select UserID From ['+@day4+'] union '+
'Select UserID From ['+@day5+']'
--print @matchStr
set @str='
insert #F1 ( UserID)
select distinct a.userid
from
(
select c.userid from
[user] c inner join baihe..user_Accountsetting d on c.userid =d.userid where datediff(dd,c.lastlogintime,getdate())>5 and d.AcceptEmail=1
) a
inner join
('+@matchStr+') b
on a.userid=b.userid'
--print @str
exec(@str)
print '五天内未登录，并且五天内有新匹配产生的所有接受邮件提醒的用户数'
create table #F2 ( UserID bigint )
--2.取出F1表中的记录以前被提醒符合条件的数据的部分
insert #F2 ( UserID)
Select b.UserID from #F1 a inner join
(
--以前曾经被提醒过，但提醒天数已经超过5天，并且提醒次数小于5次,并且接受新匹配邮件通知
Select a.userid from(
Select userid,count(userid) as RemindCount ,max(LastSentTime) as LastSentTime,AcceptEmail,IsSentByConsole from NewMatchInfo_Queue group by UserID,AcceptEmail,IsSentByConsole
) a where datediff(dd,a.LastSentTime,getdate())>5 and a.RemindCount<=5 and a.IsSentByConsole=1 and a.AcceptEmail=1
) b on a.Userid=b.Userid
print '五天内未登录，并且五天内有新匹配产生的所有接受邮件提醒,并且以前被提醒过的用户数'
--3.取出没被提醒的数据和被提醒数据中符合条件的数据总和
insert #FinalUserID ( UserID)
Select UserID From #F1 where UserID not in (Select distinct UserID from NewMatchInfo_Queue)
union
Select UserID from #F2
print '没被提醒的数据和被提醒数据中符合条件的数据总和'
--4.把#FinalUserID表中的数据插入到NewMatchInfo_Queue中
insert NewMatchInfo_Queue(UserID,EnterQueueTime,AcceptEmail)
Select UserID,getdate(),1 From #FinalUserID
print '把#FinalUserID表中的数据插入到NewMatchInfo_Queue中'
--5.更新提醒过五次的用户的接受邮件提醒状态 set AcceptEmail=0
Update NewMatchInfo_Queue set AcceptEmail=0
where UserID in(
Select UserID From
(
Select userid,count(userid) as RemindCount,AcceptEmail,IsSentByConsole
from NewMatchInfo_Queue group by UserID,AcceptEmail,IsSentByConsole
) a where a.RemindCount>5 and a.IsSentByConsole=1
)
print '更新提醒过五次的用户的接受邮件提醒状态 set AcceptEmail=0'
--6.删除临时表
drop table #F1
drop table #F2
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO