Database = 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)
Hi guys i have this above query with multiple joins
i have about 26k records in result and it takes about 6 seconds to process.
i was wondering if you could help me increase its performance.
https://ufile.io/hmcd0ru4 This is the link to execution plan
What I have tried:
I have added indexes and tried many other approaches but just cant get this query to work fast.