[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

The query is taking very high resources

Posted on 2011-10-12
12
Medium Priority
?
259 Views
Last Modified: 2012-08-14
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

0
Comment
Question by:ExpertHelp79
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36955080
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
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 36955167
no change still high
0
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 36955261
Can you post the query plan?
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 2

Author Comment

by:ExpertHelp79
ID: 36960471
I've requested that this question be deleted for the following reason:

ns
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36959573
>>ns<<
No feedback.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36960472
ExpertHelp79,

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

  please reply with the requested information.

regards,
a3
0
 
LVL 2

Accepted Solution

by:
ExpertHelp79 earned 0 total points
ID: 36966355
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36970339
I suggest then that we close the question by accepting the comment here http:#a36966355
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36970788
agreed
0
 
LVL 2

Author Comment

by:ExpertHelp79
ID: 36973954
Thanks :)
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Integration Management Part 2

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question