大数据量分表时 两个表查询比较快的方式

两人个一模一样的表，按月分表，有时候查询关联到两个表，最容易联想到的方式就是UNION 或者UNION ALL

但是会发现速度异常的慢。

经实验 ，最快的方式是使用临时表。

贴代码

checkpoint
dbcc dropcleanbuffers
dbcc freeproccache
if object_id(N'#xtest',N'U') is not null
BEGIN
 drop table #xtest
end
CREATE TABLE #xtest(
    
    [diskid] [char](20) NOT NULL CONSTRAINT [DF_tj_Client_clientcount]  DEFAULT ((0)),
    [dateandtime] [smalldatetime] NOT NULL CONSTRAINT [DF_tj_Client_dateandtime]  DEFAULT (getdate()),
    [valid] [bit] NOT NULL CONSTRAINT [DF_tj_Client_valid]  DEFAULT ((0)),
    [channelid] [int] NOT NULL CONSTRAINT [DF_tj_Client_channelid]  DEFAULT ((0)),
    [subchannel] [int] NOT NULL CONSTRAINT [DF_tj_Client_subchannel]  DEFAULT ((0)),
    [ver] [char](5) NULL,
    [publicdate] [char](8) NULL,
    [startcount] [smallint] NOT NULL CONSTRAINT [DF_tj_Client_run]  DEFAULT ((1)),
    [active] [bit] NOT NULL CONSTRAINT [DF_tj_Client_active]  DEFAULT ((0)),
    [usetime] [smallint] NOT NULL CONSTRAINT [DF_tj_Client_windowtime]  DEFAULT ((0)),
    [playtime] [smallint] NOT NULL CONSTRAINT [DF_tj_Client_playtime]  DEFAULT ((0)),
    [playcount] [smallint] NOT NULL CONSTRAINT [DF_tj_Client_playcount]  DEFAULT ((0)),
    [exitcount] [smallint] NOT NULL,
    [topads] [smallint] NOT NULL CONSTRAINT [DF_tj_Client_topads]  DEFAULT ((0)),
    [rightads] [smallint] NOT NULL CONSTRAINT [DF_tj_Client_rightads]  DEFAULT ((0)),
    
    [ip] [char](15) )

GO

insert  into #xtest(dateandtime, startcount, usetime, playtime, playcount, exitcount, topads, rightads) select top 100 dateandtime, startcount, usetime, playtime, playcount, exitcount, topads, rightads from tj_client
insert  into #xtest(dateandtime, startcount, usetime, playtime, playcount, exitcount, topads, rightads) select top 100 dateandtime, startcount, usetime, playtime, playcount, exitcount, topads, rightads from tj_client_today

select * from #xtest

drop table #xtest

 

转载于:https://www.cnblogs.com/xdoudou/p/3293210.html