aisone systems Ответов: 1

Оптимизация Sql-запросов

База данных = SQL 2014 


SELECT        
		TBU_Item.PKGUID , 
		isnull(TBU_Item.AutoID, 0) as AutoID,
		isnull(TBU_Item.ItemID, '') as [Item ID], 
		isnull(TBU_Item.ItemDescr, '') as [Item Description], 
		isnull(TBU_Item.DescrForSale, '') as [Description For Sale],
		isnull(TBU_Item.DescrForPurchase, '') as [Description For Purchase],
		isnull(TBU_Item.Abbreviation, '') as Abbreviation,
		isnull(TBU_Item.ItemTypeDID, 0) as [ItemTypeDID], 
		isnull(TBS_ItemType.ItemType, '') as [Item Type],
		isnull(TBU_Item.ItemGroupDID, Cast(0x0 as uniqueidentifier)) as [ItemGroupDID], 
		isnull(TBU_ItemGroup.GroupID, '') as [Item Group],
		isnull(TBU_Item.ActiveID, 0) as [ActiveID],	
		isnull(TBU_Item.UOMGroupDID, Cast(0x0 as uniqueidentifier)) as [UOMGroupDID], 
		isnull(TBU_UOMGroup.UOMGroupID, '')  as [UOM Group],
		isnull(TBU_Item.Memo, '') as Memo, 
		isnull(TBU_Item.ItemManagementDID, '') as ItemManagementDID, 
		isnull(TBS_ItemManagement.ManagementType, '') as [Item Management],
		isnull(TBU_Item.DefaultUOMDID, Cast(0x0 as uniqueidentifier)) as [DefaultUOMDID], 
		isnull(TBU_UOM.UOMID, '') as [Default UOM],
		isnull(TBU_Item.CostingMethodDID, 0) as [CostingMethodDID],
		isnull(TBS_CostingMethod.CostingMethod, '') as [Costing Method],
		isnull(TBU_Item.Height, 0) as Height ,
		isnull(TBU_Item.Width, 0) as Width,
		isnull(TBU_Item.Length, 0) as Length,
		isnull(TBU_Item.Weight, 0) as Weight,
		isnull(TBU_Item.MinStock, 0)  as [Minimum Stock],
		isnull(TBU_Item.MaxStock, 0) as [Maximum Stock],
		isnull(TBU_Item.ReorderLevel, 0) as [Reorder Level],
		isnull(TBU_Item.DefaultStockLocationDID, Cast(0x0 as uniqueidentifier))  as [DefaultStockLocationDID],
		isnull(TBU_Location.LocationID, '')  as [Default Stock Location],
		isnull(TBU_Item.SalesUOMDID, Cast(0x0 as uniqueidentifier))  as [SalesUOMDID],
		isnull(Tbd_SalesUOM.UOMID, '') as [Sales UOM],
		isnull(TBU_Item.SaleRepresentativeDID, Cast(0x0 as uniqueidentifier))  as [SaleRepresentativeDID],
		isnull(Tbd_SalesRepEmployee.EmployeeID, '') as [Sale Representative],
		isnull(TBU_Item.Taxable, 0) as Taxable,		
		isnull(TBU_Item.PurchaseUOMDID, Cast(0x0 as uniqueidentifier))  as [PurchaseUOMDID],
		isnull(Tbd_PurchaseUOM.UOMID, '') as [Purchase UOM],
		isnull(TBU_Item.VendorItemNo, '') as [Vendor Item No],		
		isnull(TBU_Item.PurchaseRepresentativeDID, Cast(0x0 as uniqueidentifier))  as [PurchaseRepresentativeDID],
		isnull(Tbd_PurchaseRepEmployee.EmployeeID, '') as [Purchase Representative],
		isnull(TBU_Item.SalesAccountDID, Cast(0x0 as uniqueidentifier))  as [SalesAccountDID],
		isnull(Tbd_SalesAccounts.AccountID, '')  as [Sales Account],
		isnull(TBU_Item.CGSAccountDID, Cast(0x0 as uniqueidentifier))  as [CGSAccountDID],
		isnull(Tbd_CGSAccounts.AccountID, '') as [CGS Account],
		isnull(TBU_Item.InventoryAccountDID, Cast(0x0 as uniqueidentifier))  as [InventoryAccountDID],
		isnull(Tbd_InventoryAccounts.AccountID, '') as [Inventory Account],
		isnull(TBU_Item.ExpenseAccountDID, Cast(0x0 as uniqueidentifier))  as [ExpenseAccountDID],
		isnull(Tbd_ExpenseAccounts.AccountID, '') as [Expense Account],
		isnull((select top 1 isnull(ListDefinitionID, '') as  ListDefinitionID from TBU_ItemUDFListDefinition where PKGUID  = TBU_ItemUDFDefinition.List1DID), '')  as List1,
		isnull((select top 1 isnull(ListDefinitionID, '') as  ListDefinitionID from TBU_ItemUDFListDefinition where PKGUID  = TBU_ItemUDFDefinition.List2DID), '') as List2,
		isnull((select top 1 isnull(ListDefinitionID, '') as  ListDefinitionID from TBU_ItemUDFListDefinition where PKGUID  = TBU_ItemUDFDefinition.List3DID), '') as List3,
		isnull((select top 1 isnull(ListDefinitionID, '') as  ListDefinitionID from TBU_ItemUDFListDefinition where PKGUID  = TBU_ItemUDFDefinition.List4DID), '') as List4,
		isnull((select top 1 isnull(ListDefinitionID, '') as  ListDefinitionID from TBU_ItemUDFListDefinition where PKGUID  = TBU_ItemUDFDefinition.List5DID), '') as List5,
		isnull((select top 1 isnull(ListDefinitionID, '') as  ListDefinitionID from TBU_ItemUDFListDefinition where PKGUID  = TBU_ItemUDFDefinition.List6DID), '') as List6,
		isnull((select top 1 isnull(ListDefinitionID, '') as  ListDefinitionID from TBU_ItemUDFListDefinition where PKGUID  = TBU_ItemUDFDefinition.List7DID), '') as List7,
		isnull((select top 1 isnull(ListDefinitionID, '') as  ListDefinitionID from TBU_ItemUDFListDefinition where PKGUID  = TBU_ItemUDFDefinition.List8DID), '') as List8,
		isnull((select top 1 isnull(ListDefinitionID, '') as  ListDefinitionID from TBU_ItemUDFListDefinition where PKGUID  = TBU_ItemUDFDefinition.List9DID), '') as List9,
		isnull((select top 1 isnull(ListDefinitionID, '') as  ListDefinitionID from TBU_ItemUDFListDefinition where PKGUID  = TBU_ItemUDFDefinition.List10DID), '') as List10,
		isnull(TBU_ItemUDFDefinition.String1, '') as String1, 
		isnull(TBU_ItemUDFDefinition.String2, '') as String2, 
		isnull(TBU_ItemUDFDefinition.String3, '') as String3, 
		isnull(TBU_ItemUDFDefinition.String4, '') as String4, 
		isnull(TBU_ItemUDFDefinition.String5, '') as String5, 
		isnull(TBU_ItemUDFDefinition.String6, '') as String6, 
		isnull(TBU_ItemUDFDefinition.String7, '') as String7,
		isnull(TBU_ItemUDFDefinition.String8, '') as String8,
		isnull(TBU_ItemUDFDefinition.String9, '') as String9, 
		isnull(TBU_ItemUDFDefinition.String10, '') as String10, 
		isnull(TBU_ItemUDFDefinition.Decimal1, 0) as Decimal1, 
		isnull(TBU_ItemUDFDefinition.Decimal2, 0) as Decimal2, 
		isnull(TBU_ItemUDFDefinition.Decimal3, 0) as Decimal3, 
		isnull(TBU_ItemUDFDefinition.Decimal4, 0) as Decimal4, 
		isnull(TBU_ItemUDFDefinition.Decimal5, 0) as Decimal5, 
		isnull(TBU_ItemUDFDefinition.Decimal6, 0) as Decimal6, 
		isnull(TBU_ItemUDFDefinition.Decimal7, 0) as Decimal7, 
		isnull(TBU_ItemUDFDefinition.Decimal8, 0) as Decimal8, 
		isnull(TBU_ItemUDFDefinition.Decimal9, 0)  as Decimal9, 
		isnull(TBU_ItemUDFDefinition.Decimal10, 0) as Decimal10, 
		isnull(TBU_ItemUDFDefinition.Integer1, 0) as Integer1, 
		isnull(TBU_ItemUDFDefinition.Integer2, 0) as Integer2, 
		isnull(TBU_ItemUDFDefinition.Integer3, 0) as Integer3, 
		isnull(TBU_ItemUDFDefinition.Integer4, 0) as Integer4, 
		isnull(TBU_ItemUDFDefinition.Integer5, 0) as Integer5, 
		isnull(TBU_ItemUDFDefinition.Integer6, 0) as Integer6, 
		isnull(TBU_ItemUDFDefinition.Integer7, 0) as Integer7, 
		isnull(TBU_ItemUDFDefinition.Integer8, 0) as Integer8, 
		isnull(TBU_ItemUDFDefinition.Integer9, 0) as Integer9, 
		isnull(TBU_ItemUDFDefinition.Integer10, 0) as Integer10, 
		isnull(TBU_ItemUDFDefinition.DateTime1, '1900-01-01') as DateTime1, 
		isnull(TBU_ItemUDFDefinition.DateTime2, '1900-01-01') as DateTime2,
		isnull(TBU_ItemUDFDefinition.DateTime3, '1900-01-01') as DateTime3, 
		isnull(TBU_ItemUDFDefinition.DateTime4, '1900-01-01') as DateTime4, 
		isnull(TBU_ItemUDFDefinition.DateTime5, '1900-01-01') as DateTime5,
		isnull(TBU_ItemUDFDefinition.DateTime6, '1900-01-01') as DateTime6,
		isnull(TBU_ItemUDFDefinition.DateTime7, '1900-01-01') as DateTime7,
		isnull(TBU_ItemUDFDefinition.DateTime8, '1900-01-01') as DateTime8,
		isnull(TBU_ItemUDFDefinition.DateTime9, '1900-01-01') as DateTime9,
		isnull(TBU_ItemUDFDefinition.DateTime10, '1900-01-01') as DateTime10,
		isnull(TBU_ItemUDFDefinition.Boolean1, 0) as Boolean1,
		isnull(TBU_ItemUDFDefinition.Boolean2, 0) as Boolean2,
		isnull(TBU_ItemUDFDefinition.Boolean3, 0) as Boolean3,
		isnull(TBU_ItemUDFDefinition.Boolean4, 0) as Boolean4,
		isnull(TBU_ItemUDFDefinition.Boolean5, 0) as Boolean5,
		isnull(TBU_ItemUDFDefinition.Boolean6, 0) as Boolean6,
		isnull(TBU_ItemUDFDefinition.Boolean7, 0) as Boolean7,
		isnull(TBU_ItemUDFDefinition.Boolean8, 0) as Boolean8,
		isnull(TBU_ItemUDFDefinition.Boolean9, 0) as Boolean9,
		isnull(TBU_ItemUDFDefinition.Boolean10, 0) as Boolean10,
		isnull(TBU_Item.CB, cast(0x0 as uniqueidentifier)) as CB, 
		isnull(Tbu_UserCB.FirstName, '') + ' ' +  isnull(Tbu_UserCB.LastName, '') as Created_By,
		isnull(TBU_Item.CDate, '1900-01-01') as CDate, 
		isnull(TBU_Item.CDate, '1900-01-01') as Created_At,
		isnull(TBU_Item.MB, cast(0x0 as uniqueidentifier)) as MB, 
		isnull(Tbu_UserMB.FirstName, '')  + ' ' + isnull(Tbu_UserMB.LastName, '') as Modified_By,
		isnull(TBU_Item.MDate, '1900-01-01') as MDate, 
		isnull(TBU_Item.MDate, '1900-01-01') as Modified_At,
		isnull(TBU_Item.DB, cast(0x0 as uniqueidentifier)) as DB, 
		isnull(TBU_Item.DDate , '1900-01-01') as DDate, 
		isnull(TBU_Item.BranchID, 0) as BranchID
		FROM  TBU_Item 
		INNER JOIN TBU_ItemUDFDefinition ON TBU_Item.PKGUID = TBU_ItemUDFDefinition.VMDID 
		INNER JOIN TBS_ItemType ON TBU_Item.ItemTypeDID = TBS_ItemType.AutoID
		LEFT OUTER JOIN TBU_ItemGroup ON TBU_Item.ItemGroupDID = TBU_ItemGroup.PKGUID
		LEFT OUTER JOIN TBU_UOMGroup ON TBU_Item.UOMGroupDID = TBU_UOMGroup.PKGUID
		INNER JOIN TBS_ItemManagement ON TBU_Item.ItemManagementDID = TBS_ItemManagement.AutoID
		LEFT OUTER JOIN TBU_UOM ON TBU_Item.DefaultUOMDID = TBU_UOM.PKGUID
		INNER JOIN TBS_CostingMethod ON TBU_Item.CostingMethodDID = TBS_CostingMethod.AutoID
		LEFT OUTER JOIN TBU_Location ON TBU_Item.DefaultStockLocationDID = TBU_Location.PKGUID
		LEFT OUTER JOIN TBU_UOM as Tbd_SalesUOM ON TBU_Item.SalesUOMDID = Tbd_SalesUOM.PKGUID
		LEFT OUTER JOIN TBU_Employee as Tbd_SalesRepEmployee ON TBU_Item.SaleRepresentativeDID = Tbd_SalesRepEmployee.PKGUID
		LEFT OUTER JOIN TBU_UOM as Tbd_PurchaseUOM ON TBU_Item.PurchaseUOMDID = Tbd_PurchaseUOM.PKGUID
		LEFT OUTER JOIN TBU_Employee as Tbd_PurchaseRepEmployee ON TBU_Item.PurchaseRepresentativeDID = Tbd_PurchaseRepEmployee.PKGUID
		LEFT OUTER JOIN TBU_Accounts as Tbd_SalesAccounts ON TBU_Item.SalesAccountDID = Tbd_SalesAccounts.PKGUID
		LEFT OUTER JOIN TBU_Accounts as Tbd_CGSAccounts ON TBU_Item.CGSAccountDID = Tbd_CGSAccounts.PKGUID
		LEFT OUTER JOIN TBU_Accounts as Tbd_InventoryAccounts ON TBU_Item.InventoryAccountDID = Tbd_InventoryAccounts.PKGUID
		LEFT OUTER JOIN TBU_Accounts as Tbd_ExpenseAccounts ON TBU_Item.ExpenseAccountDID = Tbd_ExpenseAccounts.PKGUID
		LEFT OUTER JOIN TBU_User AS Tbu_UserCB ON TBU_Item.CB = Tbu_UserCB.PKGUID 
		LEFT OUTER JOIN TBU_User AS Tbu_UserMB ON TBU_Item.MB = Tbu_UserMB.PKGUID
		where Tbu_item.ISD = Cast(0x0 as uniqueidentifier)



Привет ребята у меня есть этот вышеописанный запрос с несколькими соединениями

у меня есть около 26 тысяч записей в результате, и это занимает около 6 секунд, чтобы обработать.

я хотел бы узнать, не могли бы вы помочь мне увеличить его производительность.

https://ufile.io/hmcd0ru4 Это ссылка на план выполнения

Что я уже пробовал:

Я добавил индексы и попробовал много других подходов, но просто не могу заставить этот запрос работать быстро.

Jörgen Andersson

Ссылка на план выполнения не работает, она указывает на этот вопрос.

1 Ответов

Рейтинг:
1

Wendelius

Вы не разместили никакой информации о количестве строк или мощности, поэтому давайте предположим, что все соединения очень избирательны. Если это так, то пробовали ли вы использовать следующие индексы

CREATE INDEX I_01 ON Tbd_CGSAccounts (PKGUID);
CREATE INDEX I_02 ON Tbd_ExpenseAccounts (PKGUID);
CREATE INDEX I_03 ON Tbd_InventoryAccounts (PKGUID);
CREATE INDEX I_04 ON Tbd_PurchaseRepEmployee (PKGUID);
CREATE INDEX I_05 ON Tbd_SalesAccounts (PKGUID);
CREATE INDEX I_06 ON TBS_CostingMethod (AutoID);
CREATE INDEX I_07 ON TBU_Item (ISD, PKGUID, ItemTypeDID);
CREATE INDEX I_08 ON TBU_ItemGroup (PKGUID);
CREATE INDEX I_09 ON TBS_ItemManagement (AutoID);
CREATE INDEX I_10 ON TBS_ItemType (AutoID);
CREATE INDEX I_11 ON TBU_ItemUDFDefinition (VMDID);
CREATE INDEX I_12 ON TBU_Location (PKGUID);
CREATE INDEX I_13 ON Tbd_SalesRepEmployee (PKGUID);
CREATE INDEX I_14 ON Tbd_SalesUOM (PKGUID);
CREATE INDEX I_15 ON TBU_UOMGroup (PKGUID);
CREATE INDEX I_16 ON TBU_UOM (PKGUID);
CREATE INDEX I_17 ON Tbu_UserCB (PKGUID);
CREATE INDEX I_18 ON Tbu_UserMB (PKGUID);