需要优化mssql查询以获得最快速度 [英] Need to optimize mssql query for fastest speed
本文介绍了需要优化mssql查询以获得最快速度的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
请帮我以下查询进行优化。 #oz临时表有16000行。现在这个查询运行大约14秒。 SQL版本:MSSQL2008 R2。
用数据下载全部查询
创建 表 #oz(
id int ,from_sto_move bit ,product_id int ,[ date ] datetime ,
qty_orlogo 十进制( 30 , 10 ),qty_zarlaga 十进制( 30 , 10 ),cost_prev 十进制( 30 , 10 )
)
print ' 查询已启动on:' + CONVERT ( nvarchar ( 30 ),GETDATE(), 126 )
选择
oz1.product_id,
oz1.id,
oz1.from_sto_move,
sum(isnull(oz2.qty_orlogo, 0 ) - isnull (oz2.qty_zarlaga, 0 ))
qty_oz,
oz1.cost_prev
来自
#oz oz1 left join
#oz oz2
oz1.product_id = oz2.product_id 和
oz1。[ date ]> oz2。[ date ]
group by oz1.product_id,oz1.id,oz1.from_sto_move,oz1.cost_prev
print ' 查询结束于:' + CONVERT ( nvarchar ( 30 ),GETDATE(), 126 )
我需要不到5秒钟。谢谢
解决方案
尝试使用以下查询我不确定它会在5秒内提供数据,但性能会提高。
创建 表 #oz(
id int ,from_sto_move 位</ span>,product_id int , [ date ] datetime ,
qty_orlogo decimal ( 30 , 10 ),qty_zarlaga decimal ( 30 , 10 ),cost_prev decimal ( 30 , 10 )
)
< span class =code-keyword> print ' 查询开始于:' + CONVERT ( nvarchar ( 30 ),GETDATE(), 126 )
选择
oz1.product_id,
oz1.id,
oz1.from_sto_move,
sum(isnull(oz2.qty_orlogo, 0 ) - isnull(oz2.qty_zarlaga, 0 ))
qty_oz,
oz1.cost_prev
< span class =code-keyword> from
#oz oz1( NOLOCK ) left join
(选择 product_id,[ date ],oz2.qty_orlogo,oz2.qty_zarlaga FROM #oz( NOLOCK )) oz2
oz1.product_id = oz2.product_id 和
oz1。[ date ]> oz2。[ date ]
group by oz1.product_id,oz1.id,oz1.from_sto_move,oz1.cost_prev
print ' 查询结束于:' + CONVERT ( nvarchar ( 30 ),GETDATE(), 126 )
Please help me on below query to optimize. "#oz" temp table has 16000 rows. Now this query runs for about 14 seconds. SQL VERSION: MSSQL2008 R2.
DOWNLOAD FULL QUERY WITH DATA
create table #oz (
id int, from_sto_move bit, product_id int, [date] datetime,
qty_orlogo decimal(30, 10), qty_zarlaga decimal(30, 10), cost_prev decimal(30, 10)
)
print 'Query started on: ' + CONVERT(nvarchar(30), GETDATE(), 126)
select
oz1.product_id,
oz1.id,
oz1.from_sto_move,
sum(isnull(oz2.qty_orlogo, 0) - isnull(oz2.qty_zarlaga, 0))
qty_oz,
oz1.cost_prev
from
#oz oz1 left join
#oz oz2 on
oz1.product_id = oz2.product_id and
oz1.[date] > oz2.[date]
group by oz1.product_id, oz1.id, oz1.from_sto_move, oz1.cost_prev
print 'Query finished on: ' + CONVERT(nvarchar(30), GETDATE(), 126)
I need less than 5 seconds. Thanks
解决方案
Try with below query i am not sure it will give data under 5 seconds but performance will improve.
create table #oz ( id int, from_sto_move bit, product_id int, [date] datetime, qty_orlogo decimal(30, 10), qty_zarlaga decimal(30, 10), cost_prev decimal(30, 10) ) print 'Query started on: ' + CONVERT(nvarchar(30), GETDATE(), 126) select oz1.product_id, oz1.id, oz1.from_sto_move, sum(isnull(oz2.qty_orlogo, 0) - isnull(oz2.qty_zarlaga, 0)) qty_oz, oz1.cost_prev from #oz oz1 (NOLOCK) left join (Select product_id,[date],oz2.qty_orlogo,oz2.qty_zarlaga FROM #oz (NOLOCK)) oz2 on oz1.product_id = oz2.product_id and oz1.[date] > oz2.[date] group by oz1.product_id, oz1.id, oz1.from_sto_move, oz1.cost_prev print 'Query finished on: ' + CONVERT(nvarchar(30), GETDATE(), 126)
这篇关于需要优化mssql查询以获得最快速度的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文