Link to home
Start Free TrialLog in
Avatar of Robb Hill
Robb HillFlag for United States of America

asked on

Query Peformance + mulitple query plans

I have the following Stored Proc that I have inherited.
I have been running some peformance tools and this stored proc keeps coming up as doing multiple query plans.

Any ideas on a way to refactor this sp for efficiency.

Thanks,


ALTER procedure [dbo].[spGetMRUDocList2]

		@nSubScr int
	, @cRootSite varchar(50)
	, @nMaxRecords int = 10

	
as
	set nocount on 

		declare @MRUClearDateDocuments datetime
		select @MRUClearDateDocuments = MRUClearDateDocuments from tSubscr where nid = @nSubScr

		declare @30date datetime
		set @30date = dateadd(day, -30, getdate())
		
		
		if @30date >= @MRUClearDateDocuments
			set @MRUClearDateDocuments = @30date
							
			select top (@nMaxRecords) e.cFileAreaSiteCode, e.ctitle, e.cdocumentid, c.nid, c.cName, c.cFEIN, e.dCreated 
			from tevents e
			join tSubscr s on e.nSubScr = s.nid
			join tSiteXCrmClient x on e.cFileAreaSiteCode = cast(x.nidSite as varchar(6))
			join cadoc_crm..tclient c on c.nid = x.nIdClient
			where s.nid = @nSubScr
			and e.dCreated >= s.MRUClearDateDocuments
			and e.cdocumentid like 'DOC%'
			and e.ctitle != ''
			and e.dCreated in 
			(select max(ee.dCreated) from tevents ee
			join tSubscr ss on ee.nSubScr = ss.nid
			where ss.nid = @nSubScr
			and ee.cSiteCode = @cRootSite
			group by ee.cDocumentID )
			and c.cSiteCode = @cRootSite
			and e.cFileAreaSiteCode not like 'CAT%'
			order by e.dCreated desc

Open in new window

Avatar of ste5an
ste5an
Flag of Germany image

Without  more information: The culprit is that cast e.cFileAreaSiteCode = cast(x.nidSite as varchar(6))
. Which should lead to a non-sargable predicate.

And for the rest: Use proper, covering indices.
I have been running some peformance tools and this stored proc keeps coming up as doing multiple query plans.
It can be if the SP has more than one SQL command but we can't confirm that since looks like you didn't post all the code from the SP.
Avatar of Robb Hill

ASKER

That is the entire SP.

How would I resolve the CAST issue?
By correcting your data model, if possible.
no ..that would not be possible in this case....atleast not anytime soon.
Can you show an example of those multiple query plans?
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

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
SOLUTION
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
Thanks so much!!!