select top 1000 * into #tb from Y2InReadCard_1 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' order by ReadCardTime insert into #tb ...... 只不过是分别往表插数据,不要union all. 我之前也犯过这种错.用了十几个表union all搞得系统工程down了.
你的语句得到的结果理论上是不对的。这样取出来的并不一定是所有数据中的前1000条。 用最基本的试试: select top 1000 * from ( select * from Y2InReadCard_1 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all select * from Y2InReadCard_2 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all select * from Y2InReadCard_3 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all select * from Y2InReadCard_4 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all select * from Y2InReadCard_5 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all select * from Y2InReadCard_6 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all select * from Y2InReadCard_7 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all select * from Y2InReadCard_8 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all select * from Y2InReadCard_9 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all select * from Y2InReadCard_10 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all select * from Y2InReadCard_11 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all select * from Y2InReadCard_12 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' ) as tempall order by ReadCardTime desc 甚至把where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%'放到最外面
你觉得你那个SQL语句该怎么写?我运行一下时时效率有没有改善
insert into #tb
......
只不过是分别往表插数据,不要union all. 我之前也犯过这种错.用了十几个表union all搞得系统工程down了.
用最基本的试试:
select top 1000 * from (
select * from Y2InReadCard_1 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all
select * from Y2InReadCard_2 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all
select * from Y2InReadCard_3 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all
select * from Y2InReadCard_4 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all
select * from Y2InReadCard_5 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all
select * from Y2InReadCard_6 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all
select * from Y2InReadCard_7 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all
select * from Y2InReadCard_8 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all
select * from Y2InReadCard_9 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all
select * from Y2InReadCard_10 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all
select * from Y2InReadCard_11 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%' UNION all
select * from Y2InReadCard_12 where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%'
) as tempall order by ReadCardTime desc 甚至把where ReadCardTime between '2007-09-01 14:21:13' and '2008-10-27 23:59:59' and CardNumber like '%10%'放到最外面
在视图的ReadCardTime 上建立聚集索引。
库被设计成了这样,你还有什么办法呢?在你的这种设计下, 任何查询优化都只能治标了。sql2005下可以试着建成分区表。
我用的SQL2000,如果用视图效果确实更差。
请教一下,你觉得这个数据库应该怎样设计,不过这次恐怕没办法改了