SQL语句优化

Question

我现在需要把表phone里重复的数据删除，也就是cont_phone字段里包含关系的数据删除，我在cont_phone加了一个索引，但效率还是很低，执行下面的语句花了20分钟还没有跑出来，这个表的数据有40W笔，希望高手能给我一个建议。
select b.cont_id as phonecont_id,b.cont_phone,a.pktest,a.cont_id , a.cont_phone
 from 
phone a inner join phone b on  a.cont_Phone like '%'+b.cont_Phone+'%'
 and len(a.cont_phone)>7 and len(a.cont_phone)>len(b.cont_phone) and a.pktest<>b.pktest 
  我创建索引的语句create index index_phone on phone(cont_phone)
  很急，在线等待各位SQL SERVER版达人

Answer 1

create index index_phone on phone(cont_phone) 用聚集索引应该要好点,

Answer 2

我已经在pktest上有聚集索引了

Answer 3

a.cont_Phone like '%'+b.cont_Phone+'%' 
应该是这句的问题
你把表结构、数据和结果帖出来吧，重新写一个

Answer 4

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[phone]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[phone]
GOCREATE TABLE [dbo].[phone] (
[pktest] [int] IDENTITY (1, 1) NOT NULL ,
[cont_id] [int] NOT NULL ,
[cont_Phone] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[cont_MobilePhone] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[cont_status] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NULL ,
[cont_Memo] [ntext] COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
 

Answer 5

用文字描述一下再发上来几行数据
1 A
2 B
这样的，想得到的结果为
1 A

Answer 6

on  a.cont_Phone like '%'+b.cont_Phone+'%' 
你的这个条件有点问题。
cont_phone字段里包含关系的数据删除--》》指的是什么意思？

Answer 7

我想改正这个效率，我这个SQL语句没问题，数据出来也是正确的，就是执行效率太低，40分钟还跑不完，而这个语句我需要正常使用，谢谢楼上达人的大力关注跟回复

Answer 8

1. like '%'+.. 和 对列使用len()等函数，都是不走索引的。
2. 用临时表简化一下数据试试：select pktest,cont_id,cont_phone into #1
from phoneselect pktest,cont_id,cont_phone into #2
from #1
where len(cont_phone)>7select b.cont_id as phonecont_id,b.cont_phone,a.pktest,a.cont_id , a.cont_phone 
from #2 a inner join #1 b 
on  a.cont_Phone like '%'+b.cont_Phone+'%' 
and len(a.cont_phone)>len(b.cont_phone) and a.pktest <>b.pktest 

Answer 9

那你的条件是不对的。这样其实包含了a.cont_phone=b.cont_phone的。
举个例子：
cont_phone[phone]
2234954,232367  --要删除这一行数据吗？

Answer 10

--TRY:SELECT  b.cont_id as phonecont_id,b.cont_phone,a.pktest,a.cont_id , a.cont_phone
FROM  phone a INNER HASH JOIN phone b 
        on  CHARINDEX(b.cont_Phone,a.cont_Phone)>0
WHERE len(a.cont_phone)>7 AND len(a.cont_phone)>len(b.cont_phone) 
        AND a.pktest <>b.pktest 

Answer 11

  cont_id  cont_Phone            cont_MobilePhone  cont_status cont_Memo
1 333569   37825886              15002119959        待处理  2009-8-4无人2009-8-5 
2 259833   50387861              13764294399        不需要  2009-8-4宝宝不住这边
3 256192   家52827789（37825886） 13002123709       不需要   2009-8-4手机尚未启用，
---------------
我给出三条数据，比如1跟3就是重复的数据，我需要把这些数据找出来。表结构在上面的回帖里有。表数据40W条，主要是我的查询语句太低了。希望各位达人继续关注

Answer 12

SELECT * FROM TB T 
WHERE EXISTS(SELECT 1 FROM TB WHEERE CHARINDEX(cont_Phone,T.cont_Phone)>0)?

Answer 13

len(a.cont_phone)>len(b.cont_phone)
可以换成
a.cont_phone<>b.cont_phone

Answer 14

恩，这是好主意，我本来用charindex后来GOOGLE一下，说charindex会使索引失效，我就改成like了，各位继续

Answer 15

对，我要的就是这个效果，但你这个语句会把相同的数据也查出来的，要加pktest<>pktest

Answer 16

SELECT * FROM TB T 
WHERE EXISTS(SELECT 1 FROM TB WHEERE CHARINDEX(cont_Phone,T.cont_Phone)>0 AND pktest <>T.pktest)??
楼主试试

Answer 17

charindex和使用%开头的like几乎是一样的，
都不能很好的利用索引。KG的hash join是什么dd？

Answer 18

CREATE TABLE TBTEST
(  
 cont_id  INT,cont_Phone VARCHAR(50),   
 cont_MobilePhone VARCHAR(50),
 cont_status VARCHAR(50),cont_Memo VARCHAR(50))
INSERT TBTESTSELECT '333569' , '37825886'            ,  '15002119959'   ,     '待处理' , '2009-8-4无人2009-8-5' UNION
SELECT '259833' , '50387861'              ,'13764294399'    ,    '不需要' , '2009-8-4宝宝不住这边' UNION
SELECT '256192' , '家52827789（37825886）', '13002123709'   ,   '不需要' , '2009-8-4手机尚未启用，' --DROP TABLE TBTESTSELECT * FROM TBTESTSELECT * FROM TBTEST T 
WHERE EXISTS(SELECT 1 FROM TBTEST WHERE CHARINDEX(cont_Phone,T.cont_Phone)>0 AND cont_id<>T.cont_id)  
OR
EXISTS(SELECT 1 FROM TBTEST WHERE CHARINDEX(T.cont_Phone,cont_Phone)>0 AND cont_id<>T.cont_id)  
cont_id     cont_Phone                                         cont_MobilePhone                                   cont_status                                        cont_Memo                                          
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
256192      家52827789（37825886）                                13002123709                                        不需要                                                2009-8-4手机尚未启用，
333569      37825886                                           15002119959                                        待处理                                                2009-8-4无人2009-8-5（所影响的行数为 2 行）
楼主修改看看?

Answer 19

SELECT b.cont_id AS phonecont_id,b.cont_phone,a.pktest,a.cont_id , a.cont_phone 
 FROM phone a INNER JOIN phone b ON CHARINDEX(b.cont_phone,a.cont_phone) > 0 AND a.pktest <> b.pktest

Answer 20

---------------------------------
--  Author: HEROWANG(让你望见影子的墙)
--  Date  : 2009-08-23 13:48:41
---------------------------------
 
IF OBJECT_ID('[tb]') IS NOT NULL 
    DROP TABLE [tb]
go
CREATE TABLE [tb] (id int,cont_id varchar(100),cont_Phone varchar(100),cont_MobilePhone VARCHAR(20),cont_status varchar(100),cont_Memo VARCHAR(600))
INSERT INTO [tb]
SELECT 1,333569,'37825886',15002119959,'待处理','2009-8-4无人2009-8-5' UNION ALL
SELECT 2,259833,'50387861',13764294399,'不需要','2009-8-4宝宝不住这边' UNION ALL
SELECT 3,256192,'家52827789(37825886)',13002123709,'不需要','2009-8-4手机尚未启用'select * from [tb]select * from tb t
where exists(select 1 from tb where CHARINDEX(cont_phone,t.cont_phone) > 0 and cont_phone<>t.cont_phone)id cont_id cont_Phone cont_MobilePhone cont_status cont_Memo
3 256192 家52827789(37825886) 13002123709 不需要 2009-8-4手机尚未启用

Answer 21

记得用EXISTS的效率还不如JOIN啊

Answer 22

谢谢各位努力帮我写的SQL语句，但我的数据量太大，我执行了40分钟出不来数据，希望各位能有效率方面的建议，希望达人们继续关注

Answer 23

SELECT * FROM [phone] T 
WHERE EXISTS(SELECT 1 FROM [phone] WHERE CHARINDEX(cont_Phone,T.cont_Phone)>0 AND cont_id<>T.cont_id)  
UNION ALL
SELECT * FROM [phone] T
WHERE EXISTS(SELECT 1 FROM [phone] WHERE CHARINDEX(T.cont_Phone,cont_Phone)>0 AND cont_id<>T.cont_id)  OR换成UNION ALL试试

Answer 24

数据量大,还是用JOIN好了,试试JOIN先

Answer 25

SELECT T.* FROM TBTEST T ,TBTEST T1
WHERE (CHARINDEX(T1.cont_Phone,T.cont_Phone)>0  
OR CHARINDEX(T.cont_Phone,T1.cont_Phone)>0 )AND T1.cont_id<>T.cont_id  
少量数据测试,效率和EXISTS一样

Answer 26

charindex我最初用这个，但GOOGLE一下，说CHARINDEX会使索引失效，我才改为like的，各位继续在效率上讨论

Answer 27

LIKE 'COL%'LIKE '%COL%'--这是不能用到索引的

Answer 28

这个语句CHARINDEX和LIKE都用到索引了，用CHARINDEX应该比较好

Answer 29

我测过，LIKE '%COL%'也能乃至索引

Answer 30

是哦,刚测试了一下,怎么能用到呢???晕了SELECT * FROM TBTEST T ,TBTEST T1 WHERE T1.cont_Phone LIKE '%'+T.cont_Phone+'%' AND T1.cont_id<>T.cont_id UNION ALL
SELECT * FROM TBTEST T ,TBTEST T1 WHERE T.cont_Phone LIKE  '%'+T1.cont_Phone+'_%' AND T1.cont_id<>T.cont_id 
这样子效率还好点

Answer 31

SELECT * FROM TBTEST WHERE cont_Phone LIKE '50387861%'
SELECT * FROM TBTEST WHERE cont_Phone LIKE '%50387861%'
测试这个的时候,莫非列名作匹配条件不同???

Answer 32

等高人吧呵呵,你这样的匹配效率不怎么高!!!!!用函数走不到索引,用LIKE还能走,呵呵

Answer 33

a.cont_Phone like '%'+b.cont_Phone+'%' 
这句想办法不用like呀，用like索引基本上没用!
例：
a.cont_Phone = rtrim(b.cont_Phone)把cont_Phone先更新成一致

Answer 34

 where 条件 尽量少用函数 ，和 LIKE COLu  '%'+关键字+'%' 不走索引，而进行全表扫描。
先把不规则资料资料找出来整理，再筛选重复记录。