[MSSQL] Ingewikkelde view

[jsiegmund]

Ik zit met een probleem Een systeem waar ik aan bezig ben heeft nogal een beroerde performance, voornamelijk veroorzaakt door het dynamisch opbouwen van een aantal schermen wat veel tijd kost. Ik zal een poging doen om duidelijk te maken wat er op het scherm moet komen.

Het systeem bevat gebruikers en opdrachten. Beheerders van het systeem moeten de mogelijkheid krijgen om een gebruiker aan een opdracht te koppelen. Hierbij moet het scherm eruit zien als een matrix. De horizontale kolommen zijn dan: opdrachtomschrijving, datum en vervolgens voor ieder personeelslid een kolom. Verticaal worden per regel de opdrachten getoond. Het plannen van een personeelslid is dan simpelweg het aanklikken van een hokje in de matrix, op de achtergrond wordt er dan een regeltje in een koppeltabel geplaatst.

Dit alles werkt in ASP.NET met een AJAX gebaseerd grid. De performance is slecht omdat ik nu bij het initialiseren dynamisch een kolom aanmaak voor ieder personeelslid en vervolgens per regel moet gaan controleren welke combinatie opdracht-personeelslid er nu wel/niet in de database voorkomt.


Ik wil nu het dynamisch gedeelte eruit gaan slopen en het grid baseren op een database view. Dat betekent dat ik dus een view moet bouwen met de kolommen opdrachtomschrijving en datum en vervolgens een kolom voor ieder personeelslid. De view moet dan ook de koppelingen invullen (0 of 1 BIT) die reeds gemaakt zijn.

Wie heeft voldoende SQL kennis om te weten hoe je een dynamische view bouwt waarmee ik dit voor elkaar krijg? Ik vind het ook geen probleem om er een functie omheen te schrijven of een stored procedure, moet alleen even op weg geholpen worden Werk overigens met Linq-to-SQL als datalaag (ter info).

[whoami]

Wat bedoel je met 'dynamisch een kolom aanmaken' ? Waar maak je die kolom aan ?

En hoe denk je dat een view je performance probleem gaat oplossen ?

Ik denk dat jij hier gewoon SQL aan het misbruiken bent... Je wil je gegevens ophalen (ok), maar je wil je gegevens blijkbaar ook al direct in een bepaalde layout gaan steken.
Dat is niet de taak van SQL.
Haal gewoon mbhv SQL je gegevens op, en ga die opgehaalde gegevens dan in ASP.NET gaan layouten zodat ze getoond worden zoals jij dat wil.

[jsiegmund]

whoami schreef op zaterdag 28 juni 2008 @ 15:18:
Wat bedoel je met 'dynamisch een kolom aanmaken' ? Waar maak je die kolom aan ?

En hoe denk je dat een view je performance probleem gaat oplossen ?

Ik denk dat jij hier gewoon SQL aan het misbruiken bent... Je wil je gegevens ophalen (ok), maar je wil je gegevens blijkbaar ook al direct in een bepaalde layout gaan steken.
Dat is niet de taak van SQL.
Haal gewoon mbhv SQL je gegevens op, en ga die opgehaalde gegevens dan in ASP.NET gaan layouten zodat ze getoond worden zoals jij dat wil.

Met dynamisch een kolom aanmaken bedoel ik dat ik een kolommetje nodig heb voor iedere user, en ik ga uiteraard de query niet aanpassen iedere keer dat er een user toegevoegd wordt. Ik ben het met je eens dat het misschien niet de mooiste oplossing is, maar het idee erachter was dat SQL dit veel sneller voor me kan doen dan de dynamische code achter m'n grid.

Nu moet 'ie namelijk de volgende queries uitvoeren:
- Query die alle opdrachten uit de database haalt (makkelijk en snel)
- Query die alle users uit de database haalt, resultaat wordt gebruikt om dynamisch een kolom toe te voegen aan het grid
- Vervolgens moet ik voor iedere combinatie opdracht/user gaan controleren of er een regeltje in de koppeltabel staat: zeer onefficient. Ik kan ook domweg alle koppelingen ophalen en vervolgens bij iedere koppeling de juiste rij/kolom gaan zoeken, maar ook dit is zeer traag en moet dan bij iedere async postback opnieuw gebeuren aangezien er dingen veranderd kunnen zijn in de tussentijd.

Het idee was dus om de data middels SQL voor te bereiden zodat ik op de pagina zelf geen trage logica meer hoef uit te voeren. Overigens ben ik het niet met je eens dat je SQL hiervoor helemaal niet mag/kunt gebruiken; want als je een view op basis van JOIN's bouwt doe je mijns inziens precies hetzelfde: het combineren van data uit verschillende bronnen o.a. t.b.v. presentatie.

[whoami]

iCe01 schreef op zaterdag 28 juni 2008 @ 15:30:
[...]


Met dynamisch een kolom aanmaken bedoel ik dat ik een kolommetje nodig heb voor iedere user, en ik ga uiteraard de query niet aanpassen iedere keer dat er een user toegevoegd wordt. Ik ben het met je eens dat het misschien niet de mooiste oplossing is, maar het idee erachter was dat SQL dit veel sneller voor me kan doen dan de dynamische code achter m'n grid.

Je DB schema at runtime aanpassen is zowiezo , en als je dan denkt dat dit performanter is dan 'dynamische code'

- Query die alle opdrachten uit de database haalt (makkelijk en snel)

Ok, want je wil al die opdrachten tonen.

- Query die alle users uit de database haalt, resultaat wordt gebruikt om dynamisch een kolom toe te voegen aan het grid

Deze snap ik niet ...
Ik begrijp dat je alle users ook op het scherm wilt tonen, maar waarom je die 'extra column' nodig hebt Wat doe je met die column ?

- Vervolgens moet ik voor iedere combinatie opdracht/user gaan controleren of er een regeltje in de koppeltabel staat: zeer onefficient. Ik kan ook domweg alle koppelingen ophalen en vervolgens bij iedere koppeling de juiste rij/kolom gaan zoeken, maar ook dit is zeer traag en moet dan bij iedere async postback opnieuw gebeuren aangezien er dingen veranderd kunnen zijn in de tussentijd.

Je kan dit toch gewoon in één keer doen. Met één query haal je alle opdrachten op, en ga je idd gaan kijken ..

Het idee was dus om de data middels SQL voor te bereiden zodat ik op de pagina zelf geen trage logica meer hoef uit te voeren.

Trage logica zoals ?

Overigens ben ik het niet met je eens dat je SQL hiervoor helemaal niet mag/kunt gebruiken; want als je een view op basis van JOIN's bouwt doe je mijns inziens precies hetzelfde: het combineren van data uit verschillende bronnen o.a. t.b.v. presentatie.

Het combineren van data uit verschillende bronnen is geen problemen. Het ophalen van data en deze al gaan layouten is niet het doel van SQL. SQL is geen presentatie-taal.

[jsiegmund]

whoami schreef op zaterdag 28 juni 2008 @ 15:36:
[...]
Deze snap ik niet ...
Ik begrijp dat je alle users ook op het scherm wilt tonen, maar waarom je die 'extra column' nodig hebt Wat doe je met die column ?

Nog een poging om het duidelijker te maken dan

opdracht	datum	Pietje	Jan	Henk
Opdracht A	28-07-2008			x
Opdracht B	29-07-2008	X		x
Opdracht C	30-07-2008		X

Dit is wat ik op het scherm wil tonen. De koppeling tussen een opdracht en een gebruiker zit (uiteraard) niet in de opdrachten tabel, maar in een losse koppeltabel. De opdrachten kan ik ophalen met een simpele select, dan heb ik echter nog niet voor iedere gebruiker een kolom. De gebruikers kan ik vervolgens los ophalen en voor iedere gebruiker een kolom aan het grid toevoegen (so far so good). Maar dan moet ik dus voor iedere combi opdacht/gebruiker gaan controleren of er een link bestaat. Dat is best intensief met ~1000 records en ~30 users (= 30.000 queries). Andere mogelijkheid is alle links ophalen (1 query) en dan vervolgens door het grid gaan wandelen om voor iedere combi te controleren of er een link bestaat. Dat is ook bijzonder langzaam blijkt nu (was ook mijn eerste keuze).

Dus het idee was om bovenstaande weergave gewoon rechtstreeks uit SQL te halen. Dan heb ik maar 1 query die resultaten teruggeeft en hoef ik tevens niets meer dynamisch te veranderen in het grid omdat alle rijen en kolommen dan gewoon 1-op-1 overgenomen kunnen worden van het SQL resultaat.

Ik ben nu aan het sleutelen met een dynamisch opgebouwde query die met een scalar function controleert of er wel of geen link is tussen opdracht/gebruiker. Lijkt tot nu toe redelijk goed te werken maar ik vraag me dan wel af hoe het anders zou moeten.

[MrBucket]

iCe01 schreef op zaterdag 28 juni 2008 @ 15:55:
Dit is wat ik op het scherm wil tonen. De koppeling tussen een opdracht en een gebruiker zit (uiteraard) niet in de opdrachten tabel, maar in een losse koppeltabel. De opdrachten kan ik ophalen met een simpele select, dan heb ik echter nog niet voor iedere gebruiker een kolom. De gebruikers kan ik vervolgens los ophalen en voor iedere gebruiker een kolom aan het grid toevoegen (so far so good). Maar dan moet ik dus voor iedere combi opdacht/gebruiker gaan controleren of er een link bestaat. Dat is best intensief met ~1000 records en ~30 users (= 30.000 queries). Andere mogelijkheid is alle links ophalen (1 query) en dan vervolgens door het grid gaan wandelen om voor iedere combi te controleren of er een link bestaat. Dat is ook bijzonder langzaam blijkt nu (was ook mijn eerste keuze).

Mogelijkheid 1) Als je nu eens niet door alle cellen van je grid loopt, maar door alle links? maak een in-memory 2-dimensionale array die standaard voor elke (user, record) combinatie de waarde geen link heeft. Maak (voor zover nodig) twee dictionaries, eentje die een user mapt naar een kolomindex, en eentje die een record mapt naar een rijindex. Loop vervolgens door al je links heen, en voor elke link, gebruik de dictionaries om de (user, record) combinatie om te zetten naar een (rij, kolom)-combinatie, en update vervolgens de array op deze plek. Als alle links verwerkt zijn, render de array uit.

Mogelijkheid 2) Sorteer je links lexicografisch op record en user (i.e. order by recordname, username) wanneer je ze ophaalt uit de db. Loop in dezelfde volgorde door alle cellen van je grid, en loop tegelijkertijd je lijst van links sequentieel af:
Als de huidige cel in je grid niet overeenkomt met de plaats van de huidige link: ga naar de volgende cel.
Als de huidige cel in je grid wel overeenkomt met de plaats van de huidige link, render een 'X' in je grid, ga naar de volgende cel en naar de volgende link in je lijst van links.

[Annie]

Hebben we het hier niet gewoon over een pivot report (draaitabel)?
Welke oplossing je kan gebruiken is een beetje afhankelijk van de versie van MSSQL die je gebruikt.

[nescafe]

Laat je view drie kolommen retourneren:
- Label X-as
- Label Y-as
- Waarde van de cel op deze coordinaten

Eigenlijk zoals MrBucket ook aangeeft. Maar je kunt SQL ook alle mogelijk combinaties (dus cellen) voor je weergeven m.b.v. een cross join (= een join zonder conditie).

Het enige wat jij moet doen is de waarden in de juiste cel plaatsen.
Dit kan rij-voor-rij of kolom-voor-kolom, wat voor jou het makkelijkst is.

SQL:

SELECT
  Opdracht.Naam, Opdracht.Datum, Medewerker.Naam,
  CASE WHEN Koppeltabel.ID IS NULL THEN 0 ELSE 1 END AS Waarde
FROM
  Opdracht
CROSS JOIN
  Medewerker
LEFT JOIN
  Koppeltabel
    ON Koppeltabel.Medewerker = Medewerker.ID  AND Koppeltabel.Opdracht = Opdracht.ID
ORDER BY
  Opdracht.ID, Medewerker.ID -- Horizontaal de tabel opbouwen

[jsiegmund]

Annie schreef op zaterdag 28 juni 2008 @ 21:46:
Hebben we het hier niet gewoon over een pivot report (draaitabel)?
Welke oplossing je kan gebruiken is een beetje afhankelijk van de versie van MSSQL die je gebruikt.

Klopt ja, in feite wel.

De oplossing met de cross join heb ik geprobeerd maar dan krijg je dus in eerste instantie voor iedere opdracht/gebruiker combinatie een regel (en dus veel teveel data). Die kun je dan nog wel wat fijner maken, maar het blijft een probleem dat je per opdracht/gebruiker een regel terugkrijgt en dan dus toch weer moet gaan zoeken in je grid (wat het nu juist zo langzaam maakt).

Ik heb nu een SQL statement wat in code dynamisch opgebouwd wordt en de volgende kolommen retourneert:
- Opdrachtomschrijving
- Datum
En vervolgens per gebruiker de link. De link wordt met een UDF gecontroleerd en dat blijkt (had ik niet verwacht) verdraaid snel te werken.

Query wordt dan dus:
SELECT omschrijving, datum, CheckLink(opdrachtID, 'user1') AS 'user1', CheckLink(opdrachtID, 'user2') AS 'user2' FROM opdrachten

Dit geeft precies terug wat ik nodig heb en doet dat ook nog aardig snel. Nu ben ik op zich wel gevoelig voor de mooiste oplossing (en niet de eerste die werkt), maar ik zie op het moment nog geen mogelijkheid om dezelfde performance op een andere manier te realiseren. Ik zou het bouwen van die dynamische query dan nog wel het liefst in een SP doen om die logica uit de code te halen.

[ Voor 4% gewijzigd door jsiegmund op 29-06-2008 11:30 ]

[MrBucket]

iCe01 schreef op zondag 29 juni 2008 @ 11:26:
Ik heb nu een SQL statement wat in code dynamisch opgebouwd wordt en de volgende kolommen retourneert:
- Opdrachtomschrijving
- Datum
En vervolgens per gebruiker de link. De link wordt met een UDF gecontroleerd en dat blijkt (had ik niet verwacht) verdraaid snel te werken.

Query wordt dan dus:
SELECT omschrijving, datum, CheckLink(opdrachtID, 'user1') AS 'user1', CheckLink(opdrachtID, 'user2') AS 'user2' FROM opdrachten

Dat had ik ook niet verwacht; blijkbaar kan hij de UDF wegoptimaliseren... zou je de code van je UDF eens willen posten? Ben wel benieuwd eigenlijk

De oplossing met de cross join heb ik geprobeerd maar dan krijg je dus in eerste instantie voor iedere opdracht/gebruiker combinatie een regel (en dus veel teveel data). Die kun je dan nog wel wat fijner maken, maar het blijft een probleem dat je per opdracht/gebruiker een regel terugkrijgt en dan dus toch weer moet gaan zoeken in je grid (wat het nu juist zo langzaam maakt).

Zoals ik eerder postte, je hoeft niet per se te zoeken in je grid, je kunt gewoon parallel alle cellen uit je grid en alle links aflopen, mits je ze beiden in de goede volgorde gesorteerd hebt:

C#:

List<Link> links;
List<Opdracht> rows;
List<User> columns;

//Vul je links collectie met alle links, gesorteerd op "order by Omschrijving, Datum, Username".
//Vul je rows collectie met alle opdrachten, gesorteerd op "order by Omschrijving, Datum"
//Vul je columns collectie met alle users, gesorteerd op "order by Username".

//Standaard worden alle waarden op false geinitialiseerd:
bool[][] grid = new bool[rows.Count, columns.Count];

int linkIndex = 0;
for(int rowIndex = 0; rowIndex < rows.Count; rowIndex++)
{
    for(int colIndex = 0; colIndex < columns.Count; colIndex++)
    {
        Link link = links[linkIndex];
        
        if( rows[rowIndex].Omschrijving == link.Omschrijving && 
            rows[rowIndex].Datum.Equals(link.Datum) &&
            columns[colIndex].Username == link.Username)
        {
            //Match gevonden, zet een kruisje in het grid.
            grid[rowIndex][colIndex] = true;
            
            //Volgende link, als die er nog is
            if(++linkIndex >= links.Count)
                return grid;        //Alle links zijn geweest, klaar.
        }
    }
}

[jsiegmund]

code:

ALTER FUNCTION [dbo].[udf_CrewInProduction] 
(
    -- Add the parameters for the function here
    @productionID uniqueidentifier,
    @userID uniqueidentifier
)
RETURNS bit
AS
BEGIN
    DECLARE @Result bit;
    DECLARE @RecordCount int;
    SET @RecordCount = 0;

    -- Insert statements for procedure here
    IF EXISTS(SELECT * FROM CrewOnProduction WHERE
              ProductionID = @productionID AND 
              UserID = @userID)
        SET @Result = 1;
    ELSE
        SET @Result = 0;

    RETURN @Result
END

Tabelnamen wijken een beetje af, maar het idee is het zelfde.

Query bestaat uit ongever 30 kolommen die bovenstaande UDF aanroepen en de resultset is ongeveer 300 regels groot (na wat filteren). Dan zou ik toch verwachten dat ie de UDF 9000x aanroept en daar even mee bezig is, maar de query kost me minder dan een seconde. Resultaat van de query kan 1-op-1 het grid in en daardoor is de performance nu heel aardig.

Ik wil nu alleen nog proberen om de SQL code op te bouwen middels Linq, maar om de een of andere reden kan ik m'n UDF niet aanroepen via de datacontext. Heb daarover een vraag uitgezet op MSDN en doe het dus voorlopig maar even zo (resultaat is dan toch hetzelfde).

[pedorus]

Ik denk dat een oplossing met PIVOT wel eens een stuk sneller kan zijn dan 9000x een UDF. Niet opbouwen in LINQ, maar gewoon als view gebruiken lijkt mij.

[Cousin Boneless]

Het zoeken in het grid hoeft volgens mij geen performance penalty op te leveren als je ervoor zorgt dat je met hashtables werkt. Indexatie in de database doet wat dat betreft ook niet zoveel magisch.

Het lijkt me zelfs mogelijk dat je de data zodanig kunt aanleveren dat je rechtstreeks op een positie x,y kunt inprikken:

Schrijf een stored procedure die drie recordsets teruggeeft.
- Recordset 1: de personeelsleden die minstens één waarde in het grid hebben
- Recordset 2: de opdrachten die minstens één waarde in het grid hebben
- Recordset 3: de cellen (x,y) die gezet zijn.

Recordset 1 en 2 geef je dmv een tijdelijke tabel en een identity field een 1-based 'index' veld, zodat deze overeenkomen met de x en y waarden in recordset 3.

Gebruik dan in je applicatie de recordcount van recordset 1 en recordset 2 en creeer een lege boolean matrix met deze omvang.
Lees dan recordset 3 uit en zet een True op elke positie (x,y) in de matrix.

http://www.velocityreview...m-a-stored-procedure.html

[ Voor 5% gewijzigd door Cousin Boneless op 29-06-2008 16:32 ]

[jsiegmund]

pedorus schreef op zondag 29 juni 2008 @ 15:09:
Ik denk dat een oplossing met PIVOT wel eens een stuk sneller kan zijn dan 9000x een UDF. Niet opbouwen in LINQ, maar gewoon als view gebruiken lijkt mij.

Op zich interessant. Als het ik het goed begrijp moet ik dan alsnog eerst bovenstaande CROSS JOIN uitvoeren om een resultset te krijgen die ik kan pivoteren. Daar kan ik dan de pivot op uitvoeren en dan moet ik die uiteindelijke query wel dynamisch opbouwen omdat het PIVOT commando een statische lijst van kolomnamen verwacht.

Heb nu het volgende als stored procedure:

code:

CREATE PROCEDURE proc_CrewPlanningPivot
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    CREATE TABLE #resultset
    (
        ProductionID uniqueidentifier,
        Username nvarchar(256),
        Date Datetime,
        Linked int
    );

    DECLARE @cols NVARCHAR(2000)
    SELECT  @cols = COALESCE(@cols + ',[' + username + ']',
                             '[' + username + ']')
    FROM    Aspnet_Users
    ORDER BY username

    INSERT INTO #resultset (ProductionID, Username, Date, Linked)
    SELECT
      Productions.ProductionID, Aspnet_Users.UserName, Productions.Date,
      CASE WHEN CrewOnProduction.UserID IS NULL THEN 0 ELSE 1 END AS Linked
    FROM 
      Productions
    CROSS JOIN
      Aspnet_Users
    LEFT JOIN
      CrewOnProduction ON 
      CrewOnProduction.ProductionID = Productions.ProductionID AND 
      CrewOnProduction.UserID = Aspnet_Users.UserID 
    WHERE 
      Productions.Date >= GETDATE()
    ORDER BY
      Productions.Date ASC

    DECLARE @query NVARCHAR(3000)
    SET @query = N'
    SELECT *
    FROM 
    (SELECT *
    FROM #resultset) AS p
    PIVOT 
    (
    MAX([Linked])
    FOR UserName IN ('+@cols+')
    ) AS pvt;'

    EXECUTE(@query);

    DROP TABLE #resultset;
END
GO

Dit geeft in ieder geval het juiste resultaat en is snel, maar is het ook the-way-2-go?

[pedorus]

Dat kan denk ik stukken simpeler, oa zonder outer joins. Gebruik gewoon COUNT(UserId) ipv MAX(Linked). (ongetest)