SQL Server Performance Forum – Threads Archive

sp suddenly gets slow until recompile

Hello, I am calling a stored procedure from an ASP.NET 2 page.
This stored procedure uses a user defined function.
This is on Win 2003 with SQL Server 2000 Workgroup Edition. Generally performance is OK, but sometimes the page starts loading very slowly or even starts generating SQL Exception "TimeOout Expired". When I execute the stored procedure in Query Analyer while the ASP page gives timeout error, execution time within Query Analyzer seems normal. Recycling the ASP.NET application does not help.
Killing active process in Entreprise Manager does not help.
Stoppping/Restarting SQL Server instance does not (always ?) help.
But making a small change in the stored procedure always helps, even if I only execute the modified procedure once, and then restore the original code, performance remains OK. Any hints for troubleshooting this ? ASP.NET Page code —————————————————————- Dim Connection As New SqlConnection
Connection.ConnectionString = ConfigurationManager.AppSettings("ConnectionString")
Dim Command As New SqlCommand
Command.Connection = Connection
Command.CommandType = CommandType.StoredProcedure
Command.CommandText = "sprsRechercheSite"
Command.Parameters.AddWithValue("@Du", _VS.ArriveeDate)
Command.Parameters.AddWithValue("@Au", _VS.DepartDate)
Command.Parameters.AddWithValue("@Order", _VS.Order)
If _VS.Pers > 0 Then Command.Parameters.AddWithValue("@Pers", _VS.Pers)
If ForOneAddress Then
Command.Parameters.AddWithValue("@AgriturismoID", _VS.AgriturismoID)
Else
If _VS.Region <> "0" Then Command.Parameters.AddWithValue("@Region", _VS.Region)
If _VS.ProvinceID <> "0" Then Command.Parameters.AddWithValue("@ProvinceID", CInt(_VS.ProvinceID))
If _VS.TypeLogement <> "0" Then Command.Parameters.AddWithValue("@Type", _VS.TypeLogement)
If _VS.Piscine Then Command.Parameters.AddWithValue("@Piscine", 1)
If _VS.TableHote Then Command.Parameters.AddWithValue("@TableHotes", 1)
If _VS.Animaux Then Command.Parameters.AddWithValue("@Animaux", "Admis")
End If Connection.Open()
Dim Reader As SqlDataReader = Command.ExecuteReader(CommandBehavior.CloseConnection) … Do While Reader.Read()
‘if new address
If AgriturismoID <> CInt(Reader("AgriturismoID")) Then ‘if not first time new address add previous row
If AgriturismoID > 0 Then
AddAgriturismoRow(ListNumber, ProvinceNom, AgriturismoID, AgriturismoNom, LogementsDescription, Piscine, TableHotes, Table)
End If ‘add separator row (only after first address)
If Number > 0 Then
Table.Rows.Add(SeparatorRow())
End If ‘initialise new address
LogementsDescription.Clear()
If _VS.Region = "0" Then
ProvinceNom = CStr(Reader("Region")) & ", " & CStr(Reader("Province"))
Else
ProvinceNom = CStr(Reader("Province"))
‘VS.ProvinceNom = ProvinceNom
End If
AgriturismoID = Reader("AgriturismoID").ToString
‘TODO s’il faut enlever le nom de l’adresse
AgriturismoNom = UCase(Reader("Agriturismo").ToString)
‘AgriturismoNom = UCase(Reader("Localite").ToString)
Piscine = Reader("Piscine").ToString
TableHotes = Reader("Souper").ToString ‘check if same agriturismo already received a number
‘if so use that number, otherwise increment
Dim Index As Integer = Array.IndexOf(Numbers, AgriturismoID)
If Index >= 0 Then
ListNumber = Index
Else
Number = Number + 1
Numbers(Number) = AgriturismoID
ListNumber = Number Dim TopPos As Integer = CInt(Reader("TopPos"))
Dim LeftPos As Integer = CInt(Reader("LeftPos")) Dim Result As New Result
Result.AgriturismoID = AgriturismoID
Result.TopPos = TopPos
Result.LeftPos = LeftPos
Result.AgriturismoNom = AgriturismoNom
_List.Add(Result) End If ‘add first accommodation
LogementsDescription.Add(LogementRow(AgriturismoID, Reader("Nombre"), Reader("Type"), Reader("Pers"), _
Reader("PrixTotal"), Reader("Dispo"), Reader("LogementID"))) Else
‘add other accommodations
LogementsDescription.Add(LogementRow(AgriturismoID, Reader("Nombre"), Reader("Type"), Reader("Pers"), _
Reader("PrixTotal"), Reader("Dispo"), Reader("LogementID"))) End If
Loop Reader.Close()
Connection.Close() ———————————————————————— ALTER PROCEDURE dbo.sprsRechercheSite
(
@Region nvarchar(50) = ‘%’,
@ProvinceID varchar(2) = ‘%’,
@Type varchar(20) = ‘%’,
@Du datetime,
@Au datetime,
@Pers int = 0,
@Piscine char(1) = ‘%’,
@TableHotes char(1) = ‘%’,
@Animaux varchar(15)= ‘%’,
@Order nvarchar(20) = ‘DispoPrix’,
@AgriturismoID varchar(4) = ‘%’
)
AS
SET NOCOUNT ON
DECLARE @PersMin int
DECLARE @PersMax int
DECLARE @PersOccup int
DECLARE @GetDate datetime
SET @GetDate = Getdate()
IF @AgriturismoID <> ‘%’
BEGIN
SET @PersMin = 0
SET @PersMax = 99
END
ELSE
BEGIN
IF @Pers < 10 and @Pers > 0
BEGIN
SET @PersMin = @Pers
SET @PersMax = @Pers + 2
END
ELSE
BEGIN
SET @PersMin = @Pers
SET @PersMax = 99
END
END
IF @Pers > 0 AND @Pers < 9 SET @PersOccup = @Pers ELSE SET @PersOccup = -1SELECT
Region,
Province,
AgriturismoID,
TopPos,
LeftPos,
Nom as Agriturismo,
Localite,
Piscine,
Souper,
LastPhotoModif,
Min(LogementID) as LogementID,
Count(LogementID) As Nombre,
Type,
Pers,
PrixTotal,
Type_de_periode,
Unite_de_logement,
Dispo,
dbo.fscDisposort(Dispo),
CASE
WHEN @Order = ‘DispoPrix’ THEN dbo.fscDisposort(Dispo) + Str(IsNull(PrixTotal,0),5,0)
WHEN @Order = ‘DispoSituation’ THEN str(dbo.fscDisposort(Dispo),1,0) + Str(TopPos,4,0) + Str(LeftPos,4,0)
WHEN @Order = ‘Prix’ THEN str(IsNull(PrixTotal,99999),5,0)
WHEN @Order = ‘Situation’ THEN Province + Str(TopPos,4,0) + Str(LeftPos,4,0)
END AS SortOrder
FROM ftbListeAvecDates
(
@ProvinceID,
@Type,
@PersMin,
@PersMax,
@PersOccup,
@Du,
@Au,
@Piscine,
@TableHotes,
@Animaux,
–Default, [email protected],
–Default, [email protected],
@GetDate,
@AgriturismoID,
Default, –LogementID
@Region
)
GROUP BY
Region,
Province,
AgriturismoID,
TopPos,
LeftPos,
Nom,
Localite,
Piscine,
Souper,
LastPhotoModif,
Type,
Pers,
PrixTotal,
Type_de_periode,
Unite_de_logement,
Dispo
ORDER BY
SortOrder
RETURN ————————————————- ALTER FUNCTION dbo.ftbListeAvecDates
(
@ProvinceID varchar(2) = ‘%’,
@Type varchar(20) = ‘%’,
@PersMin int = 0, — utilisÃ© pour recherche
@PersMax int = 99, — utilisÃ© pour recherche
@PersOccup int = -1, — utilisÃ© pour le calcul du prix
@Du datetime,
@Au datetime,
@Piscine char(1) = ‘%’,
@TableHotes char(1) = ‘%’,
@Animaux varchar(15) = ‘%’,
@GetDate datetime,
@AgriturismoID varchar(4) = ‘%’,
@LogementID varchar(10) = ‘%’,
@Region varchar(50) = ‘%’
)
RETURNS TABLE
AS RETURN (
SELECT
PR.Region,
PR.ProvinceID,
PR.Nom as Province,
A.AgriturismoID,
A.Nom,
A.Localite,
A.Piscine,
A.Souper,
A.Equitation,
A.VTT,
A.LastPhotoModif,
C.Toppos,
C.Leftpos,
L.LogementID,
L.Nom as LNom,
L.Type,
L.Pers,
LP.PrixEuro,
LPAP.PrixEuro as PrixEuroAP,
LPF.PrixEuro as PrixFin,
isnull(LP.PrixEuroPPPN,LPAP.PrixEuroPPPN) as PRixEuroPPPN,
LP.Type_de_periode,
LP.Unite_de_logement,
P.Debut,
P.Fin,
R.ReservationID as reservationID,
U.LastLogin,
A.LastUpdateDispo,
U.Dispo as online,
dbo.fscPrixTotal
(
LP.PrixEuro,
LPAP.PrixEuro,
LPF.PrixEuro,
LPFAP.PrixEuro,
@PersOccup,
Pers,
@Du,
@Au,
LP.Type_de_periode,
LPAP.Type_de_periode,
LPF.Type_de_periode,
LPFAP.Type_de_periode,
LP.Unite_de_logement,
LPAP.Unite_de_logement,
LPF.Unite_de_logement,
LPFAP.Unite_de_logement,
P.Fin,
PAP.Fin,
PF.Debut,
PFAP.Debut
)
as PrixTotal,
dbo.fscDispo(
dbo.fscPrixTotal
(
LP.PrixEuro,
LPAP.PrixEuro,
LPF.PrixEuro,
LPFAP.PrixEuro,
@PersOccup,
Pers,
@Du,
@Au,
LP.Type_de_periode,
LPAP.Type_de_periode,
LPF.Type_de_periode,
LPFAP.Type_de_periode,
LP.Unite_de_logement,
LPAP.Unite_de_logement,
LPF.Unite_de_logement,
LPFAP.Unite_de_logement,
P.Fin,
PAP.Fin,
PF.Debut,
PFAP.Debut
),
R.ReservationID,
U.LastLogin,
A.LastUpdateDispo,
@GetDate,
isnull(LP.Type_de_periode,LPAP.Type_de_periode),
@Du,
@Au,
U.Dispo)
as Dispo
FROM Logements as L (NOLOCK)
LEFT JOIN Reservationsas R (NOLOCK) ON (L.logementID = R.LogementID and ((Du >= @Du and Du < @Au) or (Au > @Du and Au <= @Au) or (Du < @Du and Au > @Au)))
LEFT OUTER JOIN Periodes_de_prix as P (NOLOCK) ON (L.AgriturismoID = P.AgriturismoID and (P.Debut <= @Du and P.Fin > @Du))
LEFT OUTER JOIN Periodes_de_prix as PAP (NOLOCK) ON (L.AgriturismoID = PAP.AgriturismoID and (PAP.Debut <= DateAdd(Year,-1,@Du) and PAP.Fin > DateAdd(Year,-1,@Du)))
LEFT OUTER JOIN Periodes_de_prix as PF (NOLOCK) ON (L.AgriturismoID = PF.AgriturismoID and (PF.Debut <= @Au and PF.Fin > @Au))
LEFT OUTER JOIN Periodes_de_prix as PFAP (NOLOCK) ON (L.AgriturismoID = PFAP.AgriturismoID and (PFAP.Debut <= DateAdd(Year,-1,@Au) and PFAP.Fin > DateAdd(Year,-1,@Au)))
LEFT OUTER JOIN Logements_Prix as LP (NOLOCK) ON (P.PeriodePrixID = LP.PeriodePrixID and L.LogementID = LP.LogementID)
LEFT OUTER JOIN Logements_Prix as LPAP (NOLOCK) ON (PAP.PeriodePrixID = LPAP.PeriodePrixID and L.LogementID = LPAP.LogementID)
LEFT OUTER JOIN Logements_Prix as LPF (NOLOCK) ON (PF.PeriodePrixID = LPF.PeriodePrixID and L.LogementID = LPF.LogementID)
LEFT OUTER JOIN Logements_Prix as LPFAP (NOLOCK) ON (PFAP.PeriodePrixID = LPFAP.PeriodePrixID and L.LogementID = LPFAP.LogementID)
LEFT JOIN Utilisateurs as U (NOLOCK) ON (L.AgriturismoID = U.AgriturismoID)
JOIN Coordonnees as C (NOLOCK) ON (L.AgriturismoID = C.AgriturismoID)
JOIN Agriturismo as A (NOLOCK) ON L.AgriturismoID = A.AgriturismoID
JOIN Provinces as PR (NOLOCK) ON PR.ProvinceID = A.ProvinceID
WHERE
(L.Actif=1)
AND (A.Actif=1)
AND L.Type Like @Type
AND L.Pers >= @PersMin
AND L.Pers <= @PersMax
AND L.LogementID Like @LogementID
AND A.Piscine Like @Piscine
AND A.Souper Like @TableHotes
AND A.Animaux Like @Animaux
AND (A.AgriturismoID Like @AgriturismoID)
AND (A.ProvinceID Like @ProvinceID)
AND (PR.Region Like @Region) )

That’s a lot of code. My suggestion, create a baseline runtime for the stored procedure using Query Analyzer and create a timer for each major call with debug output before and after the call. See where the difference is. May the Almighty God bless us all!
www.empoweredinformation.com

Have you tried recompiling the stored procedure to see if it helps? How often do you reindex and update statistics on this database? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

]]>