Link to home
Start Free TrialLog in
Avatar of ExpertHelp79
ExpertHelp79

asked on

The query is taking very high resources

Hello
Please help me with the query . Its taking high resources need to optimize

SOS

TIA
SELECT TOP 1 @IsEnabled = 1 FROM PegTables.tblAssets A
			INNER JOIN PegTables.tblAssetLinks AL ON AL.RightAssetID = A.AssetID 
			INNER JOIN PegTables.tblAssetMetadata M ON M.AssetID = A.AssetID
			INNER JOIN PegTables.tblAssetType At ON At.AssetTypeID = A.AssetTypeId
		WHERE 
		ACLID = @ACLID AND A.IsDeleted = 0
		AND 
		A.Status IN(1,6)
		AND
		(AssetTypeEnum & @AssetTypeEnum) > 0
		AND
		At.IsDeleted = 0
		AND 
		AL.CourseID = @CourseID AND AL.IsDeleted = 0
		AND 
		EXISTS(SELECT TOP 1  V.AssetMetadataValueID  FROM PegTables.tblAssetMetadataValues V
					 WHERE V.AttributeID = @TopicID AND V.CollectionMetadataID = M.CollectionMetadataID AND V.AttributeValue <> '')
					 
			AND NOT EXISTS(SELECT 1 FROM PegTables.tblAssets Ast WHERE Ast.ACLID = @ACLID AND Ast.IsDeleted = 0
					AND Ast.Status IN(1,6) AND Ast.AssetTypeID IN(10,14,22,25) AND AL.LeftAssetID = Ast.AssetID)
					
			AND NOT EXISTS(SELECT 1 FROM PegTables.tblAssets A
								INNER JOIN PegTables.tblAssets A1 ON A1.ParentAssetID = A.AssetID AND A1.AssetID = AL.LeftAssetID
							WHERE A.ACLID = @ACLID AND A.IsDeleted = 0 AND A1.ACLID = @ACLID AND A1.IsDeleted = 0 AND A1.AssetTypeID = 4
								AND A.Status IN(1,6) AND A.AssetTypeID IN(10,14,22))
			
-- This part is taking the highest
AND 1 = 
			(CASE
				WHEN NOT EXISTS (SELECT  1 FROM PegTables.tblAssetStates Asts WHERE  Asts.AssetID = A.AssetID) THEN 1
				WHEN EXISTS(SELECT 1 FROM @StateIds)
					AND EXISTS (SELECT 1 FROM PegTables.tblStateCollectionValues Asv
								INNER JOIN PegTables.tblAssetStates Asts on Asts.CollectionID = Asv.CollectionID AND Asts.AssetID =A.AssetID
							WHERE StateID IN(SELECT ItemID FROM @StateIds))
				THEN 1
				
			ELSE 0 END )

Open in new window

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

one would need to see the explain plan for the query, but presumably you are missing some indexes. ..

you might change the last part to:
AND ( 
       ( NOT EXISTS (SELECT  1 FROM PegTables.tblAssetStates Asts WHERE  Asts.AssetID = A.AssetID) 
    OR ( EXISTS(SELECT 1 FROM @StateIds)
            AND EXISTS (SELECT 1 FROM PegTables.tblStateCollectionValues Asv
				 JOIN PegTables.tblAssetStates Asts 
                                    on Asts.CollectionID = Asv.CollectionID AND Asts.AssetID =A.AssetID
				WHERE StateID IN (SELECT ItemID FROM @StateIds)
                        )
       )
    )

Open in new window

Avatar of ExpertHelp79
ExpertHelp79

ASKER

no change still high
Can you post the query plan?
I've requested that this question be deleted for the following reason:

ns
>>ns<<
No feedback.
ExpertHelp79,

  if you want experts to help you, you need to feedback, actually.

  please reply with the requested information.

regards,
a3
ASKER CERTIFIED SOLUTION
Avatar of ExpertHelp79
ExpertHelp79

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I suggest then that we close the question by accepting the comment here http:#a36966355
Thanks :)