</p> 7 rows selected.这显示有七行需要删除,还是用上一个表我测试了一下这个代码,它用了77秒种就删除了所有的数据准备好了用Sql语句来删除冗余数据,版本一它执行了135秒: f9 ^6 _; S1 K! u5 f# e) J, ]: b( W
DELETE
. u8 x# g- v2 [/ B FROM CUSTOMERS
- V2 I4 n! [0 c S6 X WHERE ID IN
: w6 B! N0 I: {/ D0 y; x (SELECT ID- C6 [% K: ?% \* [( D; g' m+ ]
FROM5 u; q0 @ t+ P5 s4 s! n6 C/ c
(SELECT ID, LastName, FirstName, RANK() OVER (PARTITION BY LastName,
9 O: D* d+ w1 ?' _9 m FirstName ORDER BY ID) AS SeqNumber3 m5 C" x5 i5 S( F
FROM Customers)
6 N/ j2 P! v% T( G WHERE SeqNumber > 1);
: Q1 ~6 W3 m1 D" p$ K 我们可以看到最后的两行语句对表中的数据进行了排序,这不是有效的,所以我们来优化一下最后一个查询语句,把Rank()函数应用到只含有冗余数据的组,而不是所有的列下面这个语句是比较有效率的,虽然它不像上一个查询那样精简SELECT ID, LastName, FirstName3 y8 D6 I0 ^) k) _, ^& h
FROM4 w! p- q9 s$ @9 b: F& g. k
(SELECT ID, LastName, FirstName, RANK() OVER (PARTITION BY LastName,# |0 u' W k; N' F* Y: n. X3 j
FirstName ORDER BY ID) AS SeqNumber
1 ]! j/ C% ]9 e; g; | FROM! v# |( R, i. ~% J* p) e% [" I
(SELECT ID, LastName, FirstName# Q4 C5 q4 M- N5 y# {6 Q+ J
FROM Customers
, t, _. p. b$ m WHERE (LastName, FirstName) IN (SELECT LastName, FirstName! z/ Y$ ^5 r+ ]
FROM Customers4 R( \5 ] u( u1 U3 A
GROUP BY LastName, FirstName5 f6 |. K5 s% s" e( D/ E
HAVING COUNT(*) > 1)))
5 d1 b$ G8 {6 \: G- ~ WHERE SeqNumber > 1;
8 x% A- K& H* f5 z$ B& U' f 选择冗余数据只用了26秒钟,这样就提高了67%的性能,这样就提高了将这个作为子查询的删除查询的效率,
. M7 {8 ~/ ?7 ` r' y! d1 P DELETE8 L: u: W: |* \+ f+ c
FROM Customers" c* s& G6 n1 \- x
WHERE ID IN
. \0 T) C5 O) S: x* D* m9 F (SELECT ID
# Z& f x! ~! H+ n FROM9 r8 a$ C& n2 t0 c2 O8 b
(SELECT ID, LastName, FirstName, RANK() OVER (PARTITION BY LastName,
. Y. O$ S8 h X, d5 F8 p2 u FirstName ORDER BY ID) AS SeqNumber
4 f* ]; A0 l7 u6 P0 C FROM
# N9 E. D2 P/ F/ }( s/ M (SELECT ID, LastName, FirstName
N" R7 ^! H0 \! p$ J% _6 C! t FROM Customers3 {* g0 r& b0 g1 y: `
WHERE (LastName, FirstName) IN (SELECT LastName, FirstName
& j5 C' g6 O+ c( Z4 d' `- G& U8 Z& q FROM Customers7 w- G6 b7 S5 |- b9 {( s$ D
GROUP BY LastName, FirstName
5 O. Z4 o9 I# k$ G8 w6 Z0 b% d HAVING COUNT(*) > 1)))
: j: N; w$ V' p8 S# ~) l$ g6 A# P4 I
WHERE SeqNumber > 1); |