Need to optimize mssql query for fastest speed

Question

1.00/5 (1 vote)

See more:

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 5-Aug-14 23:56pm

orgilhp

Updated 6-Aug-14 3:07am

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

coded007 · Answer 1 · 2014-08-06T05:33:00

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)