How to optimise multiple join query..?

Question

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

Answer 1

Make sure you have proper indexes when joining the tables.

---

Many Thanks & Best Regards, Hua Min

Answer 2

Need your explain plan./execution plan.

Create index on Column used in where condition also Join table.

Avoid large table into query.alternate of huge amount of data.

Why you are not try with Indexed views calculated value in it.

---

-- Bhavesh Thakkar

Sr.Database Administrator
My Blog
Linked In Profile

Answer 3

Do you need that many JOINs? How about you drop (for now) few tables from this query and keep just a few? Will the performance improve?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you did not). Temporal data should use ISO-8601 formats (you did not). Code should be in Standard SQL as much as possible and not local dialect.

Why do your data element names make no sense and constantly change?

That many MAX() has a really bad code smell. But no DDL so we cannto help you.

 

 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL