[VB.net/SQL] Filteren op meerdere rijen in recordset

[DukeMan]

Ik heb een database waarin profielen staan. Deze worden opgeslagen in tabel 1. Vervolgens heb ik een 2e tabel waarin eigenschappen van deze profielen staan (tabel 2). Bij eigenschappen moet je denken aan gegevens welke de leden kunnen instellen op hun profiel.

Hieronder de definitie van de tabellen:

tbdProfile:

• ID (guid)
• Naam (varchar)
• NickName (varchar)
• Geslacht (varchar)
• Leeftijd (int)
• ProfileType (guid) verwijst naar tbdProfileType)
• ...

tbdProfileItem:

• ID (guid)
• ProfielID (guid)
• SettingName (guid) (verwijst naar tbdSectionItem)
• SettingValue (varchar)

De reden dat de eigenschappen in een aparte tabel staan met voor elke eigenschap een nieuw record is omdat de eigenschappen dynamisch aangemaakt kunnen worden (door een admin). Voor elke eigenschap dus een kolom aanmaken is dus niet handig.

Mijn probleem
Bezoekers kunnen op de site zoeken in de profielen. Er kan gezocht worden in de vaste velden van een profiel (geslacht, leeftijd, nickname etc) en in de eigenschappen van een profiel.

Wanneer je de 2 tabellen bij elkaar joint krijg je een dataset waarin alle benodigde gegevens zitten om in te zoeken: je hebt de vaste gegevens/kolommen van een profiel en daarnaast voor elke eigenschap een nieuw record met de naam en value.

Voorbeeld:

ID Profiel	Naam	Geslacht	Nickname	SettingName	SettingValue
(guid)	Nummer1	Man	Nr1	eigenschap1	waarde1
(guid)	Nummer1	Man	Nr1	eigenschap2	waarde2
(guid)	Nummer2	Vrouw	Nr2	eigenschap1	waarde3

Wil je nu zoeken op bijvoorbeeld geslacht ='man' en op minimaal 2 eigenschappen loop je tegen het probleem aan dat de vaste gegevens in elk record terug komt, echter de eigenschappen in aparte records zitten. Ga ik zoeken op "alle velden" (AND) lukt dit mij niet omdat je op meerdere records wilt filteren.

Ik heb dit nu opgelost door in een Stored Procedure een Crosstab (dynamisch) aan te maken van de eigenschappen tabel, waarbij elke eigenschap een kolom wordt en de Value van die eigenschap de waarde van die kolom is.
Hierbij loop ik tegen de beperkingen aan van de maximale lengte van een varchar in SQL (8000 karakters) aangezien de site een aantal soorten profielen heeft met elk zijn eigen eigenschappen. Ook het aantal eigenschappen begint langzaam aan te groeien waardoor de 8000 karakters bijna gehaald worden.

Ik wil dit dus aan gaan passen, maar weet zo even niet hoe ik dit het beste kan oplossen. Ik heb nu een nieuwe variant gemaakt, waarbij er (wederom) dynamisch een query wordt gemaakt in een Stored Procedure waarin de verschillende eigenschappen waarop gezocht moet worden in de where wordt geplaatst, vervolgens wordt er gezocht op de vaste eigenschappen van het profiel en daarna wordt er gecontroleerd (indien gezocht op alles/AND) of de eigenschappen inderdaad allemaal gevonden zijn. Echt optimaal is dit volgens mij niet en volgens mij kan dit beter, echter weet ik even niet hoe.

Hieronder de SP zoals deze nu gebruikt wordt.

SQL:

CREATE PROCEDURE usp_SearchProfilesV2
    @intCount int =-1,
    @strWhere varchar(4000) = '',
    @strOrder varchar(300) = ' fldNickname ',
    @intProfileType int = 2
AS

declare @strField varchar(100)
declare @strSQL varchar(8000)

declare curFields cursor for
    SELECT  tbdSectionItem.fldID 
    FROM        tbdSectionItem
            INNER JOIN tbdSection ON tbdSection.fldID = tbdSectionItem.fldSectionID
            INNER JOIN tbdProfileType ON tbdProfileType.fldID = tbdSection.fldProfileTypeID
    WHERE   tbdSectionItem.fldDeleted = 0
            AND tbdProfileType.fldType = @intProfileType
/*
    SELECT  fldID 
    FROM        tbdSectionItem
    WHERE   fldDeleted = 0
*/

set @strSQL = ''

open curFields

fetch next from curFields into @strField 

while @@FETCH_STATUS = 0
begin

    set @strSQL = @strSQL + ' MAX ( CASE WHEN ''' + LOWER(@strField) + ''' = fldSectionItemID THEN fldAllValue ELSE '''' END) AS ''K' + REPLACE(LOWER(@strField), '-', '') + ''', '

    fetch next from curFields into @strField 
end

close curFields

deallocate curFields

if substring(@strSQL, len(@strSQL), 5) = ', '
    begin
        set @strSQL = substring(@strSQL, 0, len(@strSQL))
    end


if len(@strSQL) > 0 
    set @strSQL = ', ' + @strSQL


IF OBJECT_ID('tempdb..##tmpSearch') IS NOT NULL
    DROP TABLE ##tmpSearch


set @strSQL = 'SELECT fldID, fldAge ' + @strSQL + 
        ' INTO ##tmpSearch 
        FROM vwProfileListSearchInfo 
    
        GROUP BY fldID, fldAge, fldUnique, fldNickname
        ORDER BY fldUnique'

print @strSQL   
exec (@strSQL)


if @intCount > -1 
    set @strSQL = 'SELECT TOP ' + CAST(@intCount AS varchar(10))
else
    set @strSQL = 'SELECT '

set @strSQL = @strSQL + ' tbdProfile.fldID, ' + @strOrder + '
            FROM 
            ##tmpSearch INNER JOIN tbdProfile ON tbdProfile.fldID = ##tmpSearch.fldID 
                    INNER JOIN tbdProfileType ON tbdProfileType.fldID = tbdProfile.fldProfileTypeID '



set @strSQL = @strSQL + '  WHERE fldNeedUpdate = 0 '

if @intProfileType > 0
    set @strSQL = @strSQL + '  AND tbdProfileType.fldType = ' + CAST(@intProfileType AS varchar(10))

if len(@strWhere) > 0
    set @strSQL = @strSQL + '  AND (' + @strWhere + ') ' -- AND fldProfileType = ' + CAST(@intProfileType AS varchar(10))


/*

if @intProfileType = 0 
    set @strSQL = @strSQL + '  WHERE 1=1 '

if @intProfileType > 0
    set @strSQL = @strSQL + '  WHERE tbdProfileType.fldType = ' + CAST(@intProfileType AS varchar(10))

if len(@strWhere) > 0
    set @strSQL = @strSQL + '  AND (' + @strWhere + ') ' -- AND fldProfileType = ' + CAST(@intProfileType AS varchar(10))
*/

set @strSQL = @strSQL + ' GROUP BY tbdProfile.fldID, ' + @strOrder

set @strSQL = @strSQL + ' ORDER BY ' + @strOrder

exec (@strSQL)

drop table ##tmpSearch

De SP zoals ik deze aangepast hebt.

SQL:

CREATE PROCEDURE usp_SearchProfilesV3
    @intCount int =-1,
    @strWhereProfile varchar(4000) = '',
    @strWhereProfileItem varchar(4000) = '',
    @strOrder varchar(300) = ' fldNickname ',
    @intProfileType int = 2,
    @intSearchType int = 1,
    @intColumnCount int = 1
AS

DECLARE @strSQL varchar(8000)
DECLARE @strSQLWhere varchar(8000)

-- Import voorbeeld
--SET @strWhereProfile = ' ( fldSex = ''sex_female'' ) '
--SET @strWhereProfileItem = ' (fldSectionItemID = ''6fb324cd-7da9-4df2-bd13-45c99b7fe8a0'' AND fldAllValue = ''1'') OR (fldSectionItemID = ''0e22021f-5145-492e-a9ef-710689ca6c00'' AND fldAllValue = ''1'') OR (fldSectionItemID = ''6255ee85-afed-40a2-85e4-f53a6692c0f8'' AND fldAllValue = ''1'') OR (fldSectionItemID = ''600930dd-bce5-4963-9b2a-79f106d83806'' AND fldAllValue = ''1'') '
--SET @intColumnCount = 4
--SET @intSearchType = 1
--SET @strOrder = ' fldNickName '
--SET @intProfileType = 2



SET @strSQLWhere = ''


-- Step 1: Collect information in temporary table
CREATE TABLE #tmpProfileSearchIDs (
    fldID uniqueidentifier
)

SET @strSQL = '
        INSERT INTO 
                #tmpProfileSearchIDs
        SELECT
                fldID
        FROM 
                vwProfileListSearchInfo
        '

IF LEN(@strWhereProfile) > 0 OR LEN(@strWhereProfileItem) > 0
    BEGIN
        SET @strSQLWhere = ' ( '
    END

IF LEN(@strWhereProfileItem) > 0 
    BEGIN
        SET @strSQLWhere = @strSQLWhere + ' ( ' + @strWhereProfileItem + ' ) '
    END

IF LEN(@strWhereProfile) > 0 
    BEGIN
        IF @intSearchType = 1 AND LEN(@strWhereProfileItem) > 0         -- AND
            BEGIN
                SET @strSQLWhere = @strSQLWhere + ' AND  '
            END
        IF @intSearchType = 2 AND LEN(@strWhereProfileItem) > 0         -- OR
            BEGIN
                SET @strSQLWhere = @strSQLWhere + ' OR  '
            END

        SET @strSQLWhere = @strSQLWhere + ' ( ' + @strWhereProfile + ' ) '

    END

IF LEN(@strWhereProfile) > 0 OR LEN(@strWhereProfileItem) > 0
    BEGIN
        SET @strSQLWhere = @strSQLWhere + ') '
    END

IF @intProfileType > 0
    BEGIN
        IF LEN(@strSQLWhere) > 0
            BEGIN
                SET @strSQLWhere = ' ( ' + @strSQLWhere + ' ) AND '
            END

        SET @strSQLWhere = @strSQLWhere + ' ( fldProfileType = ' + CAST(@intProfileType AS VARCHAR(2)) + ' ) '
    END

        IF LEN(@strSQLWhere) > 0
            BEGIN
                SET @strSQLWhere = ' WHERE ' + @strSQLWhere 
            END

SET @strSQL = @strSQL + @strSQLWhere + '
        GROUP BY 
                fldID'


IF @intSearchType = 1 AND LEN(@strWhereProfileItem) > 0         -- AND
    BEGIN
        SET @strSQL = @strSQL + '
            HAVING 
                    COUNT(fldNickName) = ' + CAST(@intColumnCount AS VARCHAR(10))
    END

EXEC (@strSQL)


if @intCount > -1 
    set @strSQL = 'SELECT TOP ' + CAST(@intCount AS varchar(10))
else
    set @strSQL = 'SELECT '

SET @strSQL = @strSQL + '
                tbdProfile.*
        FROM
                tbdProfile
                INNER JOIN #tmpProfileSearchIDs ON #tmpProfileSearchIDs.fldID = tbdProfile.fldID
        WHERE
                fldNeedUpdate = 0
'

IF LEN(@strOrder) > 0
    BEGIN
        SET @strSQL = @strSQL + ' ORDER BY ' + @strOrder
    END


EXEC (@strSQL)

DROP TABLE 
        #tmpProfileSearchIDs

De aanroep van de eerste SP is:

SQL:

EXEC usp_SearchProfilesV2 @strWhere='KF2BC799FC20E4562B9CB486C4286D6ED = 1 AND K77F8F50C0683449DA31D572A1E750554 = 1'

De 2e is:

SQL:

EXEC usp_SearchProfilesV3 @intCount = 1000000, 
                @strWhereProfile = ' ( fldSex = ''sex_female'' ) ',
                @strWhereProfileItem = ' (fldSectionItemID = ''6fb324cd-7da9-4df2-bd13-45c99b7fe8a0'' AND fldAllValue = ''1'') OR (fldSectionItemID = ''0e22021f-5145-492e-a9ef-710689ca6c00'' AND fldAllValue = ''1'') OR (fldSectionItemID = ''6255ee85-afed-40a2-85e4-f53a6692c0f8'' AND fldAllValue = ''1'') OR (fldSectionItemID = ''600930dd-bce5-4963-9b2a-79f106d83806'' AND fldAllValue = ''1'') ',
                @intColumnCount = 4

Zijn er betere manieren om te zoeken in een results waarin je wilt filteren op meerdere rijen?

Ik hoop dat bovenstaande een beetje duidelijk is.

[Niemand_Anders]

Ik denk van wel. Ik zou eerst de subset ophalen waarbij je selecteert op key-value pairs van de properties ( where (name='prop1' and value='1') and (name='prop5' and value='V') ) en dan een group by doen op het ID van de gebruiker (ProfileID zeg maar) met vervolgens daar achter een having count(ProfileID)=2 (vanwege de selectie op 2 dynamische velden (prop1 en prop5)).

Je krijgt dan een lijst terug van de profile id's welke voldoen aan de selectie creteria. Nu hoef je alleen nog maar de stored procedure aan te roepen op PER profiel alle gegevens op te halen. Waarbij je waarschijnlijk twee resultaat sets zal terug krijgen, te weten tblProfile en alle records uit tblProfileType voor ProfileID=xxx.

De lijst met profile id's uit de eerste query kun je eventueel cachen (in geval van een website) zodat je niet bij elke subset (1-10-, 11-20, etc) opnieuw de zoek query hoeft uit te voeren. Je kunt de de collection (List<Guid> ?) simpel opslaan in de sessie van de ingelogde admin medewerker.

[DukeMan]

Niemand_Anders schreef op donderdag 03 januari 2008 @ 11:19:
Ik denk van wel. Ik zou eerst de subset ophalen waarbij je selecteert op key-value pairs van de properties ( where (name='prop1' and value='1') and (name='prop5' and value='V') ) en dan een group by doen op het ID van de gebruiker (ProfileID zeg maar) met vervolgens daar achter een having count(ProfileID)=2 (vanwege de selectie op 2 dynamische velden (prop1 en prop5)).

Een beetje zoals de nieuwe opzet is dus, echter moet je ipv AND OR hebben volgens mij aangezien je op rijen filtert... en je op die manier (met AND) geen van de 2 rijen terugkrijgt.

Je krijgt dan een lijst terug van de profile id's welke voldoen aan de selectie creteria. Nu hoef je alleen nog maar de stored procedure aan te roepen op PER profiel alle gegevens op te halen. Waarbij je waarschijnlijk twee resultaat sets zal terug krijgen, te weten tblProfile en alle records uit tblProfileType voor ProfileID=xxx.

De lijst met profile id's uit de eerste query kun je eventueel cachen (in geval van een website) zodat je niet bij elke subset (1-10-, 11-20, etc) opnieuw de zoek query hoeft uit te voeren. Je kunt de de collection (List<Guid> ?) simpel opslaan in de sessie van de ingelogde admin medewerker.

Die 2e SP geeft alle records in de profielen tabel terug. Deze worden in de website gecached zodat ze bij pagina 2 en verder direct getoond kunnen worden.

[Niemand_Anders]

AND moet inderdaad OR zijn. Mijn fout.

Wat betreft de tweede SP. Nee, deze geeft per profiel alle informatie terug. SP2 zal dus in een loop aangeroepen worden. Aangezien jouw tweede SP op het einde de tijdelijke tabel verwijderd, moet je dus de volledige result set gaan cachen of je kunt de resultaten niet over meerdere pagina's uitsmeren.

Daarnaast weet ik niet hoeveel personen met deze lijsten kunnen werken, maar alles bij elkaar kan de hoeheid cache flink oplopen en daarmee de applicatie enigsinds vertragen.

Daarnaast ben ik geen voorstander van stored procedures welke zowel het zoeken als het retoureren van de resultaten op zich nemen. Dat maakt het later lastig variaties op het zoeken te implementeren. Jouw beide stored procedures bouwen nu de via tijdelijke tabellen de lijst op het profile id's en retourneren daarna op basis van die tabellen de resultaten uit de profiel tabellen.

Via een select * from #tmpProfileSearchIDs en daarna de drop kun je zelf de verdere afhandeling doen. Daarnaast zijn de profile stored procedures dan ook meer universeel en kunnen dan door meerdere processen worden gebruikt (bijvoorbeeld bij het wijzigen van het profiel).

[DukeMan]

Het terug geven van de gevonden profielen aan de applicatie kan je inderdaad op meerdere manieren doen, maar is hier eigenlijk niet van toepassing. Dat werkt.

Het gaat mij meer om het efficient zoeken op de profielen en de eigenschappen ervan. Kan het op een betere manier dan de 2 voorgestelde?

Ik heb inmiddels even wat tests gedaan met wat test data. Bij 300 profielen en gemiddeld 20 eigenschappen per profiel duurt het met SP1 ongeveer 1 seconde en met SP2 2 seconden. Met de group by duurt dus ook langer.

[Niemand_Anders]

300 profielen? Onze database bevat meer dan 800.000 records en volgens de programma specificaties mag de zoekopdracht nooit langer dan 5 seconden duren. Dat reden dat je query zo traag is juist door jouw 'dynamische tabel'. Omdat ik dus van de subquery slechts 10 records (het aantal wat wij op 1 scherm tonen) nodig heb houd dat het systeem snel. Ik haal dus niet continue de volledige resultset binnen. Onze zoek sp's retoureren altijd alleen maar id's. Hierdoor is de datastream ook kleiner waardoor ook sneller de resultaten worden terug gegeven. Ik weet het, het is allemaal miliseconden werkt, maar als je weet hoeveel instructies een processor in een seconden kan verwerken.

Je zou de boel iets kunnen versnellen als je een vaste collectie van doorzoekbare velden kunt defineren. In de 'pivot' tabel zou je de gegevens uit tblProfileType 'naast elkaar' kunnen zetten. Daardoor kun je op normale kolommen queryen. Helaas zorgt dit wel voor dubbele data, maar met een trigger kun je dit wel gesynchroniseerd houden. maar je zult zien dat je snelheid van de queries er flink op vooruit gaat..

[DukeMan]

Inderdaad, als ik een buffer tabel of iets dergelijks maak, die middels triggers wordt bijgewerkt indien iemand een eigenschap aanpast/vanuit de applicatie zelf, kan ik inderdaad heel snel op de kolommen filteren. Dit zal dan behoorlijk snel kunnen (geen rare truken of iets dergelijks) en het bijwerken van die buffer-tabel kost ook bijna geen tijd, aangezien het enkel 1 kolom/waarde is.

Ik zal dit eens gaan uitwerken. Thanx

PS: ik heb hier een test-omgeving waarin maar 300 profielen zitten. niet helemaal representatief voor de life-omgeving, aangezien er daar behoorlijk wat meer inzitten.