求助帮忙优化一个sql指令

文盲老顾
WEB应用领新星创作者
博客专家认证
2014-07-01 02:03:45
现在有一个企业表 qyml (id,qymc,flag),数据3万条
有一个产品表 spzs (id,cpmc,gsid,flag,picurl,jysm,xxsm),数据 120万条

现在有一个这样的需求，随机获取20条产品信息，每个公司的产品只能出现一个，且符合全文检索的关键字，我是这样写的指令

select top 20 * from (select a.id,GsID,Qymc,Cpmc,picurl,ROW_NUMBER() over (partition by gsid order by newid()) as tempRowID from Spzs a left join qyml b on a.GsID=b.id where a.flag=1 and b.flag=0 and b.enddate>getdate() and picurl is not null and picurl<>'' and gsid not in (0,209696,209696,60893,56128,54278,48227,48227,6476,58777,58397,56125,54943,54088,52846,322,206271,207286,62609,61985,57718,56238,16702,1619,610,191,178,211452,211132,205393,170022,70667,63266,62782,61816,61688,61203,59499,59014,58474,56629,56398,56134,54887,54194,1393,209)  and (contains((cpmc,jysm,xxsm),'白板') or contains((cpmc,jysm,xxsm),'信息化'))) a where tempRowID=1 order by NEWID()


我的基本思路是这样的，从spzs和qyml中联合查询，挑出符合条件的，且之前程序中没出现过的公司的其中随机一条数据取20个

只是这个指令执行起来效率是相当的慢，基本上需要10秒到30秒，比较耗费资源的就是 order by newid() 和 contains，可不用这两个又不知道该怎么实现了，求大能们帮帮忙
...全文
303 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
文盲老顾 2014-07-04
  • 打赏
  • 举报
 回复 
declare @tb table(pid int,cid int,cpmc nvarchar(100),flag int,picurl nvarchar(200))
declare @re table(pid int,cid int,qymc nvarchar(100),cpmc nvarchar(100),picurl nvarchar(200),cflag int,etime datetime,rankid int)
insert into @tb select id,gsid,Cpmc,Flag,picurl from spzs where  and contains((cpmc,jysm),'"白板" or "信息化"')
delete from @tb where picurl is null or picurl='' or flag=0
delete from @tb where cid in (0,209696,209696,60893,56128,54278,48227,48227,6476,58777,58397,56125,54943,54088,52846,322,206271,207286,62609,61985,57718,56238,16702,1619,610,191,178,211452,211132,205393,170022,70667,63266,62782,61816,61688,61203,59499,59014,58474,56629,56398,56134,54887,54194,1393,209)
insert into @re select pid,cid,Qymc,cpmc,picurl,b.flag,EndDate,QymlRankID from @tb a left join qyml b on a.cid=b.ID where  b.flag=0 and b.enddate>getdate()
select top 10 * from (select *,ROW_NUMBER() over (partition by cid order by newid()) as tempRowID from @re) a where tempRowID=1 order by rankid desc
最后搞成这样了,运行时间约2秒左右,感谢各位
Tiger_Zhao 2014-07-04
  • 打赏
  • 举报
 回复
引用 9 楼 superwfei 的回复:
4）随机到的产品必须符合包含关键字，也就是说必须使用 contains，因为一个公司多个产品，只有部分符合条件，不能完全随机获取
前提当然是在符合关键字的产品中随机。
引用 4 楼 Tiger_Zhao 的回复:
先将各自符合条件的记录选入 #a、#b，#a 中的 NEWID() 一次性生成； 删除不符合关联条件的记录；
意思是符合条件的企业各自进入 #a、#b； 有些企业没符合的产品、删除，某些产品没符合的企业、删除。 #b同一个企业下的产品只随机保留一条，其余的删除。 这不“随机的企业、随机的产品”不就达成了。
Andy-W 2014-07-03
  • 打赏
  • 举报
 回复
還是從篩分方面入手，繼續篩分SQl語句： declare @tb table ( pid int , cid int , qymc nvarchar(100) , cpmc nvarchar(100) , picurl nvarchar(200) ) insert into @tb select a.ID,GsID,Qymc,Cpmc,picurl from Spzs a left join Qyml b on a.GsID = b.ID where a.flag = 1 and picurl is not null and picurl <> '' and b.flag = 0 and b.enddate > getdate() and gsid not in ( 0,209696,209696,60893,56128,54278,48227,48227,6476,58777,58397,56125,54943,54088,52846,322,206271,207286,62609, 61985,57718,56238,16702,1619,610,191,178,211452,211132,205393,170022,70667,63266,62782,61816,61688,61203,59499, 59014,58474,56629,56398,56134,54887,54194,1393,209 ) and ( contains (( cpmc,jysm,xxsm),'白板' ) or contains (( cpmc,jysm,xxsm),'信息化' ) ) select top 10 * from ( select *,row_number () over (partition by cid order by newid ()) as tempRowID from @tb ) a where tempRowID = 1 order by newid() 第1段：把這部份。。。。contains (( cpmc,jysm,xxsm),'白板' ) or contains (( cpmc,jysm,xxsm),'信息化' ) 得的結果先插入臨時表， 第2段：再在臨時表上結合其他條件進行2次篩選。 還慢，那麼你先分段測試，先測第1段耗時，再測第2段耗時。 這樣你基本知道問題在哪裡了，繼續優化。
文盲老顾 2014-07-03
  • 打赏
  • 举报
 回复
引用 6 楼 Tiger_Zhao 的回复:
我4楼说的是中文啊，怎么不睬？ 1)contains 用组合条件，查一遍总比查两遍快。 2)不要用 NOT IN！ 你的 NOT IN 范围会越来越大，过滤的越多性能反而更低。 不要把多次取数作为独立需求处理，要把一串取数作为整体需求处理。 除了第一次需要生成大结果比较慢，每次只需要取20条并从临时表中删除，绝对能秒取。 3)第一次需要生成大结果时就决定了企业“随机”的次序，每次只需要按照这个次序取前20条即可。 4)关于每企业“随机”选一个产品 a.可以在第一次需要生成大结果时就“随机”选一个产品； b.也可以在每次取20条企业时再分别“随机”选一个产品。 你需要实测一下哪个方案快。
4）随机到的产品必须符合包含关键字，也就是说必须使用 contains，因为一个公司多个产品，只有部分符合条件，不能完全随机获取
文盲老顾 2014-07-03
  • 打赏
  • 举报
 回复
引用 6 楼 Tiger_Zhao 的回复:
我4楼说的是中文啊，怎么不睬？ 1)contains 用组合条件，查一遍总比查两遍快。 2)不要用 NOT IN！ 你的 NOT IN 范围会越来越大，过滤的越多性能反而更低。 不要把多次取数作为独立需求处理，要把一串取数作为整体需求处理。 除了第一次需要生成大结果比较慢，每次只需要取20条并从临时表中删除，绝对能秒取。 3)第一次需要生成大结果时就决定了企业“随机”的次序，每次只需要按照这个次序取前20条即可。 4)关于每企业“随机”选一个产品 a.可以在第一次需要生成大结果时就“随机”选一个产品； b.也可以在每次取20条企业时再分别“随机”选一个产品。 你需要实测一下哪个方案快。
使用一个 contains 用 or 判断多个关键字,效率提高两秒左右
引用 7 楼 TravyLee 的回复:
select top 10 * from ( select a.id,GsID,Qymc,Cpmc,picurl,ROW_NUMBER() over (partition by gsid order by newid()) as tempRowID from Spzs a left join qyml b on a.GsID=b.id where a.flag=1 and b.flag=0 and b.enddate>getdate() and picurl is not null and picurl<>'' and gsid not in (0,209696,209696,60893,56128,54278,48227,48227,6476,58777,58397,56125,54943,54088,52846,322,206271,207286,62609,61985,57718,56238,16702,1619,610,191,178,211452,211132,205393,170022,70667,63266,62782,61816,61688,61203,59499,59014,58474,56629,56398,56134,54887,54194,1393,209) and (contains((cpmc,jysm,xxsm),'白板') or contains((cpmc,jysm,xxsm),'信息化')) ) a where tempRowID=1 order by NEWID() 第一个问题：为什么随机取数据要在数据库中来做？ 可不可以：1、程序实现 2、通过生成10个随机ID，然后再根据ID去锁定10条数据？ 第二个问题：not in里的内容太多，为何不换个写法？
第一个问题，数据量比较大，总产品数100多万条记录，通过程序获取完整的数据不现实 第二个问题，我说的条件里少说了一条，如果该公司信息出现过，则不能重复出现。。。。
Tiger_Zhao 2014-07-02
  • 打赏
  • 举报
 回复
我4楼说的是中文啊，怎么不睬？
1)contains 用组合条件，查一遍总比查两遍快。
2)不要用 NOT IN！
你的 NOT IN 范围会越来越大，过滤的越多性能反而更低。
不要把多次取数作为独立需求处理，要把一串取数作为整体需求处理。
除了第一次需要生成大结果比较慢，每次只需要取20条并从临时表中删除，绝对能秒取。
3)第一次需要生成大结果时就决定了企业“随机”的次序，每次只需要按照这个次序取前20条即可。
4)关于每企业“随机”选一个产品
a.可以在第一次需要生成大结果时就“随机”选一个产品；
b.也可以在每次取20条企业时再分别“随机”选一个产品。
你需要实测一下哪个方案快。
开着拖拉机泡妞 2014-07-02
  • 打赏
  • 举报
 回复
select top 10 * from ( select a.id,GsID,Qymc,Cpmc,picurl,ROW_NUMBER() over (partition by gsid order by newid()) as tempRowID from Spzs a left join qyml b on a.GsID=b.id where a.flag=1 and b.flag=0 and b.enddate>getdate() and picurl is not null and picurl<>'' and gsid not in (0,209696,209696,60893,56128,54278,48227,48227,6476,58777,58397,56125,54943,54088,52846,322,206271,207286,62609,61985,57718,56238,16702,1619,610,191,178,211452,211132,205393,170022,70667,63266,62782,61816,61688,61203,59499,59014,58474,56629,56398,56134,54887,54194,1393,209) and (contains((cpmc,jysm,xxsm),'白板') or contains((cpmc,jysm,xxsm),'信息化')) ) a where tempRowID=1 order by NEWID() 第一个问题：为什么随机取数据要在数据库中来做？ 可不可以：1、程序实现 2、通过生成10个随机ID，然后再根据ID去锁定10条数据？ 第二个问题：not in里的内容太多，为何不换个写法？
文盲老顾 2014-07-01
  • 打赏
  • 举报
 回复 
declare @tb table(pid int,cid int,qymc nvarchar(100),cpmc nvarchar(100),picurl nvarchar(200))
insert into @tb select a.ID,GsID,Qymc,Cpmc,picurl from Spzs a left join Qyml b on a.GsID=b.ID where a.flag=1 and picurl is not null and picurl<>'' and b.flag=0 and b.enddate>getdate() and gsid not in (0,209696,209696,60893,56128,54278,48227,48227,6476,58777,58397,56125,54943,54088,52846,322,206271,207286,62609,61985,57718,56238,16702,1619,610,191,178,211452,211132,205393,170022,70667,63266,62782,61816,61688,61203,59499,59014,58474,56629,56398,56134,54887,54194,1393,209) and (contains((cpmc,jysm,xxsm),'白板') or contains((cpmc,jysm,xxsm),'信息化'))
select top 10 * from (select *,ROW_NUMBER() over (partition by cid order by newid()) as tempRowID from @tb) a where tempRowID=1 order by NEWID()
我给弄成这样，效率还是不太满意，基本在9到13秒之间
Tiger_Zhao 2014-07-01
  • 打赏
  • 举报
 回复 
(contains((cpmc,jysm,xxsm),'白板') or contains((cpmc,jysm,xxsm),'信息化'))

-->这个可以这样

contains((cpmc,jysm,xxsm),'"白板" OR "信息化"')


许多字段都没标记前缀，根本不只到那个表的字段，怎么做优化？

看你的意思是每次20个这样的取，应该考虑用临时表：
先将各自符合条件的记录选入 #a、#b，#a 中的 NEWID() 一次性生成；
删除不符合关联条件的记录；
#a 中取 NEWID() 的前20个，再到 b# 中与这20个关联的记录进行 NEWID() 选取，返回数据；
把这20个相关的数据都删除。
以学习为目的 2014-07-01
  • 打赏
  • 举报
 回复
引用 1 楼 superwfei 的回复:
select top 10 * from (
	select 
		a.id,GsID,Qymc,Cpmc,picurl,ROW_NUMBER() over (partition by gsid order by newid()) as tempRowID
	from Spzs a left join qyml b 
	on a.GsID=b.id 
	where a.flag=1 and b.flag=0 and b.enddate>getdate() and picurl is not null and picurl<>'' 
		and gsid not in (0,209696,209696,60893,56128,54278,48227,48227,6476,58777,58397,56125,54943,54088,52846,322,206271,207286,62609,61985,57718,56238,16702,1619,610,191,178,211452,211132,205393,170022,70667,63266,62782,61816,61688,61203,59499,59014,58474,56629,56398,56134,54887,54194,1393,209)  
		and (contains((cpmc,jysm,xxsm),'白板') or contains((cpmc,jysm,xxsm),'信息化'))
) a 
where tempRowID=1 order by NEWID()
指令太长了，从新粘贴一下
也建议将子查询写入临时表中，然后在临时表中再来操作
--小F-- 2014-07-01
  • 打赏
  • 举报
 回复
有全文索引？不好办。你可以尝试将子查询的记录写入临时表试试。
文盲老顾 2014-07-01
  • 打赏
  • 举报
 回复 
select top 10 * from (
	select 
		a.id,GsID,Qymc,Cpmc,picurl,ROW_NUMBER() over (partition by gsid order by newid()) as tempRowID
	from Spzs a left join qyml b 
	on a.GsID=b.id 
	where a.flag=1 and b.flag=0 and b.enddate>getdate() and picurl is not null and picurl<>'' 
		and gsid not in (0,209696,209696,60893,56128,54278,48227,48227,6476,58777,58397,56125,54943,54088,52846,322,206271,207286,62609,61985,57718,56238,16702,1619,610,191,178,211452,211132,205393,170022,70667,63266,62782,61816,61688,61203,59499,59014,58474,56629,56398,56134,54887,54194,1393,209)  
		and (contains((cpmc,jysm,xxsm),'白板') or contains((cpmc,jysm,xxsm),'信息化'))
) a 
where tempRowID=1 order by NEWID()
指令太长了，从新粘贴一下
MySQL版SQL优化
本课程通过Centos版的MySQL讲解了SQL优化的一些常见手段。 其中讲解了MySQL的分层、存储引擎等底层逻辑，并讲解了常见的索引优化手段。在讲解时，先通过理论对先关的优化知识进行了铺垫，然后使用实际的案例详细的...
MS-SQL Server 基础类 - SQL语句
这一个查询条件可不可以进行优化使到只查询执行一次就可得出所有的数据？现table1有字段 id... 怎样查找统计相同名称的金额值，请帮我，谢谢。 现在我的企业管理器打不开了，只能用查询分析器，可我不知道怎样用SQL...
百度 APP iOS 端包体积 50M 优化实践 (四) 代
⭐️ 微软合并暴雪新进展美国一位联邦法官发现，联邦交易委员未能证明微软收购动视暴雪，一个在线游戏开发商，会如何损害竞争，并且驳回了该机构阻止交易的禁令请求。华为od拿到offer22届，目标院校，考研二战前一周...
dapp理财质押挖矿分红系统开发项目案例/指南教程/功能逻辑
问项目，项目中做了什么，遇到了什么问题，怎么解决的sql优化慢查询和索引失效怎么解决的tcp三次握手。移动研究院春招 ai中心目前面试进展 #移动研究院春招# #移动研究院终面# #移动研究院# 北。
基础
请问怎么得到一个函数的执行时间，单位毫秒！如何调试asp组件呀。用vc++调试编制的组件怎样画一个箭头？高分请教如何限制程序运行的方法？求助：通过编程DirectX抓屏------>masterz:再麻烦你一下，谢谢熟悉RichEdit...
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章