Efficiency with hierarchical data 
Author Message
 Efficiency with hierarchical data

I'm using VB6, ADO w/ Oracle OLE DB to connect to an Oracle 8 database.

I've got two tables that I'm accessing to fill in a treeview control. In the
first table, I have a field called LOCATION_ID. The values contained in this
field are all formatted ####.####. The first 4 numbers are what we refer to
as a Page number and the second 4 are called Item numbers. I iterate through
this first table, selecting distinct Page numbers. For each new Page number,
I query the second table to see if there is an entry for this number (IE,
checking to see if the number is actually being used.) Then, I check the
first table once more for distinct Item numbers for each page, and check the
second table again to see if these Items are actually being used. So, I
could conceivably be hitting the DB 4 times for every record. I know this is
not the best way to set up a hierarchical database, but I didn't set it up,
and its what I have to work with.

So, my question is, what is the most efficient way to pull this off.
1) I've considered opening a recordset for each of the four checks -- each
time, closing the old and opening a new recordset each time I check a new
Item number. This causes me to hit the DB many times.
2) I've considered opening the four recordsets in their interity (instead of
Item by Item) and Filtering by the Item numbers to check if they exist. This
way, all the real work should be done on the client side. (I think)
3) I've considered using the requery method for each of the recordsets
involved in checking the Item number, but isn't that the same thing as #1?
4) And I've also considered opening the four recordsets in their interity
(as in #2) and using the Find method to check for the Item's existance.

I understand I may not have explained this as clearly as I would like to,
but I hope some of you can follow me on this.
Thanks for the help...

Sun, 18 Apr 2004 04:26:11 GMT
 [ 1 post ] 

 Relevant Pages 

1. Efficiency of hierarchical queries

2. Data Shaping/Hierarchical Data

3. security for Hierarchical data model allowing users to access data specific to their region

4. Efficiency of Calc vs. hard data

5. data storage efficiency

6. pulling blocks of data from a table - efficiency

7. efficiency: Word data into FMPro 5.5?

8. hierarchical data retrieval

9. Hierarchical data retrieval

10. how to insert/update hierarchical data with openxml/updategrams

11. Hierarchical data

12. Manipulate hierarchical data

Powered by phpBB® Forum Software