Querying a recordset using SQL?

Is this possible to do?

I have a Database that I run an SQL query against. Basically, the database holds information for all of our computers. Each store has multiple computers (obviously).

I'm loading this info into a Treeview. A node is created for each store and underneath that node the computers are loaded as children. It's much quicker for me to pull the entire table down from the database into a recordset and manipulate it in code.

What I want to do is be able to get pull all of the computers for a store so I can create the Store node and the subsequent child nodes for each computer. I can easily do this using SQL queries against the database but it takes a LONG time.

Okay, now that my incredibly confusing explanation is over with, I'd like to be able to query my database and pull the whole table down (since I need all the info anyway) into a Recordset. Then I want to take that Recordset and create a new recordset from it. Is this possible to do locally? Is it possible to run a SQL query against a Recordset?

How about using Filter instead?

Filters are ok but on larger recordsets they are also slow. If you are using SQL Server then you should create some Stored Procedures for this as they are the fastest performing method for returning data from the db.

Your tree-view control makes sense - but you should be loading only the "parent" nodes to start - with a SELECT...GROUP BY query. This will return a row for each store. That will be very fast.

Then when the user opens a parent node you then query for the computers for only that store.

That's the way to talk to MS SQL. It doesn't abuse the database or server engine and doesn't abuse the bandwidth of the network.

Quote:

---

Originally Posted by szlamany

Your tree-view control makes sense - but you should be loading only the "parent" nodes to start - with a SELECT...GROUP BY query. This will return a row for each store. That will be very fast.

Then when the user opens a parent node you then query for the computers for only that store.

That's the way to talk to MS SQL. It doesn't abuse the database or server engine and doesn't abuse the bandwidth of the network.

---

Actually, in my case it does. It takes a bit longer to load the child nodes. If I'm sitting on top of the database this is feasible, but there are times where users are connected VIA VPN on a slower connection. The latency of the queries is the biggest issue. Even on dial-up with a VPN connection, returning the entire table is very quick since it is all easily compressible text. If I break it down into multiple queries the latency starts degrading performance.

I should have mentioned the latency issue in my first post.

EDIT: A little off topic, can I get rid of this strange hierarchy view of the replies? It's making it difficult to read and follow the flow of the suggestions.

My display mode (link upper right) is linear - that makes it easy for me to view a thread - what is yours?

Then why not return two recordsets at the start - one to make the parent nodes (small and quick) and immediately a full recordset to start populating the child nodes.

What code are you using to fill the tree-view?

Quote:

---

Originally Posted by szlamany

My display mode (link upper right) is linear - that makes it easy for me to view a thread - what is yours?

---

AHHHh! Much better lol!

How about, when you select the computers, order by the shop. That way, when they open a node you can do a find on the recordset to get the first instance of a record with the shop, then loop through until the shop changes. Hold on and I'll knock up an example.

something like this:-

VB Code:

Private rs1 As ADODB.Recordset
Private rs2 As ADODB.Recordset
 
Private Sub Form_Load()
    Dim conn As New ADODB.Connection
 
    conn.Open ("my database string")
    Set rs1 = conn.Open("Select distinct shop from computer_table")
    Set rs2 = conn.Open("Select computer, shop from computer_table order by shop")
    
    'load up you tree view shop nodes from rs1
    While Not rs1.EOF
        TreeView1.Nodes.Add , , rs1.Fields("shop").Value, rs1.Fields("shop").Value
        rs1.MoveNext
    Wend
End Sub
 
Private Sub TreeView1_Click()
Dim shop As String
    shop = TreeView1.SelectedItem.Key
    rs2.Find ("shop = '" & shop & "'")
    While Not rs2.Fields("shop") = shop And Not rs2.EOF
        TreeView1.Nodes.Add shop, tvwChild, rs2.Fields("computer").Value, rs2.Fields("computer").Value
        rs2.MoveNext
    Wend
End Sub

I only knocked it up to demonstrate the principle and haven't run it so forgive any buglets that might have crept in.

That's exactly what I had in mind!

Quote:

---

Originally Posted by FunkyDexter

something like this:-

VB Code:

Private rs1 As ADODB.Recordset
Private rs2 As ADODB.Recordset
 
Private Sub Form_Load()
    Dim conn As New ADODB.Connection
 
    conn.Open ("my database string")
    Set rs1 = conn.Open("Select distinct shop from computer_table")
    Set rs2 = conn.Open("Select computer, shop from computer_table order by shop")
    
    'load up you tree view shop nodes from rs1
    While Not rs1.EOF
        TreeView1.Nodes.Add , , rs1.Fields("shop").Value, rs1.Fields("shop").Value
        rs1.MoveNext
    Wend
End Sub
 
Private Sub TreeView1_Click()
Dim shop As String
    shop = TreeView1.SelectedItem.Key
    rs2.Find ("shop = '" & shop & "'")
    While Not rs2.Fields("shop") = shop And Not rs2.EOF
        TreeView1.Nodes.Add shop, tvwChild, rs2.Fields("computer").Value, rs2.Fields("computer").Value
        rs2.MoveNext
    Wend
End Sub

I only knocked it up to demonstrate the principle and haven't run it so forgive any buglets that might have crept in.

---

That approach will keep two cursors open on the SQL Server the whole lifetime of the form. Doesn't sound like a good approach to me.

What is the total number of records (stores + computers)? How are the clients authenticating on the SQL Server (intergrated security has quite a few roundtrips between the client and the server)? I could actually be better for you to read all the data and draw the treeview in a one time operation.

Can't the RS's be forward only read only - or does that not allow the .FIND?

We always load our RS's immediately into controls and then kill the RS - so I've never worked with an approach like this.

We load some pretty large RS's into FLEX GRIDS - they seem to load rather quickly. That's why I asked to see the code being used by Chris H to load the tree view...

No, a recordset opened as forward only does not support the Find method.

Yes, it would be interesting to see his code. Does he open and close the connection every time it is used? In that case I understand why it is slow when using a dial-up connection.

Well, what I ended up going with was kind of like what FunkyDexter suggested.

After getting the entire recordset sorted by store I check to see if a node has been created already for that store, if it has been created I run through the rest of the recordset and create them as children until I hit a store I haven't seen before. If it has not been created already, I create the parent node and then the child node to represent the computer and continue on.

The database connection stays open throughout most of the program. I think the slowness is due maninly to the fact that on a dial up connection with VPN you can be looking at roundtrip times of 1 second. Multiply that by a query for each store (at this time it is 15 stores with an average of 18 computers at each one, but it will eventually be about 70 stores in total and growing) and it adds up. The volume of data being returned contributes to the slow down I'm sure but...

Sitting on top of the server with a LAN connection you'd never know the difference of trying both methods. MySQL completes the queries so quickly it's instantaneous (sp?). But get it out on a dial up VPN connection and forget about it.

Quote:

---

That approach will keep two cursors open on the SQL Server the whole lifetime of the form. Doesn't sound like a good approach to me.

---

Couldn't he just use diconnected recordsets (I think it's just a case of using the adUseClient flag). I've never had a need to do it myself so I don't know enough to be sure.

Quote:

---

Originally Posted by FunkyDexter

Couldn't he just use diconnected recordsets (I think it's just a case of using the adUseClient flag). I've never had a need to do it myself so I don't know enough to be sure.

---

Thats true. Using adUseClient as CursorLocation will load the whole recordset to the client and no cursors will be left open at the sql server.