.FindFirst too slow

[NorthNone]

We implemented last Monday and the following search is too slow for our customer:
'----------------------
Set rst = Me.RecordsetClone
rst.FindFirst [PKey] = 1111111111
If rst.NoMatch Then
MsgBox "No entry found.", vbInformation
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
'-------------------------
The form's records are in order by lastname,firstname so I wanted to use SEEK, but I split the database before implementation and it won't let me use it. When searching by last name, the above code works speedily, but is SLOW on the network with 25,000 records. There is an index on PKey in the table. The recordset is a query ordered by lastname/firstname.
Can I write a back-end vba module to do the seek? But then how would I get the form to go to that record?
BTW the above code is used to find by last name (with slight modifications) and it works fine. Should I take my PKey, pass it to backend VBA with a SEEK, find the LastName, FirstName, pass it back to the form and have it search .FindFirst that way?
HELP!!!
Stressed out in Saint Louis and wondering why I got into this line of work!

 

[KenReay]

Hi

Quite a lot of work, but you could drop the linked tables and use OPENDATABASE to give you access to the backed mdb, this would allow you to use SEEK

However I am puzzled by the fact this is deemed "too slow", 25000 records is not a lot, I think I would be questioning why it is slow, before making drastic changes

Which Version of Acces are you using?

Have you read the tips on the Microsoft MSDN site for improving performance ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[VBslammer]

I wouldn't populate a form with 25,000 records in the first place. It would be much more scalable to ask the user to provide the search key info and use a query to return only the records that match.

You could filter the recordset:

Private Sub cmdToggleFilter_Click()
  If cmdToggleFilter.Value = True Then
    Me.Filter = "[Company]='" & [Company] & "'"
    Me.FilterOn = True
  Else
    Me.FilterOn = False
  End If
End Sub

VBSlammer

Unemployed in Houston, Texas

 

[NorthNone]

Thanks for the quick reply, Ken.
**If I use OPENDATABASE how do I move (on the form) to the record I find? Right now I use Me.Bookmark = rst.Bookmark. Also I have user-level security implemented on front-end and back-end - how do I pass a password?
** I had great speed locally, but when we implemented and put it out on the network with countless other people demanding resources from the server in other programs the performance decreased dramatically.
**XP
**Yes, but not since implementing when my life turned to !#$#(!#@(*!)(
Thanks again.
JeanS

 

[KenReay]

Hi

OK, so the problem is that the system is heavily loaded, I would first experiment with VBSlammer's suggestion of reducing the size of the populating recordset, by seelcting a smaller set of records. However since Access is a File Server based product and not true client server, even seelcting a subset from a table, will put traffic on the network as all records must be brought to the local PC to apply the filter.

But the key thing is (in my opinion) you need to establish where the bottleneck is and then work around it.

I would gamble that the bottleneck is the Network (assuming local PC has decent amount of RAM and reasonable Processor speed).



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[NorthNone]

Thanks. They have robust computers so it is indeed the network. We're working to get them faster links to it which will help some, but I think you are right - we need to cut the size of the recordset and archive records that are not current somehow.
Thanks for all your help!
JSouthall

 

[MichaelRed]

To reduce network traffic w/ Ms. A. you need to do some (relatively) drastic steps. If the number of users is 'large', you need to move toward one of the "INDUSTRIAL strength" database engines. One step, slightly less painful than the full blown db Engine, might be MSDE, which IS Ms. SQL Server, but without the Management Console (which is where you get the nice tool set with T-Sql). Using even the less than complete MSDE, you can however generate all of your recordsets on the server side, reducing the net traffic. Also, many of hte 'db' calculations can be done within the MSDE with stored procedures, further reducing the traffic and the workstation load. These changes moving the work to the "Server", thus possibly requiring the app to be placed on a seperate machine and other network changes.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[NorthNone]

MichaelRed, how much rewrite of VBA code is required to go to MSDE or to true SQL?
TIA
JeanS

 

[MichaelRed]

Somewhat difficult to say. I think some of hte major factors are in the db 'structure' or design. If you go to SQL Server, Ms. Provides a tool (Upsizing?) which helps convert the tables, and (I think) does some of the queries (not XTabs). If you do not generate a lot of SQL strings this should cover quite a bit of the changes. A large part of the advantage, however, probably comes from the use of more complex stored procedures, which need to be done with some insight -but which can also be introduced after the basic process is converted.

Obviously, conversions always have the challenge of some degree of difference in the capabilities of the two processes, and these, in turn, depend on other factors, so I, at least, do not think I can give much more in terms of 'guidlines', but think that for any large scale multiuser / networked app, the multitier model will provide a distinct advantage over a monolithic version.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Ed2020]

Build an SQL string that will return the record(s) that you want and open up a recordset based on the SQL statement:

strSQL="SELECT * FROM TableName WHERE [PKey]=" & 1111111111 & ";"


Set rst = CurrentDB.OpenRecordset(strSQL)

If rst.RecordCount=0 Then
MsgBox "No entry found.", vbInformation
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing

Should work and should be a *lot* faster.

Ed Metcalfe.

Please do not feed the trolls.....