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

LVL 2
ExpertHelp79Asked:
Who is Participating?
 
ExpertHelp79Connect With a Mentor Author Commented:
sorry for the delayed response.. and also for the "ns" part ... i was quite in a hurry ...
The plan was too big to upload here as the sp was as huge as 10k lines.

After investigating a lot on the background process it was found that the fragmentation level of the tables were quite high and required REBUILD, which actually brought down the resources.

regards
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
ExpertHelp79Author Commented:
no change still high
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
DavidMorrisonCommented:
Can you post the query plan?
0
 
ExpertHelp79Author Commented:
I've requested that this question be deleted for the following reason:

ns
0
 
Anthony PerkinsCommented:
>>ns<<
No feedback.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ExpertHelp79,

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

  please reply with the requested information.

regards,
a3
0
 
Anthony PerkinsCommented:
I suggest then that we close the question by accepting the comment here http:#a36966355
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
agreed
0
 
ExpertHelp79Author Commented:
Thanks :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.