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
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 )
ASKER
no change still high
Can you post the query plan?
ASKER
I've requested that this question be deleted for the following reason:
ns
ns
>>ns<<
No feedback.
No feedback.
ExpertHelp79,
if you want experts to help you, you need to feedback, actually.
please reply with the requested information.
regards,
a3
if you want experts to help you, you need to feedback, actually.
please reply with the requested information.
regards,
a3
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I suggest then that we close the question by accepting the comment here http:#a36966355
agreed
ASKER
Thanks :)
you might change the last part to:
Open in new window