[MSSQL] Query die data uit logs haalt

[Verwijderd]

Ik heb een tabel met daarin variablen van een Item waaronder zijn status (1, 2 of 3). Daarnaast heb ik een tabel die alle veranderingen opslaat als er een variable verandert.

Vervolgens wil ik: Op een bepaalde datum in het verleden, alle items die toen waren aangemaakt en de status die ze toen hadden, uit de tabel halen. Dus ik controleer op aanmaakDatum, en zoek zijn status, eventueel aan de hand van de logs, op.

Na veel proberen en zoeken heb ik uiteindelijk een query geproduceert die met behulp van een temporary table het gewenste resultaat produceert.

• Eerst selecteer ik alles statussen van items die geen status wijziging hebben gehad en dus geen logs daarvan hebben.
• Daarna kijk ik in de log tabel en kijk ik naar de logs die NA de gewenste datum een status wijziging hebben gehad en kijk ik wat de oude waarde ervan is.
• Uiteindelijk kijk ik in de log tabel die VOOR de gewenste datum een status wijziging hebben gehad en wat daarvan de nieuw waarde is.

Maar nu vraag ik me af of dit efficienter kan. Wat ik nu heb:

SQL:

CREATE TABLE ##lijstOpDatumMetStatus (itemId int unique, value int)

declare @itemId int
declare @logId int
declare @datum datetime
declare @logdatum datetime
declare @objectId int /* itemId die in de log staat */
declare @propertyId int /* bij status verandering is deze 1 */
declare @objectTypeId int /* negeren, niet boeiend */
declare @propertyTypeId int /* negeren niet boeiend */

declare @valueInt int

set @datum = '2005-05-31 00:10:00'
set @propertyId = 1
set @objectTypeId = 1


/*****************************************************************************/
DECLARE zonderLogCursor CURSOR LOCAL FOR
    
    select id, statusId
    from rsv_item i
    where id not in(
        select objectId
        from sys_changeLog
        where propertyId = 1
    )
    AND datediff(day, @datum, i.aanmaakdatum) <= 0
    AND datediff(day, @datum, i.datum) >= 0

OPEN zonderLogCursor

    FETCH NEXT FROM zonderLogCursor INTO @itemId, @valueInt
    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO ##lijstOpDatumMetStatus (itemId, value)
        VALUES (@itemId, @valueInt)

        FETCH NEXT FROM zonderLogCursor INTO @itemId, @valueInt
        CONTINUE
    END

CLOSE zonderLogCursor
DEALLOCATE zonderLogCursor

/*****************************************************************************/
DECLARE logNaDatumCursor CURSOR LOCAL FOR

    select objectId, min(cl.datum)
    from sys_changeLog cl
    inner join sys_propertyChangeLogInt clb on clb.logId = cl.id
    inner join rsv_item i on i.id = cl.objectId 
        AND datediff(day, @datum, i.aanmaakdatum) <= 0
        AND datediff(day, @datum, i.datum) >= 0
    where cl.propertyid = @propertyId
    and objectTypeId = @objectTypeId
    and datediff(day, @datum, cl.datum) > 0
    group by objectId

OPEN logNaDatumCursor

    FETCH NEXT FROM logNaDatumCursor INTO @itemId, @logdatum
    WHILE @@FETCH_STATUS = 0
    BEGIN
        select @valueInt = clb.oldValue
        from sys_changeLog cl
        inner join sys_propertyChangeLogInt clb on clb.logId = cl.id
        where cl.objectId = @itemId 
        and cl.datum = @logdatum
    
        INSERT INTO ##lijstOpDatumMetStatus (itemId, value)
        VALUES (@itemId, @valueInt)

        FETCH NEXT FROM logNaDatumCursor INTO @itemId, @logdatum
        CONTINUE
    END

CLOSE logNaDatumCursor
DEALLOCATE logNaDatumCursor

/*****************************************************************************/
DECLARE logVoorDatumCursor CURSOR LOCAL FOR

    select objectId, max(cl.datum)
    from sys_changeLog cl
    inner join sys_propertyChangeLogInt clb on clb.logId = cl.id
    inner join rsv_item i on i.id = cl.objectId 
        AND datediff(day, @datum, i.aanmaakdatum) <= 0
        AND datediff(day, @datum, i.datum) >= 0
    where cl.propertyid = @propertyId
    and objectTypeId = @objectTypeId
    and datediff(day, @datum, cl.datum) <= 0
    and objectId not in (
        select itemId
        from ##lijstOpDatumMetStatus
    )

    group by objectId

OPEN logVoorDatumCursor

    FETCH NEXT FROM logVoorDatumCursor INTO @itemId, @logdatum
    WHILE @@FETCH_STATUS = 0
    BEGIN
        select @valueInt = clb.newValue
        from sys_changeLog cl
        inner join sys_propertyChangeLogInt clb on clb.logId = cl.id
        where cl.objectId = @itemId 
        and cl.datum = @logdatum
        
        INSERT INTO ##lijstOpDatumMetStatus (itemId, value)
        VALUES (@itemId, @valueInt)

        FETCH NEXT FROM logVoorDatumCursor INTO @itemId, @logdatum
        CONTINUE
    END

CLOSE logVoorDatumCursor
DEALLOCATE logVoorDatumCursor

/*****************************************************************************
 * select statement
 *****************************************************************************/
    select count(i.id) as aantal, l.value as statusId
    from rsv_item i
    inner join ##lijstOpDatumMetStatus l ON i.id = l.itemid
    group by l.value

DROP TABLE ##lijstOpDatumMetStatus

Resultaat:

aantal	status
27	1
72	2
131	3

En eigenlijk wil ik dit niet van één datum, maar over een periode. Dus de resultaten van iedere dag, maar die krijg ik ook als ik deze procedure voor iedere datum uitvoer.

[napel25]

Ja, het kan efficienter. De eerste cursor is overbodig. Dat kan op de volgende manier opgelost worden:

INSERT INTO ##lijstOpDatumMetStatus (itemId, value)
select id, statusId
from rsv_item i
where id not in(
select objectId
from sys_changeLog
where propertyId = 1
)
AND datediff(day, @datum, i.aanmaakdatum) <= 0
AND datediff(day, @datum, i.datum) >= 0

[napel25]

AND datediff(day, @datum, i.aanmaakdatum) <= 0

Dit is ook duur. Hij gaat nu voor elk record de diff bereken. Wanneer je met DATEADD het maximale of minimale verschil bij de @datum optelt en dat vergelijkt met i.aanmaakdatum, hoeft de database maar 1 berekening te doen. Kolom namen moet je altijd proberen kaal aan één kand van de vergelijking te houden.

[ Voor 3% gewijzigd door napel25 op 22-07-2005 12:43 ]

[napel25]

Volgens mij is de tweede cursor ook om te bouwen in iets als:

INSERT INTO ##lijstOpDatumMetStatus (itemId, value)
select cl.objectId, clb.oldValue
from sys_changeLog cl
, sys_propertyChangeLogInt clb
, (select objectId, min(cl.datum) as logdatum
from sys_changeLog cl
inner join sys_propertyChangeLogInt clb on clb.logId = cl.id
inner join rsv_item i on i.id = cl.objectId
AND datediff(day, @datum, i.aanmaakdatum) <= 0
AND datediff(day, @datum, i.datum) >= 0
where cl.propertyid = @propertyId
and objectTypeId = @objectTypeId
and datediff(day, @datum, cl.datum) > 0
group by objectId ) t
where clb.logId = cl.id
and cl.objectId = t.objectId
and cl.datum = t.logdatum

Cursors in SQL Server zijn duur. Je kan dingen efficienter oplossen met tijdelijke tabellen.

[napel25]

Verwijderd schreef op vrijdag 22 juli 2005 @ 12:20:
En eigenlijk wil ik dit niet van één datum, maar over een periode. Dus de resultaten van iedere dag, maar die krijg ik ook als ik deze procedure voor iedere datum uitvoer.

Je moet het formaat van een status per record ombouwen naar alle statussen in een record. Volgens mij kun je daarvoor het beste gebruik maken van de procedure die je hebt. Deze kun je een een cursor aanroepen die door de gewenste data heen loopt. De uitkomst van de procedure zet je in een tijdelijke tabel met de kolomen:
Datum
Status_1
Status_2
Status_3

Dan heb je wat je wilt. Maar voor de efficientie zou ik wel alle cursoren uit je procedure slopen. Volgens mij heb je die helemaal niet nodig.

[Verwijderd]

Bedankt voor je tips.

Mijn sql kennis is nog niet zo uitgebreid, ik wist niet dat je bij een "select from" tabellen kan gebruiken die je weer met een aparte "select from" aan maakt. Of dat je bij een insert een select kon gebruiken..

Kzal eens kijken of ik de cursors eruit kan halen en de datum vergelijking kan verbeteren. Verder dacht ik dat dit (data terug zoeken uit logs) een veel voorkomend probleem was en dat ik misschien iets structureels inefficient doe.