Sql query optimization

Question

0.00/5 (No votes)

See more:

Database = SQL 2014

SQL

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.

Posted 10-Apr-20 14:13pm

aisone systems

Updated 10-Apr-20 20:13pm

Add a Solution

Comments

Jörgen Andersson 11-Apr-20 8:28am

Link to execution plan doesn't work, it points back to this question.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2020-04-10T20:13:00

You didn't post any row amount or cardinality information so let's assume all joins are highly selective. If that is the case, have you tried the following indexes

SQL

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);