Hi Michael,

The statistics are automaticly updated so I asume they are up to date.
The proc having the time out problem changes. Bur as an example I'll give you three.
The first one (sp_AanwezigheidCategorie) selects records and scans through the result finding records with different CatAan and CatAf. It changes the records and adds records as long as there are still records with different CatAan and CatAf.
The second (sp_SaveProduct) is the starting point of a save of a SubCategorie relationsship. The data is send by XML, the XML is selected into table #t_xml and used for saving in the parent table (producten) and the subcategorie childtables (voedermiddelen, geneesmiddelen, productsamenstelling and meststoffen).
The thirth (sp_SaveSubCategorie) does the childtable save like mentioned above.



CREATE PROCEDURE sp_AanwezigheidCategorie
(
@tiBedrijf Int = 0,
@tdStartDatum Datetime = GetDate ,
@tdEindDatum Datetime = @tdStartDatum
)
AS

Declare @liID Int
Declare @ldGebDatum DateTime
Declare @ldAan DateTime
Declare @ldAf DateTime
Declare @liDagen Int
Declare @liCatAan Int
Declare @liCatAf Int
Declare @liTemp Int
Declare @liNextCat Int
Declare @ldCatDatum DateTime
Create Table #tmpTable (DierID Int, GebDatum DateTime, Aanvoer DateTime, Afvoer DateTime, DierDagen Int, CatAan Int, CatAf Int)
Insert Into #tmpTable
Select DierID, GebDatum, Aanvoer, Afvoer, DierDagen, Cast(CatAan As Int), Cast(CatAf As Int)
From fn_AanwezigheidCategorie (@tiBedrijf, @tdStartDatum, @tdEindDatum, 0)

Declare tmpCur Cursor For Select * From #tmpTable Where CatAan <> CatAf And DierDagen > 0

/* Als de volgende select wat opleverd zijn er dieren die aan het begin een andere categorie
hadden dan aan het eind. Die wijzigingen willen we ook apart in de tabel */
Select @liTemp = DierID From #tmpTable Where CatAan <> CatAf And DierDagen > 0
While @@RowCount > 0
Begin
Open tmpCur
Fetch Next From tmpCur Into @liID, @ldGebDatum, @ldAan, @ldAf, @liDagen, @liCatAan, @liCatAf
While @@fetch_status = 0
Begin
Set @liNextCat =
Case
When @liCatAan = 102 Then 100
When @liCatAan IN (101,103,110,121,124,200,201,300,310,402,405) Then @liCatAan + 1
Else @liCatAan -- deze mag niet voorkomen, maar voor de zekerheid
End
Set @ldCatDatum =
Case
When @liCatAan = 102 Then dbo.fd_LaatsteKalfDatum (@liID, @tdEindDatum)
When @liCatAan IN (101,103,124) Then DateAdd(Year, 1, @ldGebDatum)
When @liCatAan = 110 Then DateAdd(Month, 2, @ldGebDatum)
When @liCatAan = 121 Then DateAdd(Month, 3, @ldGebDatum)
When @liCatAan = 200 Then DateAdd(Week, 6, @ldGebDatum)
When @liCatAan = 201 Then DateAdd(Week, 30, @ldGebDatum)
When @liCatAan = 300 Then DateAdd(Week, 18, @ldGebDatum)
When @liCatAan = 310 Then DateAdd(Week, 19, @ldGebDatum)
When @liCatAan IN (402,405) Then DateAdd(Month, 7, @ldGebDatum)
Else @ldAf -- deze mag niet voorkomen, maar voor de zekerheid
End

Update #tmpTable Set CatAf = @liCatAan, Afvoer = @ldCatDatum - 1,
DierDagen = DateDiff(Day, Aanvoer, @ldCatDatum) + 1
Where Current of tmpCur

Insert Into #tmpTable (DierID, GebDatum, Aanvoer, Afvoer, CatAan, CatAf, DierDagen)
Values (@liID, @ldGebDatum, @ldCatDatum, @ldAf, @liNextCat, @liCatAf,
DateDiff(Day, @ldCatDatum, Coalesce(@ldAf, @tdEindDatum)) + 1)

Fetch Next From tmpCur Into @liID, @ldGebDatum, @ldAan, @ldAf, @liDagen, @liCatAan, @liCatAf
End
Close tmpCur
/* Als de volgende select nog wat opleverd moeten we nog een keer rond */
Select @liTemp = DierID From #tmpTable Where CatAan <> CatAf And DierDagen > 0
End
Deallocate tmpCur

Declare @liPeriode Int
Set @liPeriode = DateDiff(d, @tdStartDatum, @tdEindDatum)
Select Cast(Round(Cast(Dagen As float) / @liPeriode, 0) as Int) As iAantal, 0 As iMaanden,
Cast(Oms.nCode as VarChar(4)) as cCode, 0 as nN, 0 as nP, Oms.cOmschrijving
From (Select CatAan, Sum(DierDagen) As Dagen From #tmpTable Group by CatAan) tmp
Join dbo.fn_Omschrijvingen ('Diersoort') Oms on CatAan = nCode
GO


CREATE PROCEDURE sp_SaveProduct
(
@tiBedrijf Int = 0,
@tiProductID Int = 0,
@tcXML Text = '',
@tiReturnID Int = 0 Output
)
AS

Declare @liProductID Int
Declare @liDoc Int
Declare @lcTableCommand varchar(8000)
Declare @lcValues varchar(8000)
Declare @lcName varchar(255)
Declare @lcValue varchar(8000)

If @tiBedrijf = 0 Set @tiBedrijf = null

Select @liProductID = pk_Producten From Producten
Where pk_Producten = @tiProductID And fk_Bedrijf = @tiBedrijf

If @liProductID <> @tiProductID and @tiProductID > 0 Return

exec sp_xml_preparedocument @lidoc OUTPUT, @tcXML
Select * into #t_xml From OpenXML (@lidoc, '')
exec sp_xml_removedocument @lidoc

/* Nu gaat het opslaan beginnen, eerst tabel Producten */
Declare c_Producten Cursor for Select Localname,
Case
When tmpValue is Null Then 'Null'
When DataType Like '%char' or DataType = 'text' Then '''' + tmpValue + ''''
When DataType = 'datetime' Then '''' + Left(tmpValue, 10) + ''''
Else tmpValue
End As Value
From (Select x1.Localname, Cast(x2.text as varchar(8000)) as tmpValue, lower(col.DATA_TYPE) as DataType
From #t_xml x1 Left Join #t_xml x2 On x1.id = x2.parentid
Join INFORMATION_SCHEMA.COLUMNS col
On lower(col.Table_name) = 'producten' and lower(col.Column_name) = Lower(x1.Localname)
Where x1.nodetype = 1 and x1.ParentID = 0) tmp

open c_Producten
Fetch next from c_Producten Into @lcName, @lcValue
If @@fetch_Status = 0 Begin
If @liProductID is null Begin
/* Hier wordt een insert statement gebouwd */
If @tiBedrijf is null
Begin
Set @lcTableCommand = 'Insert Into Producten ('
Set @lcValues = ' Values ('
End
Else
Begin
Set @lcTableCommand = 'Insert Into Producten (fk_Bedrijf,'
Set @lcValues = ' Values (' + Cast(@tiBedrijf as varChar) + ','
End

While @@fetch_status = 0 Begin
Set @lcTableCommand = @lcTableCommand + @lcName + ','
Set @lcValues = @lcValues + @lcValue + ','
Fetch next from c_Producten Into @lcName, @lcValue
End
Set @lcTableCommand = Left(@lcTableCommand, Len(@lcTableCommand)-1) + ')' + Left(@lcValues, Len(@lcValues)-1) + ')'
End
Else Begin
/* Hier wordt een update statement gebouwd */
Set @lcTableCommand = 'Update Producten Set '
While @@fetch_status = 0 Begin
Set @lcTableCommand = @lcTableCommand + @lcName + '=' + @lcValue + ','
Fetch next from c_Producten Into @lcName, @lcValue
End
Set @lcTableCommand = Left(@lcTableCommand, Len(@lcTableCommand)-1) +
' where pk_producten = ' + cast(@liProductID as varchar(10))
End
End

Execute (@lcTableCommand)

/* Het statement moet een record gewijzigd hebben anders is er iets fout */
If @@RowCount = 0 Return

/* Het opslaan in Producten is gelukt. Het ProductID kan in de return value, die veranderd niet meer*/
If @liProductID is null
Set @tiReturnID = @@Identity
Else
Set @tiReturnID = @liProductID
Deallocate c_Producten

/* En nu verder met de volgende tabellen: Voedermiddelen */
Exec sp_SaveSubCategorie @tiReturnID, 'voedermiddelen', 'fk_Producten'
Exec sp_SaveSubCategorie @tiReturnID, 'geneesmiddelen', 'fk_Producten'
Exec sp_SaveSubCategorie @tiReturnID, 'productsamenstelling', 'fk_Producten'
Exec sp_SaveSubCategorie @tiReturnID, 'meststoffen', 'fk_Producten'

Drop Table #t_xml
GO



CREATE PROCEDURE sp_SaveSubCategorie
(
@tiID Int = 0,
@tcTable varchar(255),
@tcKey varchar(255)
)
AS

Declare @liID Int
Declare @lcTableCommand varchar(8000)
Declare @lcValues varchar(8000)
Declare @lcName varchar(255)
Declare @lcValue varchar(8000)
Declare @lcSelect varchar(255)

Declare c_SubCategorie Cursor for Select Localname,
Case
When tmpValue is Null Then 'Null'
When DataType Like '%char' or DataType = 'text' Then '''' + tmpValue + ''''
When DataType = 'datetime' Then '''' + Left(tmpValue, 10) + ''''
Else tmpValue
End As Value
From (Select x1.Localname, Cast(x2.text as varchar(8000)) as tmpValue, lower(col.DATA_TYPE) as DataType
From #t_xml x1 Left Join #t_xml x2 On x1.id = x2.parentid
Join INFORMATION_SCHEMA.COLUMNS col
On lower(col.Table_name) = @tcTable and lower(col.Column_name) = Lower(x1.Localname)
Where x1.nodetype = 1 and not x1.ParentID is null) tmp

open c_SubCategorie
If @@Cursor_Rows <> 0 Begin
-- Set @lcSelect = 'Declare @liDummy Int Set @liDummy = (Select distinct ' + @tcKey + ' From ' + @tcTable + ' Where ' + @tcKey + ' = ' + Cast(@tiID as varchar(10)) + ')'
Set @lcSelect = 'Select ' + @tcKey + ' From ' + @tcTable + ' Where ' + @tcKey + ' = ' + Cast(@tiID as varchar(10))
Exec (@lcSelect)
If @@RowCount = 0
Set @liID = null
Else
Set @liID = @tiID

Fetch next from c_SubCategorie Into @lcName, @lcValue
If @@fetch_Status = 0 Begin
If @liID is null Begin
/* Hier wordt een insert statement gebouwd */
Set @lcTableCommand = 'Insert Into ' + @tcTable + ' (' + @tcKey + ','
Set @lcValues = ' Values (' + Cast(@tiID as VarChar(10)) + ','
While @@fetch_status = 0 Begin
Set @lcTableCommand = @lcTableCommand + @lcName + ','
Set @lcValues = @lcValues + @lcValue + ','
Fetch next from c_SubCategorie Into @lcName, @lcValue
End
Set @lcTableCommand = Left(@lcTableCommand, Len(@lcTableCommand)-1) + ')' + Left(@lcValues, Len(@lcValues)-1) + ')'
End
Else Begin
/* Hier wordt een update statement gebouwd */
Set @lcTableCommand = 'Update ' + @tcTable + ' Set '
While @@fetch_status = 0 Begin
Set @lcTableCommand = @lcTableCommand + @lcName + '=' + @lcValue + ','
Fetch next from c_SubCategorie Into @lcName, @lcValue
End
Set @lcTableCommand = Left(@lcTableCommand, Len(@lcTableCommand)-1) +
' where ' + @tcKey + ' = ' + cast(@liID as varchar(10))
End
End
Execute (@lcTableCommand)
End
Deallocate c_SubCategorie
GO


>How about posting the proc and we'll start from there. In the meantime, verify that you're statistics are up to date.
>
>-Mike
>
>>I have a performance problem with SQL Server 2000. Here is the scenario:
>>A DLL (on a webserver) executes a stored procedure (on a DB server) over a ODBC connection. Every now and than this DLL encounters Time Out problems (The time out is 1 minute) In that case I execute the sp in de Query Analyzer (no time out) and it works fine, but indeed very slow; like 10 minutes. If I execute the same sp again it finishes within 5 seconds. The DLL now works fine again until some other sp (or the same) start timing out.
>>
>>The only cause I can think of is Caching. Once SQL Server has the appropriate tables cashed the sp finishes faster. But the difference between 10 minutes and 5 seconds is very big.
>>
>>My questions are:
>>* Are there other reason that could cause this behaviour?
>>* How do I find out wich is the actual reason.
>>* What can I do about this (beside adding memory or setting time out to 20 minutes)