I have no clue how to even start tunning this giant query.

RichardBrianSmith 2016-07-26 21:46:55

This came from my new employers slowest report.
I modified the original by aliasing the table-names and formatting the layout.
It returned about 1k rows in 40 mins.
However as soon as I started rearranging the joins and matching up the conditions it stopped working correctly (returning about 4 rows).
So I backed out those changes.

Any advise please?

RichardBrianSmith 2016-07-27 09:01:48
UPDATE: I've updated stats and added an index …

CREATE UNIQUE NONCLUSTERED INDEX [ix_LotNoInformation_CoverForStockReport] ON [dbo].[Rustler Produce Ltd$Lot No_ Information]

([Lot No_] ASC,
[Item No_] ASC,
[Variant Code] ASC,
[Produce Type] ASC,
[Produce Variety] ASC,
[Produce Size] ASC,
[Country_Region of Origin Code] ASC,
Grower ASC,
[Consignment No_] ASC,
[Depot Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data Filegroup 1]
GO