Friday, February 15, 2013 7:42 AMHi 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_
sa_off so WITH(NOLOCK)
SA_DA sd WITH(NOLOCK)
so.VKBUR = sd.VKBUR
Cust_Mas cm WITH(NOLOCK)
sd.KUNRG = cm.KUNNR
Segment_line04 sl WITH(NOLOCK)
sd.pro_level_4 = sl.PRO_LEVEL_4
ZBARARCHIVE za WITH(NOLOCK)
sd.KUNRG = za.INTP
so.VKBUR = @Market
sd.KUNRG = za.INTP
sd.PROD = sl.PROD_LEVEL_4
sd.FKDAT = @FromDate
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,
Friday, February 15, 2013 8:17 AMMake sure you have proper indexes when joining the tables.
Many Thanks & Best Regards, Hua Min
Friday, February 15, 2013 9:16 AM
Sunday, February 17, 2013 10:50 PMModeratorDo 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
Monday, February 18, 2013 3:35 AM
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