Performance of rst.findfirst code with SQL is poor

[FrankPV]

I use the following code to locate and display records on an access form. The data resides in SQL Server 2000. Performance of this screen is slow. Can anyone recommend improvements in this code to increase performance? Thanks.

Private Sub Form_Load()
Dim strCriteria As String
Dim rst As Recordset

' Initialize the recordset
Set rst = Me.RecordsetClone

' Set the flag that indicates the origin of movement through records
fFromListBox = False

' Create the criteria string then search for a record matching the criteria
strCriteria = "([SECQTY] = 0 OR IsNull([Secqty])) AND ([EDIQTY] = 0 OR IsNull([EDIQTY])) AND ([NAVQTY] = 0 OR IsNull([NAVQTY]))"
rst.FindFirst strCriteria

' If a match is found, move to that record and set the value of the list box
' to equal the ID of the record that matches the criteria. Otherwise,
' display a message
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Me!QtyList = Me!ItemID
Else
MsgBox "No undistributed orders found!", vbOKOnly + vbExclamation
Me!QtyList = Me!ItemID
End If

Me!QtyList.Requery

' Release memory claimed by the variable
Set rst = Nothing
End Sub

 

[cmmrfrds]

I am quite sure, that using the DAO findfirst method on a linked table requires a requery of the recordset from the server. So, even though you have already retrieved the recordset it will be retrieved again. Also, linked sql server tables use an ODBC driver which is an additional layer of overhead. To add insult to injury this will be another retrieve of the recordset from the server Me!QtyList.Requery. The main reason for all this is a server side cursor and can be elimiated by using an ADO recordset with a client side cursor.

Questions? What version of Access. How many rows in the recordset that is cloned. Can you show the sql statement that is the data source of the From.

 

[FrankPV]

Hello, and thanks for your help. I am using Access 2002 but the application was written in Access 2000 format and has not been upgraded to Access 2002. There are 30,000 records in the cloned dataset. The form consists of a main form which has a listbox linking to the main form. The user sees a list of items in the list box starting with the first record that is blank in the quantity fields. The user clicks on the list box and the record is displayed and updated in the main form. The sql statement for the main form is:

SELECT DISTINCTROW Invoice_Items.ItemID, Invoice_Items.DESCRIPTION, Invoice_Items.MODEL, Invoice_Items.COLOR, Invoice_Items.PRICE, Invoice_Items.QUANTITY, Invoice_Items.SECQTY, Invoice_Items.EDIQTY, Invoice_Items.NAVQTY, Invoices.RECDATE, Invoice_Items.DETAILS, Invoice_Items.NOTES
FROM Invoices INNER JOIN Invoice_Items ON Invoices.INVCNUM = Invoice_Items.INVCNUM
WHERE (((Invoices.RECDATE)>=Now()-300))
ORDER BY Invoice_Items.ItemID;

 

[cmmrfrds]

Since there are 30000 rows that can be returned to the client it is probably better to rethink the logic flow and throw out cloning with this many records.

Do you only need to select from the group of records that match this criteria?
strCriteria = "([SECQTY] = 0 OR IsNull([Secqty])) AND ([EDIQTY] = 0 OR IsNull([EDIQTY])) AND ([NAVQTY] = 0 OR IsNull([NAVQTY]))"
AND will this only retrieve a small subset of the records? How many?

Does the listbox contain some of the same records that are on the Form? It appears to be a subset of those records.

My thought is that it will be okay to requery the records for the listbox. Also, when the record is updated on the main form will it disappear from the listbox.

Another thought is since the listbox drives what record appears on the main form then only that record should be retrieved from the database - not necessary to retrieve a large set of records.

Please answer these questions first and we can go from there.

 

[FrankPV]

This form is used for two purposes but I can split the functions. The first is to allow the operator to distribute the items that haven't been done - this is what the following line is for.

strCriteria = "([SECQTY] = 0 OR IsNull([Secqty])) AND ([EDIQTY] = 0 OR IsNull([EDIQTY])) AND ([NAVQTY] = 0 OR IsNull([NAVQTY]))"

This move the file pointer to the first record that meets this criteria. If the form only showed the records that met this criteria it would be a subset of no more than 50 records. The listbox does contain the same records that are on the form.

The other use for the form is allowing the user to highlight a line in the list box which brings this record to the main form and change the distributed quantities. I could easily make another form to allow these changes and in fact as I think it through it makes sense to separate these functions.

Thanks again for your help.

 

[cmmrfrds]

It sounds like you have a plan, let us know how the performance was improved.

With SQL Server tables.
(1) avoid linking tables - you have found an example of what can happen.
(2) use pass through queries for record sources.
(3) use ADO recordsets where the recordset is not used as the data source of a Form or Report.
Better Yet.
Change to an Access Project which makes a direct connection to the SQL Server database and you can use stored procedures to keep the processing server side when it makes sense or ADO client side cursors where that makes sense.

If you run into any other problems or need examples please post back.

 

[FrankPV]

How would you recommend upgrading my application to an Access Project? Do you find it requires a lot of recoding?

Thanks again for your help.

 

[cmmrfrds]

Here are some links that should help to answer questions on what to look out for.

http://www.sqlmag.com/articles/index.cfm?ArticleID=6133

http://msdn.microsoft.com/library/d...00/html/acconChooseHowToUpsizeApplication.asp

Use the upsizing wizard.
Hopefully, you don't have spaces in field or table names since that is not allowed.
Queries will be converted to stored procedures or views and kept in sql server not the app.

Give it a try.