[Urgent help needed]......Search slow on Access front end

[jy43]

I have recently migrated from access to SQL server 2005 using the specific migration tool downloaded from MS. Our orgnisation has at most 1-5 concurrent users and the database is relatively small in size (8M after compact).Every user keeps a local copy of the Access GUI running querys and forms while the actrual data is holding on a back end server sitting in another room.

The problems we met are:

1 Search(Ctrl-F) on "company" names and "people" takes 1-5 mins (untick "the search fields as formatted" box SOMETIMES makes the search instant)
This problem seems to impact on random user. e.g. users sitting in the same room may have different performance at the same time and the situation may reverse the day after. Reopen the Access front end or restarting the SQL server somestimes solve this problem but sometimes not.
All queries other than search looks fine like opening large tables and filtering.

2 A particular tables stoped me from inserting under form view (no [Star]button allowed for insert in the navigation bar) but you can insert by unhiding the underlying table and insert directly.No serurity settings applied on that table.All form settings checked. All the other tables works fine.

3 Some linked table occasionally freeze and prevent people from editing while it all looks OK in the last second.I have to manualy delete and relink the table to make it work again. linked table manager didn't help this time.

anyone has any ideas ??????

 

[SQL_Hell]

Test all operations at sql server end (write the queries in management studio) and see if you have the same bad performance. then you will be able to see whether access or sql server is causing the problem

There has be something wrong here because 8mg is tiny for sql server which can handle databases that are 100s of GBs big.

 

[tehNellie]

Search(Ctrl-F) on "company" names and "people" takes 1-5 mins (untick "the search fields as formatted" box SOMETIMES makes the search instant)

Suggests you're using a linked table into the access front end?

This problem seems to impact on random user. e.g. users sitting in the same room may have different performance at the same time and the situation may reverse the day after.

eeek! I've just linked a table into Access from my server, run a profile, opened the table in access and used the "find" function on the table to get my record. using this, you search each individual record in your table for the string in the field required. On a 44,000 record table with only me using that DB it takes over a minute to return the results. If you've got several people doing this I'm not surprised everything is grinding to a halt, you're hammering the server using find in table view. There's also a lot of extra "preparation" work involved at the server end preparing to undertake the search which adds to the time involved.

Use a query! Preferably a passthrough query which has slightly lower overheads and returns the values that much quicker. Or from a SQL server point of view, call a stored procedure.

Using Find is hammering your server/network and as far as I can see the reason why some people get their records immediately and others have to wait ages depends largely on where the record is in the table if it sits towards the "top" of the table they'll get to it quickly simply because the database is having to trudge through fewer records.

 

[boblarson]

Use stored procedures and views in SQL Server whenever possible so that the server does the work for you and only needs to pass you the results.