MSAccess Search Very Slow when connecting to SQL server

[level1]

I m using MS Access 2003 and using the upsizing wizard i have migrated my database to SQL server 2000. Every time I use the MS Access's default search to search the recordsets of my database for a particular value on a large (10.000 recs) table, the search facility becomes very slow. I have created an SQL trace to see what the problem is and I have found that there is a substantial overhead that has been created on the SQL server when performing the search. My Trace produced 2691 records of transactions for this single search for a field. The search also uses a lot of bandwidth. Any help how to overcome this plese?

 

[PLiNk]

I am not sure this will be of great help, I am quite new to this myself. It sounds as though you know more than me.
The problem I have experienced is that the whole database i transferred to the localhost, and then the query is run. If this is the problem, you must use a pass-through.-query which will run the query on the server and then transmit the results to you.
I do not know anything about the migration wizard so i cannot tell you if this is the case.

(I have received so much help on this board I am really trying to pay back .-

 

[smedvid]

I agree with PLink - Pass-Through Query will greatly improve speed. You may also want to review your query WHERE conditions to insure that the table(s) being accessed have proper indexes to improve query response time.

Another remote possibility is that there is a network latency issue, too many hops. I did find this to be the case once when I moved my backend from one SQL Server machine to another in another building. It was almost double in response... Turns out that the network topology/routers were bouncing all over the place on the new server. htwh,

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[level1]

The problem is that I am not using any custom query for this. I am talking about the MS Access default Search facility (i.e Ctrl+F). The common office popup window that is used for all office apps for either Find or Replace. Is there any way to make this work fast instead of having to create a Custom one? ?I dont thing I am the only one having this problem? Am I? Does your default MS Access search works fine for searching through Datasheets in MS Access?

 

[PLiNk]

I never use it though. I am not entirely sure, but if you are using the standard searcg facility you must run a query (receiving all data on the server is still a query) on the server and have that transmitted, and then search thorugh this with ctrl+f. Since this is bound to take up a lot of bandwith space i wil suggest you create a pass through query with that uses the Like(*&[Searchbox]&* and abandon the searching method ni ctrl f since that can only search in data on your screen, getting all that data is heavy, and hence your speed problem.