Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to optimise multiple join query..?

Author  Topic 

bhushan_juare
Starting Member

45 Posts

Posted - 2013-02-15 : 02:37:13
Hi All,
This is the query i have written

Declare @FromDate DATETIME
Declare @EndDate DATETIME
Declare @Mar nvarchar(4)
Set @FromDate = '2011-06-30 00:00:00.000'
--Set @EndDate = '2011-07-28 00:00:00.000'
Set @Mar = '1001'

Select max(so.VKBUR) As MAR,
max(so.BEZEI) As NAME,
max(sd.KUNRG) As P,
max(cm.NAME1) As NAME2,
max(sd.PRODH) As SEGMENT,
max(sl.VTEXT) As MAT_DESCRIPTION,
max(za.FGCODE) As IT,
max(za.FGDESC) As IT_DESCRIPTION,
max(za.EANNUM) As U,
max(sd.FKIMG) As QUANTITY,
max(sd.VALINR) As VALUE_IN_FC,
max(sd.NTGEW) As WEIGHT_
From
sa_off so WITH(NOLOCK)
INNER JOIN
SA_DA sd WITH(NOLOCK)
On
so.VKBUR = sd.VKBUR
INNER JOIN
Cust_Mas cm WITH(NOLOCK)
On
sd.KUNRG = cm.KUNNR
INNER JOIN
Segment_line04 sl WITH(NOLOCK)
On
sd.pro_level_4 = sl.PRO_LEVEL_4
INNER JOIN
ZBARARCHIVE za WITH(NOLOCK)
On
sd.KUNRG = za.INTP
Where
so.VKBUR = @Market
AND
sd.KUNRG = za.INTP
AND
sd.PROD = sl.PROD_LEVEL_4
AND
sd.FKDAT = @FromDate
Group BY
sd.KUNRG,
cm.NAME1,
sl.VTEXT,
sd.PROD

Is there any way to optimise above query as it took too much time to display result set i.e for 100 line items time taken by the query:
1 minute and 46 Sec

Thanks & Regards,
Bhushan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-15 : 03:05:48
can you show some sample data and expected output? without that, its hard to understand what you are trying to do.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-02-15 : 04:09:29
you may check indexes on your table relating the data you are fetching! as well as those joined tables too..

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page
   

- Advertisement -