t***u
发帖数: 368 | 1 blow query is VERY slow： > 15 mins, any suggestions appreciated !!
@tempTable ~ 20k data
#ExpTable ~ 500k data
declare @tempTable table
(
data_id int,
Q float,
Qtype int
)
create table #ExpTable
(
data_id int,
B1 float,
B2 float,
B3 float,
B4 float,
B5 float,
B6 float,
B7 float,
B8 float,
B9 float,
B10 float
)
CREATE INDEX IDX_1 ON #ExpTable(B1)
CREATE INDEX IDX_2 ON #ExpTable(B2)
CREATE INDEX IDX_3 ON #ExpTable(B3)
CREATE INDEX IDX_4 ON #ExpTable(B4)
CREATE INDEX IDX_5 ON #ExpTable(B5)
CREATE INDEX IDX_6 ON #ExpTable(B6)
CREATE INDEX IDX_7 ON #ExpTable(B7)
CREATE INDEX IDX_8 ON #ExpTable(B8)
CREATE INDEX IDX_9 ON #ExpTable(B9)
CREATE INDEX IDX_10 ON #ExpTable(B10)
select
a.data_id ,
b.Q,
b.QType
from #ExpTable a
,
(select distinct Q, QType from @tempXTable) b
where
(
a.B1 = b.Q or a.B2 = b.Q or a.B3 = b.Q or a.B4 = b.Q or a.B5 = b.Q
or
a.B6 = b.Q or a.B7 = b.Q or a.B8 = b.Q or a.B9 = b.Q or
a.B10 = b.Q
) | c****s
发帖数: 10 | 2
try this:
select a.data_id, b.q, b.qtype from a inner join b
on b.q = a.b1 or b.q = a.b2 or b.q = a.b3 or b.q = a.b4 or ..... |
|
