Question Slow access 2003 search over ODBC SQL Server linked tables

[eligio]

Hello. I have an Access 2003 database with linked tables (via ODBC) to a SQL Server 2005 database. The application (Access) and the DB (SQL Server) are on different machines. The application (Access forms) works fine: opening, queries, etc. The problem is when you want to navigate to a record. When you search over a field (for example, customer name) by doing Ctrl+F, it takes about 30 seconds to find the record. Before, without using SQL Server, the database tables and the forms where on the same .mdb file, and the same search takes about 5 seconds. I have create indexes for the fields I usually search, but it continues being slowly. Are there any tunning tips for increasing the search speed? Thanks in advance.

 

[KenHigg]

Just a generic question: How many records are involved?

 

[eligio]

Hello,

Thanks for your reply.

Depends on the form, but for example, I'm testing a form that navigate through a table of 5,000 records.

 

[KenHigg]

That is a very small number of records. It doesn't sound like a db issue. Are you trying to hit the sql server over a LAN or a WAN?

 

[eligio]

That is a very small number of records. It doesn't sound like a db issue. Are you trying to hit the sql server over a LAN or a WAN?

Yes, that's why I cannot understand why it's so slow.

I can access (via LAN) to the SQL Server, and query with no problem.

The only time it seems to be slow is when trying to search doing Ctrl+F

 

[KenHigg]

I don't understand what you mean when you say you use a Ctrl+F to do thie search?

 

[eligio]

I don't understand what you mean when you say you use a Ctrl+F to do thie search?

Ok, one example: Customers form.
I have a form for adding/updating/deleting customers. Form display customer data. I have navigation buttons to move forward/backward.

If I want to navigate to the customer #1025 in order to update its mail address, I focus on Id field, press Ctrl+F and search for '1025' in that field. After a few seconds all data in the form is about customer 1025.

That's the search I'm talking about.

Sometimes they search by Id, sometimes by name, etc.

 

[KenHigg]

Hum... I haven't used that feature in Access. Maybe switching to a different method to find records would help.

 

[LPurvis]

I don't know about Ken, but personally, it's hard to speak with any authority on the use of the built in Find. (I use it maybe once a year - when I'm feeling lazy? lol I should point out that I feel lazy more than once a year ;-)

But the theory behind it will be analogous to how Access loads its datasheets (and searches them - and hence your form too).
There are various options on the Find dialog.
LookIn (for which Access just needs to be familiar with the table schema - it should have a local cache anyway - whether it chooses to use it or not is hard to say but I would assert that it does.
Match (simply the search type - exact, partial).
This is a factor to consider even when performing efficient searches.
This will determine the database's ability to use any index on the field in question.
Any part of field will scupper such ability.
Match Case and Search as Formatted further negate the potential for an efficient search.

However to come back to my initial point...
Access default functionality with ODBC sources attempts to offer efficiency by graduating requests.
So if you open a table - it'll load data for the visible rows (and generally what would be the visible number after that). It loads these not as a batch request from SQL Server but by requesting the PKs of those rows and then iteratively requesting those rows data.
I believe that Find is performing a searching equivalent - but for the speed you're describing it's almost certainly performing a local read of the table data.
5000 rows isn't many it's true. But you don't want that data all pulled locally for Jet to filter and Access display the matching result.
Performing iterative batch loads of all the data to locally query for a match.
(A quick peek at a SQL trace certainly seems to confirm this suspicion).

Unfortunately - the recommendation is what my knee jerk reaction would be anyway.
Don't use the built in Find. :-s
Build your own search form - based on a query of one type or another.
You mentioned yourself that you can query normally. Therein lies your workaround.

Cheers.

 

[MSAccessRookie]

Hello. I have an Access 2003 database with linked tables (via ODBC) to a SQL Server 2005 database. The application (Access) and the DB (SQL Server) are on different machines. The application (Access forms) works fine: opening, queries, etc. The problem is when you want to navigate to a record. When you search over a field (for example, customer name) by doing Ctrl+F, it takes about 30 seconds to find the record. Before, without using SQL Server, the database tables and the forms where on the same .mdb file, and the same search takes about 5 seconds. I have create indexes for the fields I usually search, but it continues being slowly. Are there any tunning tips for increasing the search speed? Thanks in advance.

You are having problems with the Microsoft Built-in search functions? I believe that I can address this subject for you because I think that I had the exact same issue in the late spring of this year. After over 2 weeks of somewhat painful research, it was determined by some experts that in my case, there was allegedly NOTHING that I could do about the Built in Search procedures.

I refused to believe that I could not find a Solution to the problem, and turned to Visual Basic. In my implementation, the users need to locate 1 of 40,000+ Invoices, and it was taking over 20 seconds to perform the search. Once the Visual Basic Solution was implemented, the search became nearly instantaneous.

I created a new field on the form for the search, and placed the code behind the "On Exit" Event for the field. Once the user has entered an invoice number, a clone of the recordset of the form is searched, and the master recordset is updated from the results.

I am sure that this code is very crude, but it was my initial attempt, it worked very quickly after developing it, and has not failed to date, so I have found no need to update it.

Feel free to see it it will do anything for you.

Private Sub NewInvoiceNumber_Exit(Cancel As Integer)
    Dim rst As Recordset
    If IsNull(Me.NewInvoiceNumber) Then
        ' If it is NULL, then no search is needed.
 
        GoTo Exit_NewInvoiceNumber_Exit
    Else
        Set rst = Forms!fmrWorkOrders.Form.RecordsetClone
 
        rst.FindFirst "[InvoiceNumber] = " & CStr(Me.NewInvoiceNumber)
 
        If Not rst.NoMatch Then
            Forms!fmrWorkOrders.Form.Bookmark = rst.Bookmark
        Else
            MsgBox CStr(Me.NewInvoiceNumber) & " Not Found!"
        End If
 
        rst.Close
        Set rst = Nothing
    End If
 
Exit_NewInvoiceNumber_Exit:
' Position the cursor on the Invoice Number Field
    Me.InvoiceNumber.SetFocus
 
End Sub