Robb Hill
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,
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
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.
ASKER
That is the entire SP.
How would I resolve the CAST issue?
How would I resolve the CAST issue?
By correcting your data model, if possible.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks so much!!!
. Which should lead to a non-sargable predicate.
And for the rest: Use proper, covering indices.