Slow SQL SELECT Code - Any Suggestions

[swisstoni]

Hey guys, first time post here but looks like a decent forum!

I'm moving a lot of our web based admin stuff over from a PHP based online system to Access, using ODBC link tables.

In PHP I had a neat enough little script to run through and generate a list of categories like this:

Main Category
--->Sub Category
--->Sub Category 2
--->---->Sub Sub Category
--->Sub Category 3

and so on!

I have a table with the categories in, with columns for cat_name, cat_id, and cat_parent. If the parent is zero then it's a main category.

I've transferred the code into VBA but it's real slow. I've around 200 categories and nothing more than 1/2 levels deep, so I don't see why the recursion seems to be taking so long. In PHP it generates this in no time, but Access is being real slow! Any suggestions?

My code is shown below:

Private Sub Display_cats(parent As Integer, hide As Integer, depth As Integer)

Dim sSQL As String
sSQL = "SELECT catid, catname, parent FROM [categoriesW] WHERE parent= " & parent & " AND catid <> " & hide & " AND catname NOT LIKE 'temp%' AND catid <> 0 ORDER BY catname;"

Dim catname As String
Dim prefix As String

depth = depth + 1

For i = 1 To depth
prefix = prefix & "--->"
Next i

Dim MyDB As Database, MyRec As Recordset
Set MyDB = CurrentDb
Set MyRec = MyDB.OpenRecordset(sSQL, dbOpenForwardOnly)
While MyRec.EOF = False

If MyRec![parent] = 0 Then
catname = StrConv(MyRec![catname], vbUpperCase)
Else
catname = prefix & MyRec![catname]
End If

Combo20.AddItem (catname & ";" & MyRec![catid])

Display_cats MyRec![catid], hide, depth

MyRec.MoveNext
Wend

depth = depth - 1

MyRec.Close
MyDB.Close

End Sub

 

[namliam]

Private Sub Display_cats(parent As Integer, hide As Integer, depth As Integer)

    Dim sSQL As String
    sSQL = "SELECT catid, catname, parent FROM [categoriesW] WHERE parent= " & parent & " AND catid <> " & hide & " AND catname NOT LIKE 'temp%' AND catid <> 0 ORDER BY catname;"
    
    Dim catname As String
    Dim prefix As String

    depth = depth + 1
    
    For i = 1 To depth
        prefix = prefix & "--->"
    Next i
        
    Dim MyDB As Database, MyRec As Recordset
    Set MyDB = CurrentDb
    Set MyRec = MyDB.OpenRecordset(sSQL, dbOpenForwardOnly)
    While MyRec.EOF = False
    
        If MyRec![parent] = 0 Then
            catname = StrConv(MyRec![catname], vbUpperCase)
        Else
            catname = prefix & MyRec![catname]
        End If
    
        Combo20.AddItem (catname & ";" & MyRec![catid])
        
        Display_cats MyRec![catid], hide, depth
        
        MyRec.MoveNext
    Wend
    
    depth = depth - 1
    
    MyRec.Close
    MyDB.Close

End Sub

Why do it this way anyway? Recursive functions are nice if you have unlimited and unpredictable depth.
If you have limited depth and/or predictable depth, use a query as the rowsource for your combobox. Much faster... tho less reliable if you go beyond the "pre programmed" depth.

 

[swisstoni]

My knowledge of Access is nowhere near my knowledge of PHP!

I was trying to do this using a query, but I'm really quite unsure where to start. I can get a query that displays, for example, all cats where the parent = 0, but how then do I get just that first level of recursion to then displaythe categories underneath that main category?

 

[namliam]

It isnt as much access knowledge as it is " general query " knowledge is it??

Take this SQL and put it into a query called "qryLevel1"
SELECT categoriesW.CatID, categoriesW.CatID AS Parent0, 0 AS Parent1
FROM categoriesW
WHERE categoriesW.ParentID=0;

Now take this query and call it "qryLevel2"

SELECT categoriesW.CatID, categoriesW.ParentID AS Parent0, categoriesW.CatID AS Parent1
FROM categoriesW INNER JOIN 
         qryLevel1 ON categoriesW.ParentID = qryLevel1.CatID;

Now make a third query, call it anything as it is your final result:
SELECT * FROM qryLevel1
UNION select * from qryLevel2
ORDER BY parent0, parent1;

Expand on the above to get to the possible levels you want to have, and you are done.

 

[swisstoni]

Thankyou! That appears to be exactly what I need, however it doesn't display the catname - it only returns the catid, parent0 and parent1.

I changed the first two queries so they also SELECTed catname, but the final one doesn't seem to pick this up...

Also - is there a way to get the "-->" back for sub categories?

Any ideas? Thankyou SO much for your help so far!

 

[swisstoni]

Apologies - I've got the catname's displayin now - might have been a refresh problem on my part!

Still, any idea how I might go about prefixing subcats with "--->" - should I concat this onto the second query maybe?

 

[swisstoni]

Sorry for the many many posts, I've now sorted out the "--->" addition.


My last question - how can I sort the list of main categories alphabetically? I see at the minute they're sorted by parent0 - I've tried adding catname into the ORDER BY clause in various places but it seems to be messing things up!

 

[swisstoni]

Sorry for the many many posts, I've now sorted out the "--->" addition.


My last question - how can I sort the list of main categories alphabetically? I see at the minute they're sorted by parent0 - I've tried adding catname into the ORDER BY clause in various places but it seems to be messing things up!

 

[namliam]

SELECT * FROM qryLevel1
UNION select * from qryLevel2
ORDER BY Catname;

Should order it by the category name, except your sample in your original post seemed to indicated you wanted it by order or ranking....i.e.
Cat 1
Children of cat1
Grandchildren of Cat1
Cat 2

If you alphabettize above then you get
Cat 1
Cat 2
Children of cat1
Grandchildren of Cat1

But if that is what you are looking for.... then sure...

 

[swisstoni]

I realise that, but is there any way I can have the main categories in alphabetical order. For example:

A Main Category
---->Child Cat
---->D Child Cat
---->E Child Cat
B Main Category
---->Another Category
---->Yet Another category


So that the main categories, and then the sub categories are all in alphabetical order, but still giving the child categories underneath the main categories?

 

[namliam]

Then you would have to order first on 'level' then on name... like so.

SELECT * FROM qryLevel1
UNION select * from qryLevel2
ORDER BY parent0, parent1, Catname;

 

[swisstoni]

Unfortunately that doesn't seem to work - it's still sorting by the parent categories first and displaying the categories in any order it wants.

 

[swisstoni]

Any ideas?

 

[namliam]

Hmz, you are right, doesnt work...

Tho it is not exactly showing any particular order... it is showing in the order of CatID...

This is going to be tough to do....
Lets presume a table, ID, Parent, Name
1 0 i
2 1 h
3 1 g
4 1 f
5 2 e
6 2 d
7 2 c
8 2 a
9 3 b

You want to see
i
==> f
==> g
====> b
==> h
====> a
====> c
====> d
====> e

I am a little stumped for the moment, but... might get a brain flash later.. for now tho... sorry...

 

[swisstoni]

Thanks for all your help so far.

I agree I thought it looked tricky!

 

[swisstoni]

Any further thoughts perhaps?

 

[namliam]

Sorry no new thoughts