高分:含千万条数据的表查询的性能问题

[raymonzhao]

A表：
symbol tdate tclose
000001 20070725 1.23
000002 20070726 0.23
000001 20070723 2.3
000004 20070618 1.4
B表:
symbol
000001
000002
000004
输入2007-07-26
输出
symbol tdate tclose lclose
000001 20070725 1.23 2.3
000002 20070726 0.23 NULL
000004 20070618 1.4 NULL
symbol从B表中取现出,tdate为最新的tclose,
lclose为第二新的tclose,
tclose不为0或NULL


A表的记录数为2000万条左右,现在主要是一个效率的问题.不知道怎么才能再快点.

[昵称被占用了]

说说现在的索引情况

[昵称被占用了]

语句：
select x.*,y.tclose as lclose
from a x left join a y
on a.symbol=b.symbol
and b.tdate=(select max(tdate) from a where symbol=a.symbol and tdate<a.tdate)
where a.tdate<=cast('2007-7-26' as datetime)
and a.tclose<>0 and a.tclose is not NULL
and not exists (
select 1 from a where symbol=a.symbol
and tdate<=cast('2007-7-26' as datetime)
and tclose<>0 and tclose is not NULL
and tdate>a.tdate
)

最好有索引(symbol,tdate,tclose)

[raymonzhao]

..看不懂.我晕了.

[raymonzhao]

select x.*,y.tclose as lclose
from a x left join a y
on a.symbol=b.symbol
and b.tdate=(select max(tdate) from a where symbol=a.symbol and tdate<a.tdate)


B 是什么.B表里没有TDATE.

[昵称被占用了]

晕,我写错了

语句：
select x.*,y.tclose as lclose
from a x left join a y
on x.symbol=y.symbol
and y.tdate=(select max(tdate) from a where symbol=x.symbol and tdate<x.tdate)
where x.tdate<=cast('2007-7-26' as datetime)
and x.tclose<>0 and x.tclose is not NULL
and not exists (
select 1 from a where symbol=x.symbol
and tdate<=cast('2007-7-26' as datetime)
and tclose<>0 and tclose is not NULL
and tdate>x.tdate
)

最好有索引(symbol,tdate,tclose)

[qys2000]

要有索引(symbol,tdate,tclose)

[cqq_chen]

看看这贴，问问这位兄弟

http://community.csdn.net/Expert/topic/5673/5673596.xml?temp=.3719904

[raymonzhao]

首先谢谢楼上.上面的语句太慢了,不行,期待更好方案.

[PKwudijiang]

问题没看明白。lclose怎么定义的？

[lang8134]

试试这个.
------------------------------------------------------------------------------------
create table #t (
symbol varchar(10),
tdate varchar(10),
tclose numeric(8,2),
lclose numerice(8,2))

insert #t(symbol,tdate,tclose)
select a.symbol,max(tdate),tclose from a,b where a.symbol=b.symbol
group by a.symbol,tclose

create index idx_t_symbol_tdate on #t (symbol,tdate)

update #t set lclose = b.tclose from a b
where #t.symbol=b.symbol and b.tdate < #t.tdate and not exists (select * from a where a.symbol=b.symbol and a.tdate>b.tdate)

select * from #t

drop table #t

[lang8134]

不知道你的有多慢,有的时候由于数据多,同时硬件和sql优化不是很好的话,是慢的.

[lang8134]

楼主是求某一天的,那把下面那句换下.
insert #t(symbol,tdate,tclose)
select a.symbol,max(tdate),tclose from a,b where a.symbol=b.symbol
group by a.symbol,tclose
换成
-------------------------------------------------------------------
insert #t(symbol,tdate,tclose)
select a.symbol,tdate,tclose from a,b where a.symbol=b.symbol
where a.tdate=replace('2006-07-26','-','')

[ayzwd]

直接在查询分析器里“显示执行计划“，然后根据执行计划里耗时情况来创建索引，如果还不行，就用索引优化向导，这样就可以了。

[lang8134]

上边的有错误,重新贴个完整的.
--------------------------------------------------------------------------------
set nocount on

create table #t (
symbol varchar(10),
tdate varchar(10),
tclose numeric(8,2),
lclose numeric(8,2))

insert #t(symbol,tdate)
select a.symbol,tdate from a,b
where a.symbol=b.symbol
and a.tdate=replace('2007-07-26','-','')

create index idx_t_symbol_tdate on #t (symbol,tdate)

update #t set tclose = a.tclose from a where #t.symbol=a.symbol and #t.tdate=a.tdate

update #t set #t.lclose = c.tclose from a c
where #t.symbol=c.symbol and c.tdate < #t.tdate and not exists (select * from a where a.symbol=c.symbol and a.tdate>c.tdate
and a.tdate<>#t.tdate)

select * from #t

drop table #t
drop table a
drop table b

set nocount off

[lang8134]

笔误,习惯了.
上面的
drop table a
drop table b
是多余的.

[tony418]

不好意思 我想问下怎么才能提问？

[lang8134]

到小类去提问.