Click here to Skip to main content
15,071,262 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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

SQL
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
Posted
Updated 6-Aug-14 3:07am
v2
Comments
Herman<T>.Instance 6-Aug-14 6:33am
   
what is the exat goal of your query, what it is suposed to do?
orgilhp 6-Aug-14 6:38am
   
it will show list of movements of products.
orgilhp 6-Aug-14 6:41am
   
#oz oz1 left join
#oz oz2 on
oz1.product_id = oz2.product_id and
oz1.[date] > oz2.[date]

this section is the main issue. i have created indexes on product_id and [date] fields. but there was no effect.
Richard Deeming 6-Aug-14 8:37am
   
You haven't posted the indexes in your question. Use the "Improve question" link to add them to your code.

It would also help if you could specify which version of MSSQL you're using. For example, 2012 has some improvements which would remove the need for the left join.

1 solution

Try with below query i am not sure it will give data under 5 seconds but performance will improve.

SQL
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)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900