Performance Issue Using FindFirst with Access tables vs.SQL tables

[Netooi]

Hello, I have a question dealing with performance problems when using the VB FindFirst command. When I use this command on tables in Access I have around 3x faster performance than when i use the FindFirst command on SQL attached tables. I have noticed significant improvement in times when opening recordsets as snapshots vs dynasets, but still have the same performance problem... Its more like 5 or more times faster on dynasets than snapshots.

My problem is that in a database i'm working on there are quite a few times that I call FindFirst on some SQL attached tables and i need to use dynasets.. I need to know if there is a better way to retrieve the records from SQL attached tables and a short explantion of how to do so, and I would also be grateful if anyone could explain the reason behind the performance difference...
Thanks,
Netooi

 

[Rolliee]

I would do this with code.

with db as the currentdb()

SQL = "SELECT * FROM MyTable WHERE ..... ORDER BY .....;"

set rs = db.openrecordset(SQL)


if rs.eof and rs.bof then exit sub ' no hits
rs.movelast: rs.movefirst

do while not rs.eof
if rs![MyField] = myCheck then exit loop
rs.movefirst
loop
if if rs![MyField] = myCheck then
.......' do yout thing

endif

rs.close
db.close

all the other good stuff to end subby

rollie@bwsys.net

 

[cmmrfrds]

Are you using Access 2000 as the front end, if so there are some ways to improve performance - that is the first question.

 

[Netooi]

No, i'm using Access '97 if you have any suggestions for it... I havent had time yet to play with the first reply but I'll try it too and see if its any improvement and i'll let you know what happens....

THanks,
Netooi

 

[cmmrfrds]

The "find" can be very inefficient some drivers require a reread from the database - it will go out and retrieve the data from sql server on each "find". There are alternatives.

Some more questions.

Are you coding your recordset manipulation in VBA?

How many records are you typically returning from SQL Server, and are you only returning the fields you need?

Are you joining SQL Server tables to Access Local tables?

Are you familiar with ADO? If not are you willing to learn?

What is your PC OS? Win 98, ME, Windows 2000?

 

[Netooi]

1. The coding is being done in event procedures on the Access forms using Visual Basic..

2. I'm not sure on the number of records.. I'm working with someone on this project and they asked me to research this problem.. I'm guessing a couple thousand records max.. And i need to check with them to make sure that they are only getting the records they need, but i'm pretty sure that is already taken care of.

3. I dont believe there is any SQL tables joining with Access tables..

4. I'm not that familiar with ADO, but if it provides a good fairly easy to implement solution i'm very willing to learn...

5. And Windows 2000...

The FindFirst command is used in many places throughout the database i'm working on and in some cases it works fine... there are some cases though where its joined to SQL linked tables where its really slow and i'm trying to see if there are any alternative ways of doing those. Thats why the solution needs to be somewhat easy to implement, cause it will have to be done in a number of places.

Thanks for whatever help you can give..
Netooi

 

[PowerfulStar]

Greetings,

I have a somewhat similar problem with performance issues; doesn't matter how optimised my queries are, the deciding factor is the NETWORK! I tried opening an Access table with only 200 records on a remote site and it took quite a few seconds (as opposed to opening a local Access table with 115,000 recors which took a blink of an eye).

Thought you might like to know.

Regards,


PowerfulStar

 

[cmmrfrds]

What is probably happening is that the findfirst does a reread of the database to position at the correct record. This can be avoided by using a client side cursor and the filter method in ADO. Another thing you can do with ADO is get rid of the linked tables which uses ODBC an is an extra layer that can impact performance. You will not need the linked sql server tables unless you are joining them to a local access table. It is better (faster, efficient) to read the sql server tables directly with ADO and the OLEDB provider. I will explain how to build an ADO connection string in this post let me know if you want to proceed.

First, you will need a reference to the ADO library checked. Probably. Microsoft ActiveX data objects 2.6 library

Next, setup a connection string.
Dim connString as String

Build the connection string and test with the UDL Wizard.
Here is how you invoke the udl wizard. Do this on your desktop.

1. create a blank notepad file.
2. save the file.
3. rename the file by adding a new extention of .udl
make sure you save as all files not txt or some other
file type.
4. the icon for the file should change from notepad to a
little computer - it has now become the wizard.
5. double click on the file and it will bring up a dialog
box with multipule tabs.
6. use the microsoft SQL Server provider
7. look under the ALL tab and there will be parameter settings which you can change if necessary. There is a test
button, press to test the connection.
8. close the file.
9. open the file from notepad instead of double clicking the icon. You will see the connection string which you can copy and paste into your program.

Now, that you have the connection string. You can use the string to establish a connection to sql server.

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
'--- EXAMPLE
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

-- The next steps are to work with the recordset which we can cover in another post if you want to continue.

 

[cmmrfrds]

Tek-tips use the letters L O L to make a laughing face, that is why you see it in the connection string.

Example of using an Client Side ADO Recordset with Filter.

Dim cn As New ADODB.Connection, sql1 As String
Dim rs As ADODB.Recordset, connString As String
Set rs = New ADODB.Recordset

connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"

'-- set cursor location
rs.CursorLocation = adUseClient

sql1 = "select * from dbo.Employees "
rs.Open sql1, connString, adOpenStatic, adLockOptimistic
Debug.Print rs.RecordCount
Debug.Print rs.EOF
If rs.EOF = True Then
'no records returned exit
Exit Sub or Function
End If

Example of a filter which replaces the FindFirst method. These are fields returned on the recordset on the data you asked for.
rs.Filter = "lastName = 'Johnson' and firstName = 'Sam' "
'Count the records found
Debug.Print rs.RecordCount
'Turn off the filter
rs.Filter = adFilterNone

Still not fast enough, setup a client side index on lastName
rs("lastName&quot.Properties("OPTIMIZE&quot = True
rs.Filter = "lastName = 'Jones' "
Debug.Print rs.RecordCount
'Turn off the filter
rs.Filter = adFilterNone

Client Side sorting - default is ascending.
rs.Sort = "lastName DESC"

 

[Netooi]

Could something like this be done with ADO?
Also, i'm using Access 97 and someone told me you cant do ADO with '97.. Is that true or not?


Dim db As Database
Dim rsNames As Recordset
Dim rsCompleted As Recordset
Set db = CurrentDb

Set rsNames = db.OpenRecordset("tblNames", dbOpenSnapshot)
Set rsCompleted = db.OpenRecordset("dbo_tblCompletedNames", dbopensnapshot) 'SQL attached table

Do Until rsNames.EOF Or intCounter = 150

criteriaNames = "Name = " & "'" & rsNames![Name] & "'" & _ " and Number = " & "'" & rsNames![Number] & "'"

rsCompleted.FindFirst criteriaNames


If rsCompleted.NoMatch = False Then


intCounter = intCounter + 1
Forms!form1!lbl1.Caption = intCounter
Forms!form1.Repaint
End If


rsNames.MoveNext

 

[Netooi]

Dont know if i made this clear or not....
tblNames is an Access table,
tblCompletedNames is a sql attached table

Netooi

 

[cmmrfrds]

You can use ADO with a lot of programs/products including Access 97. What you need is the MDAC libraries and they come built in on the Windows 2000 OS. On older OS's like Win 98 you may need to download (free) from Microsoft.

You will need a reference to
Microsoft ActiveX Data Objects 2.6 Library
The number on your PC maybe 2.1 or 2.5 but pick the latest number.

'--------- Your example - there may be typing errors.
'- I am going with your example, but I would setup a query
'- on the Completed table to only bring back the records
'- you need. It would be a slight change to the Open syntax.
'- Also, I would make the query direct to the database and
'- get rid of the ODBC link - see previous post.

Dim cn As New ADODB.Connection
Dim rsNames As New ADODB.Recordset
Dim rsCompleted As New ADODB.Recordset

'-- set connection to current project
Set cn = CurrentProject.Connection

'-- set cursor location
rsNames.CursorLocation = adUseClient
rsCompleted.CursorLocation = adUseClient

'-- Get names
rsNames.Open "tblNames", cn, adOpenStatic, adOpenUnspecified, adCmdTable

'-- Get Completed
rsCompleted.Open "dbo_tblCompletedNames", cn, adOpenStatic, adOpenUnspecified, adCmdTable

'-Check for eof on both tables

Do Until rsNames.EOF Or intCounter = 150

rsCompleted.Filter = "Name = " & "'" & rsNames![Name] & "'" & _ " and Number = " & "'" & rsNames![Number] & "'"

'- check for any found
If rsCompleted.RecordCount > 0 Then
'--- found record(s)
intCounter = intCounter + 1
Forms!form1!lbl1.Caption = intCounter
Forms!form1.Repaint
End If

rsNames.MoveNext

'--- when done with loop set filter off and full recordset will be available and you can refilter as needed.
rsCompleted.Filter = adFilterNone

 

[Netooi]

Sorry its been a couple days for a reply...

Question about the line "CurrentProject.Connection"
It doesnt recognize CurrentProject as a valid object for me.. Do i need to include something for it?

Also one other question... Is ADO still a good solution if in my example earlier... tblCompletedNames had 100000 records or so.

 

[cmmrfrds]

You will need a reference to the ADO library called.
Microsoft ActiveX Data Objects 2.6 Library

The solution I suggested would be okay for a few thousand records, but not in the case of 100k records.

If you have a 100k records, then you probably don't want to bring them all back to the client side. Your best bet is to use stored procedures and do all the processing server side.

An alternative is to do a query inside the name loop to the completed table. Format a query to check the completed table. Your criteria would be the where clause for the query. This should work fine and be relatively fast for the 150 name records you are going through.

dim sql as string, rsCompleted As New ADODB.Record
psuedo code for loop.
sql = "select etc... where " & where criteria
rsCompleted.Open sql, cn, adOpenStatic, adOpenUnspecified
if rsCompleted.EOF = True Then
no records
else
deal with the records
end if