首页新闻找找看学习计划

sql优化

0
悬赏园豆:5 [已解决问题] 解决于 2010-11-12 11:15

我有一句sql语句

select top 12 * from Product where productID not in (select top 12 productID from Product where (productName like '%2612%' or productID in (select productID from Product_Fitting where suitType like '%2612%')) and isAdjust = 0 and [status]=1 ) and (productName like '%2612%' or productID in (select productID from Product_Fitting where suitType like '%2612%')) and isAdjust = 0 and [status]=1

里面因为有了一句

productID in (select productID from Product_Fitting where suitType like '%2612%')) and isAdjust = 0 and [status]=1 ) and (productName like '%2612%' or productID in (select productID from Product_Fitting where suitType like '%2612%')

就会很卡!请问各位高手谁帮我优化一下,我这个是做分页用的

问题补充: 第一句是:select top 12 * from Product where productID not in (select top 12 productID from Product where (productName like '%2612%' or productID in (select productID from Product_Fitting where suitType like '%2612%')) and isAdjust = 0 and [status]=1 ) and (productName like '%2612%' or productID in (select productID from Product_Fitting where suitType like '%2612%')) and isAdjust = 0 and [status]=1 第二句是:productID in (select productID from Product_Fitting where suitType like '%2612%')) and isAdjust = 0 and [status]=1 ) and (productName like '%2612%' or productID in (select productID from Product_Fitting where suitType like '%2612%')
°潶、緤°的主页 °潶、緤° | 初学一级 | 园豆:17
提问于:2010-08-17 10:43
< >
分享
最佳答案
0

第二句:

productID in (select productID from Product_Fitting where suitType like '%2612%')) and isAdjust = 0 and [status]=1 ) and (productName like '%2612%' or productID in (select productID from Product_Fitting where suitType like '%2612%')

1.应该可以写成(where后有重复):

 isAdjust = 0 and [status]=1 ) and (productName like '%2612%' or productID in (select productID from Product_Fitting where suitType like '%2612%')

2.like后的条件是否只需向右匹配就可以了,这样可以使用索引,具体可以探讨一下需求(productName like '2612%')

3. select * 是否需要显示所有的字段信息,如果不需要,且字段很多的话,尽量只检索有用的字段。

4.通过Left join 的形式,减少多余的where条件与表检索。

精简后的Sql:

select top 12 * from Product P Left Join Product_Fitting PF ON P.productID = PF.productID
where P.productName like '%2612%' AND PF.suitType = like '%2612%'
AND P.isAdjust =0 and P.[Status] = 1

收获园豆:5
nabber | 菜鸟二级 |园豆:479 | 2010-08-17 16:11
其他回答(6)
0

like '%2612%' //like加上左右通配符就会引起全表扫描。

Astar | 园豆:40805 (高人七级) | 2010-08-17 10:59
这是一个模糊查询啊!所以要加啊!
支持(0) 反对(0) °潶、緤° | 园豆:17 (初学一级) | 2010-08-17 11:00
@°潶、緤°:如果你在sqlserver上执行这样的like,优化不了,可以建立全文索引,或者使用第三方索引组件。
支持(0) 反对(0) Astar | 园豆:40805 (高人七级) | 2010-08-17 11:02
0

采用翻转分页试下

select top 12 * from (select top 24  * from product where **** order by productid)  order by productid desc

)

dail | 园豆:630 (小虾三级) | 2010-08-17 11:06
0

你看看这个查询的
select count(*) from Product_Fitting where suitType like '%2612%')
select count(*) from Product_Fitting
看看这两个查询的比值是多少,如果很大,例如 30% 那么就没什么优化的余地,如果很小,例如 3% 那么可以尝试这个方法:

create index idx_Product_Fitting_suitType on Product_Fitting(suitType);
将查询该写成:
select productID from Product_Fitting where suitType in
(
  select suitType from Product_Fitting like '%2612%'
)

也许可以骗过优化器使用索引

killkill | 园豆:992 (小虾三级) | 2010-08-17 11:17
0


select *
from (
select ROW_NUMBER() OVER (ORDER BY productID) AS FRowIndex,* from Product where productID not in
(
select productID from Product_Fitting where suitType like '%2612%')
and productName like '%2612%'
and isAdjust = 0 and [status]=1
)tb
where FRowIndex <13

 

jowo | 园豆:2834 (老鸟四级) | 2010-08-17 11:27
0

1. 建全文索引

2. 尽量把in换成join

3. 表里尽量少用Null值,如果是Null在做操作的时候用Isnull函数换成非Null值,多写几种等价的SQL,测一下,选最佳的那个。

灰灰狼 | 园豆:5 (初学一级) | 2010-08-17 11:51
0

效率比较,尽量使用INNER JOIN,用ON或者where过滤掉不需要的行

1. Exists比In好.

2. 模糊查询 suitType like '%2612%' SQL优化器无法使用Index

changbluesky | 园豆:854 (小虾三级) | 2010-08-20 15:47
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册