我每天都要import约5000条记录到一个表,然后要用这5000条跟原来表内所有记录作比较(现在约20万,会越来越多),找出是否新的记录跟原来的记录有重复,然后更新所有出现重复的记录,旧的新的是重复的都要更新。
现在发现很慢,请问各位有没有什么方法可以加快速度??
我的表结构为:ID A1 A2 A3 Status我原来的SQL语句是这样的:UPDATE myTable SET [Status] = 90
WHERE ID in(
SELECT DISTINCT(s1.[ID])
FROM myTable AS s1
INNER JOIN myTable AS s2
ON s1.id <> s2.id
AND s1.status in (-1)
AND (
(
(s1.A1 <> '' AND s1.A1 <> '-' AND s1.A1 IS NOT NULL) AND (
s1.A1 = s2.A1
OR s1.A1 = s2.A2
OR s1.A1 = s2.A2
)
)
OR
(
(s1.A2 <> '' AND s1.A2 <> '-' AND s1.A2 IS NOT NULL) AND (
s1.A2 = s2.A1
OR s1.A2 = s2.A2
OR s1.A2 = s2.A2
)
)
OR
(
(s1.A2 <> '' AND s1.A2 <> '-' AND s1.A2 IS NOT NULL) AND (
s1.A2 = s2.A1
OR s1.A2 = s2.A2
OR s1.A2 = s2.A2
)
)
)
WHERE 1=1
AND s1.[Status] <> 90 )请问各位有什么方法优化一下??
现在发现很慢,请问各位有没有什么方法可以加快速度??
我的表结构为:ID A1 A2 A3 Status我原来的SQL语句是这样的:UPDATE myTable SET [Status] = 90
WHERE ID in(
SELECT DISTINCT(s1.[ID])
FROM myTable AS s1
INNER JOIN myTable AS s2
ON s1.id <> s2.id
AND s1.status in (-1)
AND (
(
(s1.A1 <> '' AND s1.A1 <> '-' AND s1.A1 IS NOT NULL) AND (
s1.A1 = s2.A1
OR s1.A1 = s2.A2
OR s1.A1 = s2.A2
)
)
OR
(
(s1.A2 <> '' AND s1.A2 <> '-' AND s1.A2 IS NOT NULL) AND (
s1.A2 = s2.A1
OR s1.A2 = s2.A2
OR s1.A2 = s2.A2
)
)
OR
(
(s1.A2 <> '' AND s1.A2 <> '-' AND s1.A2 IS NOT NULL) AND (
s1.A2 = s2.A1
OR s1.A2 = s2.A2
OR s1.A2 = s2.A2
)
)
)
WHERE 1=1
AND s1.[Status] <> 90 )请问各位有什么方法优化一下??
WHERE [ID] in(
SELECT DISTINCT(s1.[ID])
FROM [m_Table] AS s1
INNER JOIN [m_Table] AS s2
ON s1.[ID] <> s2.[ID]
AND s1.[Status] in (-1,0)
AND (
(
(s1.[A1] <> '' AND s1.[A1] <> '-' AND s1.[A1] IS NOT NULL) AND (
s1.[A1] = s2.[A1]
OR s1.[A1] = s2.[A2]
OR s1.[A1] = s2.[A3]
)
)
OR
(
(s1.[A2] <> '' AND s1.[A2] <> '-' AND s1.[A2] IS NOT NULL) AND (
s1.[A2] = s2.[A1]
OR s1.[A2] = s2.[A2]
OR s1.[A2] = s2.[A3]
)
)
OR
(
(s1.[A3] <> '' AND s1.[A3] <> '-' AND s1.[A3] IS NOT NULL) AND (
s1.[A3] = s2.[A1]
OR s1.[A3] = s2.[A2]
OR s1.[A3] = s2.[A3]
)
)
)
WHERE 1=1
)
大家可以写自己的SQL或者其他方法之类来给我参考下的呀,不一定要改我的
但你们也不能这样忽略我啊,我在好几个版块问问题怎么都没人理我呢?好Down啊~~~