Recordset taking long Processing

[tdubs]

hi all


I am having problems running a recordset. Throught my program.. what I do to query data from tables I use recordsets. This is what I usually do

Dim RS As DAO.Recordset
Dim DB As Database
Dim SQLStr As String

Set DB = CurrentDb()

SQLStr = "Select * From TableName"

Set RS = DB.OpenRecordset(SQLStr)

If RS.EOF And RS.BOF Then
RS.MoveLast
TotalRows = RS.RecordCount
RS.MoveFirst
TempArray = RS.GetRows(TotalRows)
End If

and works fine all the time.. until one point when it starts lagging... like usually it itakes a couple of seconds to run the code.. but now it takes like 2-3 mins.. I have checked putting stops between the code to check where the problem is.. and starts lagging in one of the lines where I use:

Set RS = DB.OpenRecordset(SQLStr)

is there a limit of recordset I can make in a period of time? if so whats the limit? and how could I fix he problem to make it run smoothly...



Hope someone can help me.. I would really appreciatte it.. Thanks in Advance

Regards.

 

[Bodisathva]

try this slight modification and see if it helps...

Dim RS        As Recordset
Dim SQLStr    As String
Dim TempArray As Variant

SQLStr = "Select * From TableName"

Set RS = CurrentDB.OpenRecordset(SQLStr, dbOpenSnapshot)

With RS
     If .RecordCount > 0 'there are records in the recordset
          .MoveLast
          TempArray = .GetRows(.RecordCount)
     End If
End With

 

[tdubs]

try this slight modification and see if it helps...

Dim RS        As Recordset
Dim SQLStr    As String
Dim TempArray As Variant

SQLStr = "Select * From TableName"

Set RS = CurrentDB.OpenRecordset(SQLStr, dbOpenSnapshot)

With RS
     If .RecordCount > 0 'there are records in the recordset
          .MoveLast
          TempArray = .GetRows(.RecordCount)
     End If
End With

Thanks for the replay.. tried what you said.. didnt make any difference...
Thanks for help thou...

any other idea?

 

[david.brent]

Do you need to put the records into a temporary array .GetRows. Can't you just work with the recordset?

Your SQL statement could take a while

Select * From Tablename

as I'm not sure it be taking advantage of any indexes you might have on the table.

I used to use a auto index on all of my tables and when I needed to Select * I used to do something like this...

SELECT * FROM Tablename WHERE Auto_ind > 0

but it has drawbacks - adding records/updating can take a little longer.

It used to speed things up a bit.


I would probably do this though...

Dim RS as DAO.Recordset

Set RS = CurrentDB.OpenRecordset("Tablename")

With RS
.MoveFirst
'Step through recordset one record at a time
Do While not .eof

Whatever you need to do

.MoveNext 'very important.
Loop
End With

RS.Close

 

[tdubs]

The SQL Statement:

Select * From Tablename

was just an example.. usually my statements differ from each situation.

cuz basically what i am doing is comparing two tables and see what are the differences

for this one my statement is

SQLStr = "SELECT " & DBNameB & "." & KFieldB & ", " _
                            & DBNameB & "." & TagFieldB _
                 & " FROM " & DBNameB _
                & " WHERE " & DBNameB & "." & KFieldB & " NOT IN " _
              & " (SELECT " & DBNameA & "." & KFieldA _
                 & " FROM " & DBNameA & ")" _
                 & " AND " & DBNameB & "." & KFieldB & " <> '0'"

where DBNameB, DBNameA, KFieldB, KFieldA, TagFieldB..... are string variables. cuz it will be different depending on situation...

well I was playing around with my code and i think it is because my database is quite big (approx 9000 records per table, comparing 2 tables in one statement).... like when I change the tables to be compare to smaller ones... then it run quite fast... as well as using the dbsnapshot...
Althou..no matter what.. the RS.movelast line is quite slow...

I see that you guys don't use that command.. how come.. like I had problems with the 'RS.RecordCount' before. Sometimes it doesnt return me the right value.. thats why i always .movelast... get recordcount, then .movefirst and then .getrows, I use .movefirst to make sure i retrieve all the records...

I am not sure if what I am doing is rite... can anyone advise me on that???

thanks in advance...

 

[EMP]

NOT IN cannot be optimised by Access.

Try using a Left Join instead of Not In a subquery. (For SQL syntax, see a SQL statement created by the Find Unmatched Query Wizard.)

^

 

[tdubs]

NOT IN cannot be optimised by Access.

Try using a Left Join instead of Not In a subquery. (For SQL syntax, see a SQL statement created by the Find Unmatched Query Wizard.)

^

thanks... ill try that