How I can optimize this query?

meristema · January 2, 2016, 1:42pm

Hello guys,
someone can help me to optimize query below, it process almost 3 million rows and read all stocking Items for output only available stock with prices, I think it possible improve code and I guess someone can tell me advise:

SELECT AvailableView.description AS descItem, costoFor, sum(qtaGiac) AS qtaGiac
FROM AvailableView INNER JOIN (SELECT Storage, Description FROM MA_Storages )
AS a ON AvailableView.storage = a.storage
INNER JOIN (SELECT CustSupp, CompanyName FROM SupplyView
WHERE Disabled = '0' ) AS b ON AvailableView.Supplier = b.CustSupp
INNER JOIN ListForDetails
ON AvailableView.IdListaFor = ListForDetails.IdListaFor AND AvailableView.Lot = ListForDetails.Lot INNER JOIN ListForPrices ON ListForDetails.IdListaFor = ListForPrices.IdListaFor AND
ListForDetails.IdRiga = ListForPrices.idRiga WHERE qtaGiac>0
AND (AvailableView.storage = 'GOLD' AND geo = 'UE' AND listino = 'X' AND nDays >= 0 AND nDays <= 10)
GROUP BY AvailableView.description, costoFor
ORDER BY AvailableView.description, costoFor, sum(qtaGiac)

execution plan is ok, there isn't any mssing index, but users wait for one minute for output.

Thank you very much
bye

bitsmed · January 2, 2016, 2:36pm

Please show us views (as create view statement), table description (as create table statement) for all tables involved (also those from view),

meristema · January 2, 2016, 4:18pm

this is first view:

SELECT dbo.LotAvailView.Item, dbo.LotAvailView.Lot, dbo.LotAvailView.Description, dbo.LotAvailView.Picture, dbo.LotAvailView.DescriptionText,
dbo.LotAvailView.Storage, dbo.LotAvailView.ValidFrom, dbo.LotAvailView.LoadDate, dbo.LotAvailView.PurchaseOrdNo,
dbo.LotAvailView.ProductCtg, dbo.LotAvailView.ProductSubCtg, dbo.LotAvailView.Supplier, dbo.LotAvailView.TaxCode,
ISNULL(dbo.PreCliView.QtaPrenota, 0) AS QtaPrenota, dbo.LotAvailView.Colore,
dbo.LotAvailView.QtaDispo - ISNULL(dbo.PreCliView.QtaPrenota, 0) AS QtaDispo, dbo.LotAvailView.QtaDispo AS QtaGiac,
dbo.LotAvailView.ReceivedQty AS QtaOrdFor, dbo.LotAvailView.MinimumSaleQty, dbo.LotAvailView.IssuedQty,
dbo.LotAvailView.CompanyName, dbo.LotAvailView.AGESTEP1, dbo.LotAvailView.GEO, dbo.LotAvailView.PRODUTTORE,
dbo.LotAvailView.AGING, dbo.LotAvailView.NGiorni, dbo.LotAvailView.Costo AS CostoFor, dbo.LotAvailView.CostoFin AS Cost,
dbo.LotAvailView.Misura AS BaseUoM, dbo.LotAvailView.StepDpz, dbo.LotAvailView.DescSubCategory, dbo.LotAvailView.DescCategory,
dbo.LotAvailView.DescColore, dbo.LotAvailView.QUAL, dbo.LotAvailView.LUNG, dbo.LotAvailView.IdListaFor, dbo.LotAvailView.Area,
dbo.LotAvailView.AreaDesc, dbo.LotAvailView.codQual, dbo.LotAvailView.codLung,
dbo.LotAvailView.Item + '-' + dbo.LotAvailView.Lot + '-' + dbo.LotAvailView.Storage AS id
FROM dbo.LotAvailView LEFT OUTER JOIN
dbo.PreCliView ON dbo.LotAvailView.Lot = dbo.PreCliView.lotto AND
dbo.LotAvailView.Item = dbo.PreCliView.Item

GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[16] 4[40] 2[25] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = -288
Left = 0
End
Begin Tables =
Begin Table = "LotAvailView"
Begin Extent =
Top = 6
Left = 38
Bottom = 323
Right = 204
End
DisplayFlags = 280
TopColumn = 20
End
Begin Table = "PreCliView"
Begin Extent =
Top = 6
Left = 242
Bottom = 99
Right = 393
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'AvailView'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'AvailView'
GO

meristema · January 2, 2016, 4:20pm

this is second view:

SELECT dbo.CustSuppSupplierOptions.Area, dbo.CustSupp.CompanyName, dbo.CustSupp.TaxIdNumber, dbo.CustSupp.FiscalCode,
dbo.CustSupp.Account, dbo.CustSupp.Address, dbo.CustSupp.ZIPCode, dbo.CustSupp.City, dbo.CustSupp.County,
dbo.CustSupp.EMail, dbo.CustSupp.Fax, dbo.CustSupp.Telephone1, dbo.CustSupp.Telephone2,
dbo.CustSuppSupplierOptions.TaxCode, dbo.CustSuppSupplierOptions.Blocked, dbo.CustSupp.Disabled,
dbo.CustSuppSupplierOptions.Salesperson, dbo.CustSupp.CustSuppType, dbo.CustSupp.CustSupp
FROM dbo.CustSupp INNER JOIN
dbo.CustSuppSupplierOptions ON dbo.CustSupp.CustSuppType = dbo.CustSuppSupplierOptions.CustSuppType AND
dbo.CustSupp.CustSupp = dbo.CustSuppSupplierOptions.Supplier

GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "CustSupp"
Begin Extent =
Top = 6
Left = 38
Bottom = 121
Right = 240
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "CustSuppSupplierOptions"
Begin Extent =
Top = 6
Left = 278
Bottom = 121
Right = 504
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'SupplyView'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'SupplyView'
GO

bitsmed · January 2, 2016, 5:17pm

Is LotAvailView yet another view?
In which tables do you find the fields listino and nDays?