Slow Link to SQL Back end (1 Viewer)

Running Access FE with SQL Server back end.

We have just added a second site based across the road, currently we only have a 10Mb link between the sites and we are experiencing front end hangs during text based wildcard searching on one field with approx 60000 records.

We are getting 32ms Avg Pings to the SQL Server (Not great )

I'm wondering if there is any mileage in looking at a Pass through query to cut down on data movement? or barking up wrong tree?

I would certainly try one to see if it helped. I use them a lot in a similar situation, along with stored procedures and ADO command objects.

Also recommend a Scalar Function
http://www.access-programmers.co.uk/forums/showthread.php?t=272547 - almost last post
This Scalar function looks at the last 3 characters in a field and uses the wildcard. Different types of function can take Argument(s) and return a value or a table.
A view using the Scalar Function (and over a dozen more) is shown here. The view can be set up as a linked table.
These run very fast.
Just another option if it fits.
RIGHT(Well_Name, 3) COLLATE LATIN1_GENERAL_BIN LIKE '%[HD]%'
The BINary option can also be a speed up in T-SQL.

You didn't show an example of the wildcard search.
Having the wildcard at the end of the string, like 'abc%', would help if that column were indexed, it allows seeking directly to the records which start with 'abc' and ignore everything else. Having the wild card at the beginning means it has to look at every row, regardless of indexing. So, the wildcard at beginning or end can make a big difference. A non-indexed field with the wildcard at the end will also be slow.

Another advantage of T-SQL (later versions) if the wild card is at the front-end is the CONTAINS function.
For a typical database, the CONTAINS search can be much faster assuming the appropriate full text search index is built on the field being searched.
In SQL Server Management Studio, right click on the field being search and choose the Full Text Search Index. Avoid doing this for evey text field and focus on the ones being searched.

Another option is to run the Access on Citrix or Remote Terminal. This way, the users use the ICA Client. The ICA is really tiny bandwidth. All process and network traffic take place on the server. My users in Utah or North Dakota have the same effictive respone as if they were on a terminal at the server. This has the advantage of letting you deploy a single Access project on the server. No Access need be installed on the user's. Access using linked tables on the same Server as the SQL Server is pretty much unlimited with virtually no ping time. It is just another option should too many users try to cram down a 10 MB network pipe.

Thanks for the responses, I'll have a play around tomorrow at work and also check the wildcard options. From memory its a partial serial number search, so is wildcarded both ends of the search string. Which probably isn't helping...

The wildcard search is probably the major culprit for slowness -- doesn't make use of any indexing. IIRC it has to read all records.

Okay - I was completely wrong about the wildcard - there isn't one (amazing how quickly you forget what code you've written or inherited), however it does search two different serial number fields. The second one of these fields wasn't indexed, so for a starter I've added that and rebuilt the other indexes.

I'm work on the pass through as well as at the moment if one or two users has the issue, it's only going to get worse if more are added at the second site... (Obviously there wasn't any initial requirement for DB access at the second site, only via our intranet, which isn't an issue, planning is great isn't it!)

update statistics as well.

Also consider using a covering index if the performance doesn't increase enough