>The statistics are automaticly updated so I asume they are up to date.

Not necessarily. When a table is more than 1024 pages, autostats does a sampling and the algorithm can be quite crazy. You can use DBCC SHOW_STATISTICS() to determine whether a sampling is being done or not. If it is, try doing an explicit UPDATE STATISTICS WITH FULLSCAN to see if there's any difference.

As for the code. Unfortunately I don't read Dutch so I can't make a lot of recommendations. I can see that you're doing a lot of things that we try to stay away from: temporary table, cursors, and dynamic SQL. Are you sure that you can't rewrite these in a set-oriented fashion. You will see MUCH MUCH better performance from just doing that.

-Mike



>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)