Search Faster

[Shirley]

I have a database with over 50,000 records. And the search process is taking to long. But as of yet I have not found any information on a random search. If you know how to make the find faster, please let me know.<br>

 

[bitbrain]

Are your search columns indexed? are you searching on the primary key?

 

[Shirley]

The columns are indexed with no dublication for the Id, and Indexed with duplication for LastName. The primary key is the ID. The combo box search for LastName and ID. The LastName is in Alphabatical order. But at the time it takes 3 minutes just to find one person.

 

[bitbrain]

Do other queries run okay? Sounds like your table is set-up right. Could it be hardware - memory, disk space, processor speed. Is the db on a local drive or on the network?

 

[Shirley]

All the other query run find, it is just that one table have over 120,000 record and the second have over 50,000. And I Conbined the two table on one form for the user to view the information not to edit anything.

 

[bitbrain]

Are you searching the result-set of the join? If so, that might explain the delay. When opening a query that is not sorted or summed, Access will start displaying results as it gets them - giving the ILLUSION that performance is fast. It could be that when you search on the result-set the entire query needs to finish before the search takes place (making a lot of assumptions here).<br>
<br>
Make sure that all your join columns are indexed.<br>
<br>
Assuming that your form is based on a query that joins two large tables AND you have a combo box that looks for a value in the join query result set, try the following:<br>
<br>
Make the value of the combo box part of the query's selection criteria. The form should come up empty when it is opened initially.<br>
<br>
Change the click event of the combo box to requery the form. When the query runs, it will select & join just the rows for the criteria - not the entire table.<br>
<br>
<br>
<br>
<br>

 

[Shirley]

I did not combine the two table in a query, but they are both link by the StudentID. On the Student Table the StudentID is the primary key, and on the Course Table the StudentId is the foreign key. On the form in question the Course Table was set up as a subform that is linked to the Student that is selected. These two table was developed in FoxPro and I am trying to convert the data to Access. So from the imported table I did a query for each table to combine a number of fields. For the query the form was developed.

 

[bitbrain]

I've used sub forms with good results. Again, make sure your forign key is indexed. Is the data native to Access i.e. was it imported? Or, is the database LINKED to the FoxPro database.<br>
<br>
Did you create the subform yourself or did you use the wizard?<br>
<br>
Make sure the "Link child" and "Link Master" field properties reference the correct fields.

 

[Shirley]

The table was inported but I formated the data and the input mask to make it easier for the user. I checked the indexe on both table on the Course Table the index is the StudentID and on the Student Table the index is StudentID primary key in ascending order and LastName yes duplicates OK. But I was wondering how do you set up a combo box as part of a query's selection criteria. I even combined the tables as you suggested but there are records on the Course Table that are not on the Student Table and due to that I was unable to enforce reference enterity, update or delete. I could only join the two table. And when I combined the two table there were a number of duplicated StudentID. I know how to hide duplication on the report but not exactly how on a form or queryu.

 

[Shirley]

Dear bitBrain<br>
<br>
I finally got the database to search faster after I reduced the search to one index the LastName. Now I will have to figure out how to select a record on the subform and update the main form. Thanks for you help bitbrain.

 

[ALA02]

I don't know if this is feasible for you Shirely but how about setting up an ODBC connection to a SQL server such as MS SQL server. All query's, report generation(all the work in general) is done on the SQL server and the data is sent back to the remote machine that requested the data...just another option...a more costly one But it should speed processing up by a tremendous amount.

 

[Shirley]

Thanks for the suggestion ALA02 but how much would it cost and what procedures would I have to do.

 

[ALA02]

Hello Shirley, at my currentl position I do a lot of work through an ODBC connection, it happens to be MS SQL server 7.0(I think thats the latest version). I know that through Access you must set up your ODBCConnectStr to set up your connection. However, I am not the administrator of the SQL server. I only see the access side of the "going ons". I would suggest using Access help files(actually quite helpful). Try keywords "pass through query's" and "sql". Also I would head on over to microsoft's website and do some research there under MS SQL Server. If your looking for speed and your company offers you some financial flexibility SQL server is the way to go. <br>
Also, speak with your network admin about setting up a SQL server, see what information he/she can give you. Hope this helps at least a little.

 

[elizabeth]

Have you tried just running your query without the form, using the selection criteria in the form? That can help you decide whether to eliminate the form itself as part of the performance problem. <br>
<br>
Are you using Access as both the front and back end? One of the reasons that client server products (like SQL Server as ALA02 suggested) are faster than Access as a back end is that the entire file doesn't have to be served to you over the network. You could try copying the tables to your local drive. If that makes a difference it could be network perfomance.<br>
<br>
BTW, 50K & 120K records sound like a lot for either the current number of students or courses (if I understand you correctly). If they aren't all current, you might consider archiving older records in a separate table or tables.

 

[Shirley]

That is what I did. I copied the data to my local drive, but and narrowed the search criteria to look for just Student Last Name which were in ascending order, instead of searching for StudentID and LastName. This shorten the search.