July 16, 2012 at 5:26 am
MS SQL Server 2005
The stored procedure below was recently changed with the dynamic SQL added. It was added because the SPROC resides on a server where data is replicated to it. The replicated data does not get there fast enough to reflect the updates on the Accounts.dbo.Address table. Therefore the temporary table #Locations was added to get the information from Accounts.dbo.Address. The Employers table in it resides only on the server it is executed on (CMISQL).
In other words the Accounts.dbo.Address table is on one server and the Employer date only resides on CMISQL.dbo.Employers. The address table is replicated to CMISQL but when an update is done the data does not replicate fast enough for the application so the application does not always reflect the update.
Whether or not that was a good idea or not it has caused issues. After about a day of use the SPROC slows down to about a six second delay. Recompiling it gets it back to “normal” which is about two seconds. The DBA group set up a nightly job that recompiles it and so as far as the user community is concerned it is the same response time consistently. My question is would I benefit from adding “With NoCompile” to the execution from the application.
For example “EXEC grEmployerTab3 ‘12345’ With NoCompile”
The reason the SQL is dynamic is so the server does not need to be hardcoded in the SPROC. I didn’t know of another way to achieve that other than checking the server it is executing on and assigning the server that way.
I’m also open to suggestions if someone sees why it needs recompiled once a day to keep the performance consistent.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[grEmployerTab3] (@Policy int)
AS
set nocount on
Declare
@EffectiveDate varchar(11),
@EmpStatus varchar(25),
@NoteDate smalldatetime,
@fkLinkEmployerRate int,
@server varchar(128)
DECLARE @sqlquery AS VARCHAR(1000)
DECLARE @sqlserver AS VARCHAR(50)
Select
@EffectiveDate = isnull(EffectiveDate,''),
@EmpStatus = Description
From
Employers_Status es,
refEmployerStatus s
Where
es.StatusID = s.pkEmployerStatus
and es.EmployerID = @Policy
select @server = rtrim(@@servername)
set @fkLinkEmployerRate = (Select fkLinkEmployerRate from Employers where EmployerId = @Policy)
if @server = 'CMISQL' or @server = 'COMMONSQL'
Exec [cmisql\Commonsql].Notes.dbo.Rate_GetLastNoteForPolicy
@fkLinkEmployerRate,
@NoteDate Output
else if @server like 'UASQL%' or @server like 'TESTSQL%'
Exec [uasql\Commonsql].Notes.dbo.Rate_GetLastNoteForPolicy
@fkLinkEmployerRate,
@NoteDate Output
else if @server like 'DEVSQL%'
Exec [Devsql\Common1].Notes.dbo.Rate_GetLastNoteForPolicy
@fkLinkEmployerRate,
@NoteDate Output
if @server = 'CMISQL' or @server = 'COMMONSQL'
set @server = '[CMISQL\CommonSQL]'
else if @server like 'UASQL%' or @server like 'TESTSQL%'
set @server = '[UASQL\CommonSQL]'
else if @server like 'DEVSQL%'
set @server = '[DEVSQL\COMMON1]'
If object_id('tempdb..#Location') is not null drop table #Location
Create table #Location(
pklocation int, fkid int, fktype char(1), address1 varchar(50), address2 varchar(50),
city Varchar(50),state varchar(2), zip varchar(9), phone varchar(10), fax varchar(10),
primarylocation tinyint, website char(1), userlup varchar(20), datelup datetime,
note varchar(255), fkAddresstype int)
set @sqlquery =
'insert into #Location
select
pkLocation= pkAddress
,fkID= fkGroupRatingID
,fkType= GroupRatingType
,Address1= Address1
,Address2= Address2
,City= City
,[State]= [State]
,Zip= Zip
,Phone= Phone
,Fax= Fax
,PrimaryLocation= case when GroupRatingMailingAddress = 1 then 1 else 0 end
,WebSite= ''''
,UserLup= UserLup
,DateLup= DateLup
,Note= MemoText
,fkAddressType= 0
from ' + @server + '.Accounts.dbo.Address
where PrimaryAddress <> 2
and fkGroupRatingID = ' + convert(varchar,@Policy) +
' and GroupRatingType = ''e'''
exec (@sqlquery)
set nocount off
Select
e.dba,
e.LegalName,
e.PolicyNo,
e.BusSeqNo,
l.Address1,
l.Address2,
l.City,
l.State,
l.Zip,
l.pHone,
l.Fax,
l.WebSite,
l.Note, --jfrease 4.14.05
l.pkLocation,
r.Description,
e.EmployerID,
@EffectiveDate as EffectiveDate,
@EmpStatus as EmpStatus,
@NoteDate as noteDate,
isnull(e.PublicEmployer,0) as PublicEmployer,
isnull(e.fkJurisdiction,1) as fkJurisdiction,
e.UBINumber
,Tier = IsNull((Select fkTier from dbo.AccountsEmployer ae
where ae.PolicyNumber = convert(varchar(20),e.PolicyNo) + '-' + convert(varchar(20),e.BusSeqNo)),'')
,TPATerminationDate = IsNull((Select ae.TPATerminationDate from dbo.AccountsEmployer ae
where ae.PolicyNumber = convert(varchar(20),e.PolicyNo) + '-' + convert(varchar(20),e.BusSeqNo)
and year(ae.TPATerminationDate) > '1900'),'')
,CHS = IsNull((Select 'Yes' from dbo.AccountsEmployer ae
where ae.PolicyNumber = convert(varchar(20),e.PolicyNo) + '-' + convert(varchar(20),e.BusSeqNo)
and IsNull(ae.MCOOrgId,0) = 5130
and IsNull(ae.MCOTerminationDate,'2050-01-01') >= GetDate()
and IsNull(ae.MCOEffectiveDate,'01/01/1900') <= GetDate()),'No')
,fkpolicybusinesstype = IsNull((Select fkpolicybusinesstype from dbo.AccountsEmployer ae
where ae.PolicyNumber = convert(varchar(20),e.PolicyNo) + '-' + convert(varchar(20),e.BusSeqNo)),'')
,isnull(e.AssocMbrID,0) as AssocMbrID
,isnull(e.BoardMember,'N') as BoardMember
,l.pkLocation
,'primaryLocation' = (select case when PrimaryLocation = 1 then 'Yes' else 'No' end)
From
Employers e
left join #Location l
on e.EmployerID = l.fkID
and l.fkType = 'e'
left join refAddressType r
on l.fkAddressType = r.pkAddressType
Where e.EmployerID = @Policy
Group by
e.policyNo ,
e.BusSeqNo,
e.legalname,
e.dba,
l.address1,
l.address2,
l.city,
l.state,
l.zip,
l.phone,
l.fax,
l.website,
l.note,
l.pklocation,
r.Description,
e.EmployerID,
e.PublicEmployer,
e.fkJurisdiction,
e.UBINumber,
e.AssocMbrId,
e.BoardMember,
l.pkLocation,
PrimaryLocation
Return
GO
July 16, 2012 at 5:32 am
The short answer is, there's no such hint as "NoCompile" and trying to run "EXEC <SomeProcedure> WITH NoCompile" will just get you an error message saying "Incorrect syntax near 'nocompile'."
As for the recompile daily, hard to say anything without seeing exec plans of a slow run and a fast.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2012 at 5:55 am
GilaMonster (7/16/2012)
The short answer is, there's no such hint as "NoCompile" and trying to run "EXEC <SomeProcedure> WITH NoCompile" will just get you an error message saying "Incorrect syntax near 'nocompile'."As for the recompile daily, hard to say anything without seeing exec plans of a slow run and a fast.
Sorry..I saw it on a web site and mistyped it. It should be:
exec grEmployerTab3 467 with recompile
and that does work. I need to work with the DBA group to get a fast and slow run. That would take time in test and I can't slow down production just for that.
Based on it needs to be recompiled everynight I was thinking the recompile option does the same thing the nightly job does. I'm wondering if once a day is better than everytime. I can test that myself, I think, by comparing running it with the option on and off. That would just be me looking at my watch.
July 16, 2012 at 6:01 am
tyson.price (7/16/2012)
Based on it needs to be recompiled everynight I was thinking the recompile option does the same thing the nightly job does. I'm wondering if once a day is better than everytime. I can test that myself, I think, by comparing running it with the option on and off. That would just be me looking at my watch.
If you only need a recompile once a day, then only recompile it once a day. Otherwise you're spending resources compiling and compiling for no gain in performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2012 at 6:52 am
GilaMonster (7/16/2012)
tyson.price (7/16/2012)
Based on it needs to be recompiled everynight I was thinking the recompile option does the same thing the nightly job does. I'm wondering if once a day is better than everytime. I can test that myself, I think, by comparing running it with the option on and off. That would just be me looking at my watch.If you only need a recompile once a day, then only recompile it once a day. Otherwise you're spending resources compiling and compiling for no gain in performance.
I posted this on two of my favorite SQL forums (this one and another) and got the same response from both. Looks like it stays in place 😀
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply