Hi Michael,

The SHOW_STATISTICS shows the statistics are updated last on July 1 2002. That is not very recent, but on this test machine very little changes occur. I did the UPDATE STATISTICS and wil have to wait and see if that changed anything.

I'm afraid all the ugly code is neccesary for now. I think it should be able to work set-oriented, but I haven't figured it out yet. Maybe you have a good idea if I explain what the code is meant to do? I'll take the sp_AanwezigheidCategorie first (added below again). This proc selects records wich represent cows into table #tmpTable. This table no has the ID of the animal (DierID), the date of birth (GebDatum), the date of arrival (Aanvoer), the date of departure (Afvoer), number of days present (DierDagen) and finally two codes (CatAan and CatAf). These codes represent the type of cow (calf, 1 year old, older cow etc.) the animal was at arrivel (CatAan) or departure (CatAf).
What I need to calculate is the number of days the animal was present as every code. The records that have equal CatAan and CatAf hold this number in DierDagen. But the records with different CatAan and CatAf can't be calculated yet. I can calculate when a animal moves from one code to the other by using its birthday. In this proc I select all records of the animals with different CatAan and CatAf codes. Then I calculate when the animal moves from the startcode (CatAan) to the next code. Now I update the original record by replacing CatAf with CatAan. I insert a new record with the code the animal move to as CatAan and the previous CatAf as CatAf. The dates move to. If the animal only moved to an other code this one is ready now, but if the animal moved through more categories the record wil again be selected in the following round and will go though this process again.

At the end all records have equal CatAan and CatAf so I can calculate the average number of animals of every Cat by summing the days and deviding that by the length of the period.

Does this make some sense now? I'v seen people do wonders with Set-Oriented sql, but this one never made it.

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


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