Dynamisk opbyggede querys

Rettelse til anden sidste sætning:
Det er specielt de dynamiske grupper, der trækker tænder. Her skal jeg hente en SQL fra gruppe-tabellen, eksekvere denne SQL og finde ud af om personens ID er med i resultatet heraf.

weebfreelanceer.dk>Not quite sure what your SP looks like but if the SQL is changing then it may be an idea to Re-compile the SP each time you use it.

you use the WITH RECOMPILE option when you create it!

Stored Procedure ser nogenlunde således ud. SP'en modtager ID på person og ID på gruppe og skal herefter returnere 1 eller 0 alt efter om personen er med i gruppen eller ej.

Først hentes den SQL-sætning som bestemmer, hvem der er med i den pågældende gruppe. Dernæst bygges en sql-sætning op, som lægger resultatet af gruppens sql over i en temporær tabel, således at man bagefter kan finde resultatet og se om personen var med i gruppen eller ej.

---------------------------------------------------------------

CREATE  PROCEDURE SP_IsGroupMember @personGUID UNIQUEIDENTIFIER, @groupGUID UNIQUEIDENTIFIER, @returnresult TINYINT OUTPUT
AS

-- Hent SQL
DECLARE @sql nvarchar(1000)
SET @sql = (SELECT groupSQL FROM group WHERE groupGUID = @groupGUID)

-- Opret SQL Streng, der skal lægge resultatet i tempgroupmembers
-- Denne tabel indeholder en GUID og et INT-felt
DECLARE @tempsql nvarchar(1100)
SET @tempsql = N'INSERT INTO tempgroupmembers SELECT @restempGUID, (SELECT COUNT(*) FROM person WHERE personGUID = ''{'+CONVERT(varchar(50),@personGUID)+'}'' AND personGUID IN ('+@sql+'))'

-- Opret GUID til ny resultatrecord
DECLARE @resGUID uniqueidentifier
SET @resGUID = newid()

EXECUTE sp_executesql @tempsql, N'@restempGUID uniqueidentifier', @restempGUID = @resGUID

-- Hent resultat fra tempgroupmembers
DECLARE @personcount TINYINT
SET @personcount = (SELECT personcount FROM tempgroupmembers WHERE GUID = @resGUID)

-- Slet resultat fra tempgroupmembers
DELETE FROM tempgroupmembers WHERE GUID = @resGUID

-- Sæt flag, hvis person er medlem af gruppen
IF @personcount > 0
  SET @returnresult = 1

END -- Procedure

--------------------------------------------------------

Et eksempel på en gruppeSQL kunne være:

(SELECT personGUID FROM person WHERE DATEPART(yyyy,birthdate) > 1985)

If we could see your tables/fields, or at least what is importinat here, and an example as to what your trying to do then maybe we can make other suggestions.

I can also see that you are executing another SP and I am guessing that the SQL for that is in @tempsql! So because the SQL is changing all the time then it may help perfomance IF you use the re-compile option.

Om den recompiles eller ej udgør ikke den store performance lige i denne sammenhæng, det vil nok være bedre at løse problemet på en anden måde. Evt. ved en anden logik.

For at du kan få den korrekte hjælp bør du poste design af tempgroupmembers og forklare hvorfor du har en sql statement til at afgøre medlem af gruppen.
Post de tabeller som vedr. det hele og skriv i pseudo kode hvad du vil opnå.

Umiddelbart som jeg ser det er det gjort meget mere besværligt end det behøves.

Først lige lidt tabeldesign:

CREATE TABLE tempgroupmembers  (
  guid UNIQUEIDENTIFIER,
  personcount TINYINT
)

CREATE TABLE person (
  personguid UNIQUEIDENTIFIER,
  personname VARCHAR(100),
  birtdate DATETIME
)

CREATE TABLE group (
  groupguid UNIQUEIDENTIFIER,
  groupname VARCHAR(100),
  groupsql VARCHAR(2000)
)


Lad os stille et simplificeret eksempel op:

Jeg har en gruppe, der hedder ”Juniorer”. Medlemmerne af gruppen skal være de personer, der er mindre end eller lig med 18 år. For ikke at skulle vedligeholde en statisk liste med ID på de personer der opfylder dette kriterie, har jeg i stedet lavet en SQL, som til enhver tid vil finde de personer, der er med i gruppen.

SQL ligger i gruppetabellen i feltet ”groupSQL” og den har følgende indhold:

SELECT person.personguid FROM person WHERE DATEDIFF(yyyy, person.birthdate, getdate()) <= 18

Jeg tager nu fat I en person, og skal finde ud af om vedkommende er med I gruppen eller ej.

Derfor kalder jeg min SP (SP_IsGroupMember). Denne SP skal have et ID på personen og et ID på gruppen og den returnerer herefter 1 eller 0 alt efter om personen er medlem eller ej.

Som tidligere beskrevet starter denne SP med at hente SQL’en på gruppen over i @sql.

@sql = SELECT person.personguid FROM person WHERE DATEDIFF(yyyy, person.birthdate, getdate()) <= 18

Dernæst bygger jeg ud fra @sql en ny SQL-streng, der kan lægge et count over i tempgroupmembers. Grunden til dette er at jeg ikke kan finde andre måder at returnere et resultat fra en dynamisk opbygget SQL-streng.

@tempsql = INSERT INTO tempgroupmembers SELECT @restempGUID, (SELECT COUNT(*) FROM person WHERE personGUID = ''{'+CONVERT(varchar(50),@personGUID)+'}'' AND personGUID IN ('+@sql+'))

Ved hjælp af “sp_executesql” kan jeg få lov at erstatte parametre, der ændrer sig løbende. Det bruger jeg til at give mit resultat et unikt ID. Resultatet af count gemmes altså i tempgroupmembers med et unikt ID og det antal personer som har den aktuelle personGUID og er medlem af gruppen. Count vil altid være 1 eller 0.

Når jeg nu herefter kender det unikke ID på mit resultat slår jeg det op i tempgroupmembers, og returnerer resultatet herfra.

Det her bliver gjort rigtigt mange gange, da jeg har mange personer (brugere) og mange dynamiske grupper, så bare en lille optimering, ville sikkert gøre en del for performance.

Hej igen..

aha nu forstår jeg bedre, lige et spørgsmål mere. Nu siger du gruppe sql, kan en person tilhøre flere grupper? og/ eller findes der flere grupper end under 18år?

hmm ja der er flere grupper fik jeg lige læst haha.

Kan du komme med et eksempel på en anden gruppe?

Der kan være uendeligt mange dynamiske grupper. Man kunne forestille sig en gruppe kaldet "Sønderjyske kvinder", hvor SQL'en spørger på postnummer og køn... U name it .... der er selvfølgelig mange flere felter i tabellen "person" end jeg lige har skitseret i mit simplificerede eksempel tidligere.

Ok! Så ville jeg nok definere alle mine grupper i en funktion eller måske sp, nu er nedenstående bare lidt simpel og kun med groupid istedet for guid, men idéen er der:

create function groupmember(@groupid as int, @dt as datetime)
returns bit
as
begin
declare @r int
set @r = 0

if @groupid = 0
if exists(select personguid from person where DATEDIFF(yyyy, person.birthdate, @dt) <= 18 )
    set @r = 1

if @groupid = 1
    set @r = 1

return 0

end

----------------

og så kalde den via din sp noget ala:

IF exists(select personguid from person where personguid = @personguid and dbo.groupmember(personguid , getdate()) = 1)
SET @returnresult = 1

-----

Håber du kan se hvad jeg vil :O)

Jeg kan godt se hvad du vil... jeg legede også selv med tanken om at lægge det i views, men når jeg nu har 130 forskellige afdelinger som hver har 10-15 dynamiske grupper, så bliver det til temmelig mange funktioner.

En anden mulighed er at benytte computed columns! Eller måske datatypen 'table' til definition af gruppen.

Det kan jeg nu også hjælpe lidt med... Jeg vil lige prøve at lave en forespørgsel rundt i netværket, fordi det egentlig er en ganske interessant problemstilling du sidder med! Måske du slet ikke skulle bruge en relationel db ;O)

Hej igen!
Hvordan bliver den groupsql opbygget?

groupSQL ligger som et felt i tabellen groups.

Jeg henter den fra min SP og eksekverer den ved hjælp af EXECUTE sp_executesql

Håber det er svar på spørgsmålet, ellers må du lige uddybe.

Men hvordan bliver selve den sql opbygget inden den kommer ind i feltet?

Lige nu lægger jeg den manuelt ind, men det er planen at lave en query builder

Jeg mener bare alle disse grupperegler som du jo foretager i hånden. De skal jo som du siger laves om til noget produktionsmæssigt på et tidspunkt. Dvs. at du ligeså godt kan lave det korrekt fra starten af, det dette vil forbedre dit db-design væsentligt. Det er dødens pølse at sidde og rette op på et dårligt skalérbart design :O(
Da det jo er en afgrænset mængde attributter der kan udgøre dit kriterie handler det om at opstille disse så logisk som muligt og derved forbedre performance.

Det er netop ikke en afgrænset mængde attributter der kan udgøre mit kriterie.

Hvis jeg satte mig ned og prøvede at forudse hvilke krav der kunne opstå i fremtiden, ville jeg låse mig fast på hvilke kriterier, man kan bruge til at gruppere personer med og det vil jeg gerne undgå.

Jeg mener helt sikkert at denne metode med SQL-sætninger er yderst fleksibel og skalerbar, men sådan noget går jo ofte ud over performance

Måske findes der ikke en gylden løsning på problemet.

Nej løsninger er så for så vidt egentlig også ganske smart, der er bare lige et lille men. Hvordan vil du omsætte de dynamiske grupper til noget en webuser selv kan vedligeholde/ bestemme??
Den eneste grund, som jeg ser det, til at det er dynamisk og dejligt fleksibelt er at du selv laver de påkrævede selects manuelt!

Hvad gør du hvis der pludselig er 1000 personer?

Når jeg skal lave brugergrænsefladen til "Query Builder", er jeg selvfølgelig nødt til at lave en eller anden form for afgrænsning på hvilke felter webuser'en kan bruge som kriterier.

Men her ligger begrænsningen så i brugergrænsefladen, som jeg med tiden kan ændre, hvis det skulle vise sig nødvendigt. Selve løsningen med SQL'en i gruppetabellen vil jeg nødig lave om på.

Om der er 3 eller 10000 personer skulle helst være fuldstændig ligegyldigt.