RDO Speed Search 
Author Message
 RDO Speed Search

Hi,
    I am writing a dbase and successfully switched to RDO from DAO ( A good
decision based on speed).  I am having one problem though.  I want to click on
a treeview item and get the ID from the KEY in it and then look up that item
in the database to get other information (dates, parameters etc.)  I need it
to be fast because I want the info to appear immediately in a view
window/statusbar.  I have three approaches.  Here they are:

        Method 1:  A linear search through an already established recordsource

        With MyRS
        .MoveFirst
        While Not .EOF
            If .rdoColumns("counter") = key Then
                temp = .AbsolutePosition
                .MoveLast
            End If
            .MoveNext
        Wend
        .AbsolutePosition = temp
        Debug.Print .rdoColumns("Description")
        End With

        Method 2:  This method does a query and gets a temp recordsource

        Dim psItem As rdoPreparedStatement
        Dim rsItem As rdoResultset
        Dim QSQL As String
        QSQL = "SELECT * FROM project..things WHERE counter = " & key
        Set psItem = cnItem.CreatePreparedStatement("ItemStatement", QSQL)
        Set rsItem = psItem.OpenResultset(rdOpenKeyset, rdConcurReadOnly)
        Debug.Print "Return Value: " & rsItem(0)
        rsItem.MoveFirst
        Debug.Print rsItem.rdoColumns("Description")
        rsItem.Close
        psItem.Close

        METHOD 3: This method uses absolute position on an established
                  Recordset

        WITH MyRS
        .AbsolutePosition = Node.tag   ' tag holds position, not key
        debug.print .rdoColumns("description")

Methods 1 and 2 are about the same speed (slow).
Method 3 is fast and obviously easy, but there is one hitch.  According to the
literature, AbolutePosition can change if I ever reload the recordsource.  I
also read that if I do a delete the AbsolutePosition becomes void.  I can
avoid deletes with a flag, but I may have to reload the recordsource.

Ok, now that you understand the problem, my question is this:  Is there a
better way to do this that is fast?  I thought about doing a combination of 3
with 1 or 2 where I compare the Counter=KEY of the AbsolutePosition, and if
not equal then do another method.  This would seem to be the best solution.  
But, is there a better one?

        -Thanks for the consideration (and help from those who have helped in
the past)

        -brian        



Sat, 20 Mar 1999 03:00:00 GMT  
 RDO Speed Search

Here is an update on what I have done:

I used a modified version of this method-

Quote:
>        Method 2:  This method does a query and gets a temp recordsource

>        Dim psItem As rdoPreparedStatement
>        Dim rsItem As rdoResultset
>        Dim QSQL As String
>        QSQL = "SELECT * FROM project..things WHERE counter = " & key
>    Set psItem = cnItem.CreatePreparedStatement("ItemStatement", QSQL)
>        Set rsItem = psItem.OpenResultset(rdOpenKeyset, rdConcurReadOnly)
>        Debug.Print "Return Value: " & rsItem(0)
>        rsItem.MoveFirst
>        Debug.Print rsItem.rdoColumns("Description")
>        rsItem.Close
>        psItem.Close

Instead of playing with psItem, I just did a requery.  It turns out the reason
things were slow was because I was doing an ODBC Log of all SQL transactions.
 Once that was turned off (in the ODBC Manager) it ran lightening fast.  The
real reason for turning it off was because it was creating 1.5 GIG log files.
 But anyway, it seems to work great now and I am grateful to all who gave
advice.  Some people haven't been acknowledged, but that is because I found
the answer before they contacted me.

So, here is my recommendation - Turn of the log file in ODBC Manager.

        -brian

p.s. There was one other problem (bug) that I found and had to create a work
around, but that is in another post.  I will leave it up to you to find it
(Hint:  Look for "Wierd RDO bug on SQL Server")



Mon, 29 Mar 1999 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Switching from DAO/RDO to ADO - Need crosstabs and SPEED

2. speed up VB4-RDO

3. Speed issue with ADO v's RDO

4. Access/RDO Multi-User Speed Problems

5. Speed, Speed, and Speed!

6. Speed Speed Speed - Cutting down on wasted cycles

7. Contact Search Speed - To damn slow

8. search speed on large access db using jet

9. search speed on large access db using jet

10. SPEED UP SEARCHES, here's how

11. Character-search in string, speeding up?

12. CR8 Slow Search Speed

 

 
Powered by phpBB® Forum Software