Moved Data to SQL Server - Now Slow

[trevorwilliams2]

I am in the process of migrating my tables to SQL server 2008.
I recently moved my contacts data to SQL Server.

I have a contacts menu with about 13k records.
The record source for this menu is simply the contacts table.
(the table is actually a view filtered by client name)

Common use for searching in this menu is to search in field, for example a last name by clicking in the text box, Ctrl F...

Access would return a record in approx 3 seconds.
With SQL Server searches now take 20 seconds.

I have tried adding SQL Indexes to FirstName, LastName, but no difference in performance.

Does anybody have any suggestions as to how I might speed things up?

Thanks

 

[dhookom]

I expect the contacts menu is read-only? If so, consider using a pass-through query to the SQL Server data. You can change the SQL property of this query with a little DAO code to filter the list.

Duane
Hook'D on Access
MS Access MVP

 

[trevorwilliams2]

Unfortunatly I need to be able to edit...will not work with a pass-through.

 

[trevorwilliams2]

One note: The speed of the searches works great with the use of a combo box. Folks in the office have have been programmed for years to use Ctl F to search since long before I was here.

That being, I know there is going to be a lot resistence and the question: "if its on sql server how come it's so slow?"

I think the problems lays with the Access (2003) built in search function. That is the beast that I trying to slay right now...

 

[dhookom]

You only need to be able to edit a single record at a time. Can you use the pass-through to find the record and then only have an edit form with a single record in its record source?

Duane
Hook'D on Access
MS Access MVP

 

[bubba100]

I have noticed when Access links to SQL tables manual searching takes much longer than with a query. I have no experience with SQL 2008. I would then suggest that you use a query. You could set the shortcut CtlF to open your own small form that "looks" very similar but uses a query to find your record.

 

[MrDataGuy]

Something does not seem right to me here.
~13,000 records and 3 or 20 seconds to find a match??
I would expect matchings being found in <<1 second, maybe 0.01 seconds.

Oh wait "the table is actually a view filtered by client name" ok Filtered Views now I understand why it is slow (Filter Views are commonly slow, or they were years ago and I never used them since.)

I suggest that something other then a view be used. Myself I would do a SQL Command, but there are other (and maybe better) solutions.

Bottom line for me is that even 3 seconds is 100s if not 1000s of times too slow for a 'search' in 13K of records (even with no index).

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[trevorwilliams2]

I give it a try with the raw table vice the view...

 

[trevorwilliams2]

same results....this has to be a flaw with the access built in search function. (Ctrl F)

As a work-around I am building a small popup form with a combo that bookmarks my contacts form.

For instance a search in the company field brings up the popup with a combo for all of the companies and then in AfterUpdate the bookmark is sent to the contacts form.

Dont know if is going to work...

 

[trevorwilliams2]

Update:

Using a bookmark works, I recommend staying away from the Access built in search functionality (Ctrl F) when working with a SQL back end.

There might be another answer to this but the above work-around seems to work.