Optimer funktion der henter en række id'er fra mysql

Prøv noget i stil med dette:

Function FindIDer(ByVal ID)
    Dim idArr(1000)
    idArrIdx = 0

    sql0 = SELECT id FROM kategorier WHERE ParentID=" & id & " AND active=1"
    Set rs0 = Conn.Execute(sql0)

    Do While Not rs0.EOF
        idArr(idArrIdx) = rs0("id")
        idArrIdx = idArrIdx + 1
        rs0.MoveNext
    Loop

    sql1 = "SELECT id FROM kategorier WHERE ParentID IN (" & Join(idArr, ",") & ") AND active=1"
    Set rs1 = Conn.Execute(sql1)

    Do While Not rs1.EOF
        idArr(idArrIdx) = rs1("id")
        idArrIdx = idArrIdx + 1
        rs1.MoveNext
    Loop

    FindIDer = Join(idArr, ",")
End Function

Der manglede lige et par dims og en " foran SELECT men det virker ikke. Jeg får en unknown WHERE clause. Har rettet funktionen til følgende:

Function FindIDer(ByVal ID)
    Dim idArr(1000)
    dim idArrIdx, sql0, rs0
    idArrIdx = 0
   
    sql0 = "SELECT id FROM kategorier WHERE ParentID=" & id & " AND active=1"
    Set rs0 = Conn.Execute(sql0)
   
    Do While Not rs0.EOF
        idArr(idArrIdx) = rs0("id")
        idArrIdx = idArrIdx + 1
        rs0.MoveNext
    Loop
   
    sql1 = "SELECT id FROM kategorier WHERE ParentID IN (" & Join(idArr, ",") & ") AND active=1"
    Set rs1 = Conn.Execute(sql1)
   
    Do While Not rs1.EOF
        idArr(idArrIdx) = rs1("id")
        idArrIdx = idArrIdx + 1
        rs1.MoveNext
    Loop
   
    FindIDer = Join(idArr, ",")
End Function

Jeg gætter på at det er i sql1 at den går galt - sikkert i min Join. Prøv lige denne debug udskrift:

    sql1 = "SELECT id FROM kategorier WHERE ParentID IN (" & Join(idArr, ",") & ") AND active=1"
    Response.Write "DEBUG: " & sql1 & "<br>"
    Set rs1 = Conn.Execute(sql1)

Det er den sql det er galt med. Den giver følgnede:
Select id From kategorier Where ParentID In (373,374,379,380,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,)

Og nu får jeg følgnede fejl:
You have an error in your sql syntax ... near ,,,,,,,,,,,,,,

Tænkte det nok. Det betyder at de to linjer skal erstattes med noget i stil med:

inClause = Join(idArr, ",")
dblCommaPos = InStr(inClause, ",,")
inClause = Left(inClause, dblCommaPos-1)

sql1 = "SELECT id FROM kategorier WHERE ParentID IN (" & inClause & ") AND active=1"
Response.Write "DEBUG: " & sql1 & "<br>"
Set rs1 = Conn.Execute(sql1)

Det giver følgense sql:
SELECT id FROM kategorier WHERE ParentID IN (373,374,379,380,382,384,386,388,390,392,393,396,397,398,400,401,402,403,405,406,407,408,410,418,420,425,426,427,518,537,562,666,680) AND Aktiv = 1

Men følgende fejl:
You have an error in you SQL syntax; ... near ',,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,' at line 1

Hmm ... eksperten har forkortet sql'en ... her er den lige delt op:
SELECT id FROM kategorier WHERE ParentID IN
(373,374,379,380,382,384,386,388,390,392,393,396,397,398,400,401,402,403,405,406,
407,408,410,418,420,425,426,427,518,537,562,666,680) AND Aktiv = 1

Den fejlbesked stemmer da ikke overens med hvordan SQL'en ser ud?! Der er jo ikke noget ',,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,' i den.

Ahh, havde overset hvor fejlen er. Den ligger i en af de sql strenge der benytter det respons der kommer fra ovensteånde kode. Det der lige skal ændres, er at den skal returner en streng som følgende:
Or ParentID = 373 Or ParentID = 374 Or ParentID = 379

Og ikke:
373,374,379...

Kan du hjælpe med det?

Der er ingen grund til lige den ændreing - hvis det er output fra denne funktion som er problemt så skal den selvfølgelig også bare ændres tilsvarende:

Function FindIDer(ByVal ID)
    Dim idArr(1000)
    Dim idArrIdx, sql0, rs0
    Dim inClause, dblCommaPos
    idArrIdx = 0
   
    sql0 = "SELECT id FROM kategorier WHERE ParentID=" & id & " AND active=1"
    Set rs0 = Conn.Execute(sql0)
   
    Do While Not rs0.EOF
        idArr(idArrIdx) = rs0("id")
        idArrIdx = idArrIdx + 1
        rs0.MoveNext
    Loop

    inClause = Join(idArr, ",")
    dblCommaPos = InStr(inClause, ",,")
    inClause = Left(inClause, dblCommaPos-1)

    sql1 = "SELECT id FROM kategorier WHERE ParentID IN (" & inClause & ") AND active=1"
    Set rs1 = Conn.Execute(sql1)
   
    Do While Not rs1.EOF
        idArr(idArrIdx) = rs1("id")
        idArrIdx = idArrIdx + 1
        rs1.MoveNext
    Loop

    inClause = Join(idArr, ",")
    dblCommaPos = InStr(inClause, ",,")
    inClause = Left(inClause, dblCommaPos-1)
   
    FindIDer = inClause
End Function

Og du har sat et index på ParentID (og gerne også fortalt det er en fremmednøgle) ?

ParentID har fået et Index i datatype men jeg har ikke fortalt den at det er en fremmednøgle. Hvad hjælper dette og hvor gør man dette i en mysql database?

Det er outputtet som er et problem. Funktionen benytter jeg mange gange i mine koder og den skal returner outputtet som:
"Or ParentID = 373 Or ParentID = 374 Or ParentID = 379 ..."

Og ikke:
"373,374,379 ..."

Og jeg forstår ikke lige hvorfor du i ovenstående funktion har tilføjet denne stump kode igen, således at den ligger 2 gange i funktionen:

    inClause = Join(idArr, ",")
    dblCommaPos = InStr(inClause, ",,")
    inClause = Left(inClause, dblCommaPos-1)

Index bør gøre noget ved "Lige nu er funktionen meget langsom"

Og hvorfor vil du have returneret noget med "OR ..." når du kan bruge "373,374,379 ..." som du har set før.

Glem det med fremmednøgle - det er nok for meget lige nu ;)

Det den lille stump kode gør er at reducere sådan noget som:

373,374,379,380,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

- til:

373,374,379,380

Grunden til at jeg har den to gange er simpel nok; Der opbygges et array, idArr, med id'erne undervejs i koden. I første omgang bruges dette array til at beregne en ny SQL sætning:

sql1 = "SELECT id FROM kategorier WHERE ParentID IN (" & inClause & ") AND active=1"

Denne bruges efterfølgende til at trække flere id'er ud af din tabel. Disse havner ligeledes i arrayet. Når at scriptet er færdigt med at køre ligger alle id'erne i arrayet, og de skal så returneres som

373,374,379,380,1,2,3

- hvis 1, 2 og 3 er de nye id'er. Hvis man blot lader koden returnere

FindIDer = Join(idArr, ",")

- som i min kode fra 13/11-2005 19:13:14 så får man igen en masse ”,,”'er for meget i enden. Den anden gentagelse af kode-stumpen fjerner denne.

Jeg ved ikke om der er nogen hastighedsmæssig forskel på om du bruger IN og en komma-liste eller om du bruger OR (jeg tvivler). Men det er under alle omstændigheder, rent programmeringsmæssigt, meget nemmere at bruge IN og kommaer.

Årsagen til at jeg vil have det returneret med en "OR" er at jeg ikke kender andre måde at kunne lave sql scriptete på. Det benyttes eks. som nedenstående kode viser. Kan det trækkes ud på en anden måde da med komma seperaeret tal? Desuden skal jeg haven en midlertidig løsning med "OR" da jeg ikke lige har tid til at rette mine sql strenge igennem til at funger med kommaer.

Og mht. index så havde jeg det også på før, hvor en side tog op til 1.5 sek at blive lavet.

IDer = FindIDer(DB_ID)
Sql3 = "SELECT Count(*) as Antal FROM Data Where (ParentID = " & DB_ID & IDer & ") and Dato > '" & NyDato & "'"

Disse to SQL-sætninger giver det samme resultat:

SELECT * FROM tabel WHERE id IN (1,3,5,7,65,78)
SELECT * FROM tabel WHERE id=1 OR id=3 OR id=5 OR id=7 OR id=65 OR id=78

Versionen med IN er både mere kompakt, og er samtidigt lettere at overskue, end versionen med OR.

Jeg har lige kigget mine koder igennen, og det er for en stor omgang at skulle ændre hele min kode til at arbejde med en in istedet for or. Derfor vil jeg bede om at få lavet en slut del i funktionen (i form at en stump kode eller en anden funktion) der ændre, således at funktionen sender et resultat tilbage med en OR opbygning.

Prøv at rette den sidste linje i funktionen til:

FindIDer = "ParentID=" & Replace(inClause, ",", " OR ParentID=")

Det hjalp, men nu får jeg nogle gange fejl når funktionen kaldes. Fejlen skyldes at inClause er tom og jeg får derfor en tom sql som følgende:
SELECT id FROM kategorier WHERE ParentID IN () AND Aktiv = 1

Det drejer sig om følgende linie der giver fejl:
sql1 = "SELECT id FROM kategorier WHERE ParentID IN (" & inClause & ") AND Aktiv = 1"

Sådan:

Function FindIDer(ByVal id)
    Dim idArr(1000)
    Dim idArrIdx, sql0, rs0
    Dim inClause, dblCommaPos
    idArrIdx = 0
   
    sql0 = "SELECT id FROM kategorier WHERE ParentID=" & id & " AND active=1"
    Set rs0 = Conn.Execute(sql0)
   
    Do While Not rs0.EOF
        idArr(idArrIdx) = rs0("id")
        idArrIdx = idArrIdx + 1
        rs0.MoveNext
    Loop

    If idArrIdx <> 0 Then
        inClause = Join(idArr, ",")
        dblCommaPos = InStr(inClause, ",,")
        inClause = Left(inClause, dblCommaPos-1)

        sql1 = "SELECT id FROM kategorier WHERE ParentID IN (" & inClause & ") AND active=1"
        Set rs1 = Conn.Execute(sql1)
   
        Do While Not rs1.EOF
            idArr(idArrIdx) = rs1("id")
            idArrIdx = idArrIdx + 1
            rs1.MoveNext
        Loop
    End If

    idArr(idArrIdx) = id

    inClause = Join(idArr, ",")
    dblCommaPos = InStr(inClause, ",,")
    inClause = Left(inClause, dblCommaPos-1)
   
    FindIDer = "ParentID=" & Replace(inClause, ",", " OR ParentID=")
End Function

- og så har jeg også rettet den fejl at id'et ikke selv var med. :^|

Sådan ... det er super! Så køre det umidelbart som det skal! Smid endelig et svar så du kan få dine point!

Du får lige 40 point ekstra oven i hatten for den gode indsats.

Og hvis du skulle finde nogle småting der kan optimer koden meres så hører jeg meget gerne fra dig igen!

Og mange tak for hjælpen!

Jeg er nu lidt spændt på at høre hvordan det egentlig gik med ydelsen? Det burde give en hel del at reducere antaller af SQL-kald, men det eksakte resultat afhænger af så mange forskellige ting.

Og husk nu rådet mht. at sætte index på ParentID.

... og et svar :^)

Index er på i min database ... der tror jeg ikke jeg kan hente mere.

Og efter hvad jeg kan se har det gjort genereringstiden 3 gange så hurtig på min side, men har også været rundt og ændre andre steder i min kode. Nu er jeg nede på under 0.5 sek ... vil gerne ned på under 0.3 så jeg må lede videre!

Endnu engang tak.

Du kan altså også hente noget tid i koden, hvis du går fra at bruge den der "ParentID=" notation til IN-notationen. Dels bliver din applikation mindre RAM-krævende (kortere SQL-strenge) og dels bliver den hurtigere, fordi at der ikke skal laves en resource-krævende Replace til sidst i funktionen.

Takker for point :^)