[MSSQL] Rijen om en om updaten

[P_de_B]

Ik krijg een tabel met tijden uit een klok applicatie. De tabel ziet er als volgt uit:

code:

CREATE TABLE [TimeKeeperTussenTabel] (
    [TimeKeeperID] [bigint] IDENTITY (1, 1) NOT NULL ,
    [Pasnummer] [int] NOT NULL ,
    [DatumTijd] [datetime] NOT NULL ,
    [InOfUit] [char] (1) COLLATE Latin1_General_CI_AS NOT NULL ,
    [Handled] [bit] NOT NULL ,
    [IsEersteBoeking] [bit] NOT NULL )
GO

Een voorbeeldje van de data (dit betreft slechts 1 pasnummer, er zijn er vele honderden)

code:

    13491   2365    06-04-2005 15:38:00     0   0
    13661   2365    06-04-2005 12:51:00     0   0
    13976   2365    06-04-2005 11:55:00     0   0
    14369   2365    06-04-2005 7:57:00  b   0   1
    14816   2365    05-04-2005 16:58:00     0   0
    15491   2365    05-04-2005 12:54:00     0   0
    15815   2365    05-04-2005 11:57:00     0   0
    16224   2365    05-04-2005 7:55:00  b   0   1
    16656   2365    04-04-2005 17:09:00     0   0
    17174   2365    04-04-2005 12:17:00     0   0
    17598   2365    04-04-2005 12:00:00     0   0
    18448   2365    04-04-2005 7:56:00  b   0   1

Het veld IsEersteBoeking geeft aan of een klokactie de eerste boeking is van een dienst. Het veld InOfUit geeft aan of een klokactie een 'In' boeking is (b) of een 'uit' boeking (e). Logischerwijs is de waarde van InOfUit 'B' voor elk record waarbij IsEersteBoeking op 1 staat.
Ik moet nu de overige records om en om updaten met 'b' of 'e'. In bovenstaand voorbeeld zou voor 4 april de volgende gegevens correct zijn:

code:

    16656   2365    04-04-2005 17:09:00  e  0   0
    17174   2365    04-04-2005 12:17:00  b  0   0
    17598   2365    04-04-2005 12:00:00 e   0   0
    18448   2365    04-04-2005 7:56:00  b   0   1

Het eerste record van de dienst per is dus alstijd een b, de volgende e dan weer een b etc. Dit geldt dus per pasnummer.

Het volgende script doet wat ik wil, maar is erg traag:

code:

UPDATE TimeKeeperTussenTabel
SET InOfUit =  CASE(
            SELECT Count(*) FROM TimeKeeperTussenTabel WHERE DatumTijd <= T1.DatumTijd AND Pasnummer = T1.Pasnummer AND DatumTijd > 
                (SELECT TOP 1 DatumTijd FROM TimeKeeperTussenTabel WHERE DatumTijd < T1.DatumTijd AND IsEersteBoeking = 1 AND Pasnummer =T1.Pasnummer ORDER BY DatumTijd DESC))
        %2 WHEN 1 THEN 'E' ELSE 'B' END
FROM TimeKeeperTussenTabel T1
WHERE Handled = 0 AND IsEersteBoeking = 0

Heeft iemand een beter idee?

[justmental]

Dit lijkt me het beste op een programmatische manier op te lossen.
Dus niet de hele functionaliteit in SQL, maar alleen een cursor om de tabel te lezen.
Doordat je de vorige waarde vast kunt houden in een variabele hoeft deze er niet telkens bij gezcoht te worden.
Als het veel gegevens zijn om te updaten heb je misschien een bulk update functionaliteit nodig, ik weet niet of MSSQL die heeft.

[curry684]

Volgens mij kun je simpel en snel je doel bereiken als je snapt waarom dit stukje SQL iets anders doet dan je in eerste instantie zou verwachten:

code:

declare @idx int;
set @idx = 0;
update Test set @idx = @idx + 1, Field = @idx;

[P_de_B]

curry684 schreef op donderdag 07 april 2005 @ 16:47:
Volgens mij kun je simpel en snel je doel bereiken als je snapt waarom dit stukje SQL iets anders doet dan je in eerste instantie zou verwachten:

code:

declare @idx int;
set @idx = 0;
update Test set @idx = @idx + 1, Field = @idx;

Nee, iets vergelijkbaars had ik all, ik kan alleen (volgens mij) niet de volgorde garanderen van de update.

Ik had het volgende:

code:

UPDATE TimeKeeperTussenTabel
SET InOfUit = @Var,  @var = case @var when 'B' then 'E' else 'B' end, Handled = 1
FROM TimeKeeperTussenTabel 
WHERE Timekeepertussentabel.Handled = 0 AND Timekeepertussentabel.IsEersteBoeking = 0

Ik moet echter de volgorde kunnen bepalen, en dat mag niet in een update afaik. Ik heb nog geprobeerd te joinen met een derived table om zo de volgorde te garanderen maar dat ging niet goed. Ook met de FORCE ORDER query hint, kreeg ik nog vreemde resultaten. Sommige stukken waren goed, terwijl andere ineens 3 b's achter elkaar kregen.

Of heb ik je nu helemaal verkeerd begrepen?

[curry684]

P_de_B schreef op donderdag 07 april 2005 @ 16:53:
[...]

Nee, iets vergelijkbaars had ik all, ik kan alleen (volgens mij) niet de volgorde garanderen van de update.

Update loopt alle rows over in de 'table order', die bepaald wordt door.... de clustered index. Door het tijdelijk verplaatsen van de clustered index kun je het dus sowieso bepalen

[P_de_B]

curry684 schreef op donderdag 07 april 2005 @ 17:00:
[...]

Update loopt alle rows over in de 'table order', die bepaald wordt door.... de clustered index. Door het tijdelijk verplaatsen van de clustered index kun je het dus sowieso bepalen

Dat weet ik, maar de tabel bestaat uit heel veel rijen, en deze update vind erg vaak plaats. Ik ben bang dat dat geen optie is.

De clustered index volgorde komt helaas niet overeen met de DatumTijd volgorde.

[Annie]

Aangezien je een conditional update wil doen over veel data, zal het denk ik nooit echt snel gaan. Maar als je die subselects kwijt bent dan zal het al een stuk sneller reageren, denk ik.

Is het een optie om de gegevens over te pompen naar een kopie en daarna de oude tabel te droppen? In dat geval zou iets onderstaande misschien werken. het is een ruwe schets en uit de losse pols, dus geen idee of het ook verdere uitwerking rechtvaardigt. Ik heb geen mogelijkheid om wat te testen/fröbelen.

1. Maak een tijdelijk tabel
2. Selecteer de gegevens uit je tabel in de kopie, op volgorde van pasnummer, datum en IsEersteBoeking en laat een tellertje meelopen (ik had hetzelfde idee als curry684).

code:

INSERT INTO kopie_van_TimeKeeperTussenTabel 
  (Tellertje, Pasnummer, DatumTijd, InOfUit, Handled, IsEersteBoeking)
SELECT 
  @i = @i + 1,
  Pasnummer, 
  DatumTijd, 
  CASE  <insert nifty code op basis van tellertje en overige gegevens>, 
  Handled, 
  IsEersteBoeking
FROM TimerKeeperTussenTabel
ORDER BY Pasnummer, DatumTijd, IsEersteBoeking DESC

3. Drop de tellerkolom
4. Verwijder het origineel
5. Hernoem de kopie

[ Voor 5% gewijzigd door Annie op 07-04-2005 18:06 ]

[Verwijderd]

Annie schreef op donderdag 07 april 2005 @ 18:03:
Aangezien je een conditional update wil doen over veel data, zal het denk ik nooit echt snel gaan. Maar als je die subselects kwijt bent dan zal het al een stuk sneller reageren, denk ik.

Is het een optie om de gegevens over te pompen naar een kopie en daarna de oude tabel te droppen? In dat geval zou iets onderstaande misschien werken. het is een ruwe schets en uit de losse pols, dus geen idee of het ook verdere uitwerking rechtvaardigt. Ik heb geen mogelijkheid om wat te testen/fröbelen.

1. Maak een tijdelijk tabel
2. Selecteer de gegevens uit je tabel in de kopie, op volgorde van pasnummer, datum en IsEersteBoeking en laat een tellertje meelopen (ik had hetzelfde idee als curry684).

code:

...

3. Drop de tellerkolom
4. Verwijder het origineel
5. Hernoem de kopie

Stap 0: Begin transactie
Stap 6: Commit transactie (of rollback als er iets niet 100% lekker ging )

[P_de_B]

@Annie, ik denk toch dat het gezien de grote hoeveelheid gegevens niet werkt, daarnaast is het dacht ik niet mogelijk toe te wijzen in een select en data op te halen?

Ik heb net de volgende functie gemaakt:

code:

CREATE FUNCTION fnInOfUitBoeking (@Pasnummer INT, @DatumTijd DateTime)

RETURNS CHAR(1)

AS
BEGIN
DECLARE @BeginTijd DATETIME, @Count INT
SELECT TOP 1 @BeginTijd = DatumTijd FROM TimeKeeperTussenTabel WHERE DatumTijd < @DatumTijd AND IsEersteBoeking = 1 AND Pasnummer = @Pasnummer ORDER BY DatumTijd DESC

SELECT @Count = Count(*) FROM TimeKeeperTussenTabel WHERE DatumTijd <= @DatumTijd AND Pasnummer = @Pasnummer AND DatumTijd > @BeginTijd

RETURN (CASE @COUNT %2 WHEN 1 THEN 'E' ELSE 'B' END)

END

en die doet het redelijk snel, in ieder geval snel genoeg. Het vreemde is dat de code vergelijkbaar is met het originele statement, maar dat het wel veel en veel sneller gaat. Waarom zou het in een functie zoveel sneller gaan?

[curry684]

Execution plans bekeken?

[P_de_B]

curry684 schreef op vrijdag 08 april 2005 @ 10:41:
Execution plans bekeken?

Die wijken gigantisch af, maar het 'ingewikkelde' gedeelte zit in de functie en dat krijg ik niet te zien in het executieplan.

en alles heeft weer eens een cost van 0% :x dat heb ik wel vaker, maar heb er nog geen oorzaak voor gevonden

[bigbeng]

Zou het kunnen zijn dat het komt omdat je in je oorspronkelijke query een subselect gebruikte met een verwijzing in je binnenste selectie where clause naar de buitenste select, terwijl je hier twee losse queries gebruikt? Misschien dat SQL Server dan beter in staat is om de juiste index te gebruiken.

Tis maar een gok hoor, ik ben niet zo thuis in de execution engine van SQL Server