Odbc linked tables - search slow.

Hi,

In my accDB with tables linked to sql server I have a form where recordsource is a table with +60K records. When I use access "build in search" it is very slow (+60sec). In ADP the result is < 3sec.

Is ODBC/Linked tables not using table indexes, is there any way to speed up search?

I have tried creating custom vba search as below, its working but is really slow too.

Set rs = frm.Recordset.Clone
rs.FindFirst "[" & strSearchField & "] = '" & strSearchFor & "'"
frm.Bookmark = rs.Bookmark

Also when searching directly in tables linked tables are much slower than adp. Is this by design or could I be doing something wrong?

Thanks,
Jakob
edaj6Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
FindFirst is a slow as you can get. A simple SELECT statement to find a record would be much much faster.

Comparing an accdb with an adp is not wise. They are two very different beasts. The tables you see in an adp are actually SQL Server tables and you are interacting directly with the server (think of it as a windows directly into SQL Server). In an accdb, you have linked tables and the data is "mirrored" into your local tables and so you 60K+ records have to travel from the SQL Server to your PC - they do not in the adp. An adp uses OLEDB to connect, whereas an accdb uses ODBC.

Your example above needs for Access to load ALL records from your table into the PC, then pass through them one by one to find the first match.

Kelvin
0
edaj6Author Commented:
Thanks,

But how should the select statement be used, is there a fast way to move to a record if I know the key value? - I dont want to filter the recordsource, since users need to move to records before/after the match
0
Kelvin SparksCommented:
It depends what you are trying to do with the record. Why are you searching for it and what will you do with it when you find it?

The answers to that will determine how you search.


Kelvin
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

edaj6Author Commented:
I want to be able to check if record exist and if it does I should be able to edit, view and delete record.

Just as access build in find.

Jakob
0
Kelvin SparksCommented:
OK,
Have a form that is bound to the table containing the records you're checking (you may want to drop the binding later). In the form header place a text box where you'll enter the ID (primary key or other unique field) that you want to search for. In the After Update event of that text box use something like

Dim sSQL as String

sSQL = "SELECT * FROM MyTable WHERE SearchFeild = '" & me.txtsearchbox & "'"

Me.RecordSource = sSQL
Me.Requery


What this does is build a select string that selects the record from your table that you're looking for, it them sets the form's recordsource to that SQL and then requires the form. This will then bind the form to just that record. You might want to put some form of handler in to deal with non existent records. This will return the record in less than a second.

Kelvin
0
Kelvin SparksCommented:
I'm heading home for the night (just coming up to 10 pm here). Be back at 6.30 tomorrow morning and will look for any feedback then.

Good luck

Kelvin
0
edaj6Author Commented:
Thanks but this is a filter on the record source. I need to select the record searched for. Also, I would like to have the search code in a seperate form that can be called from multiple forms.

I have found that gotoRecord is really fast. I will try and find a solution with this, something like:

rs.open (select row, id from (select ROW_NUMBER ( ) OVER (..
DoCmd.GoToRecord , , acGoTo, rs.row
0
Gustav BrockCIOCommented:
Using Clone is not fast. Use RecordsetClone:

Set rs = frm.RecordsetClone

/gustav
0
edaj6Author Commented:
Clone is not a problem,

Set rs = frm.Recordset.Clone  < 1sec
Set rs = frm.RecordsetClone < 1 sec
FindFirst "[ID] =" & intID  > 60sec

Jakob
0
Kelvin SparksCommented:
If you want to stay with loading the entire table into an accdb and walking back and forth through it, then you have to accept the performance issues you experience. To resolve this needs you to put that approach aside and think creatively. Goto a row still requires you to walk through the table to determine a row number, so you won't get a performance gain.

The approach I suggested above is what I use on every form for viewing and editing data. Loading the entire table and moving through the recordset clone maybe fine for local Access tables, but needs to be replaced for any large tables, or ones that are linked.

I recall reading, although I've not been able to find that article again, that ODBC is slow and only delivers about 1,000 rows per second ( a slow network could make that even slower).

Kelvin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
edaj6Author Commented:
Kelvin, thanks for your comments.

I may use some of your ideas, but for now I have made a custom search form. Its working and gets result in < 0.5 sec

Function GetRow():
strSql = "Select TOP 1 row from " & _
        "(select ROW_NUMBER ( ) OVER (" & strOrderBy & ") as row, " & strSqlSelect & ")qry where " & strSearchField & " like '%" & strFind & "%' AND row > " & lngRowStart

Form:
DoCmd.GoToRecord acDataForm, frm.Name, acGoTo, GetRow

Thanks,
Jakob
0
Kelvin SparksCommented:
Hi Jakob

Ina round about sort of way, your approach and mine have some similaritys. The important thing is that you've dropped that FindFirst which was killing your performance.


Kelvin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.