Database Information Storage

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I produced a database that is being fed with new information every week. So
rar it has more than 1 mln lines (records). and the queries are slwing down
as the table gets biger and biger. Is there any way to fix it...
 
Slow queries typically suggest your query is hunting for too much data
-- you need to refine your query to bring back only what you want.

There are some things to try:
1) Confirm that you have indexed the critical fields that you are
querying against, reduce and eliminate JOINS in the query.
2) refine your query so that it only pulls out a smaller record set.
For example, if the weekly new information is all your really care
about, add a record date component to your query and limit the query to
the last 8 days of data.
3) Create two queries -- one for the most often used recent data and a
second one for "all the data in the database" -- then train your users
when to use one vs.the other to answer their questions.
4) Using VB, create a record set that pulls back only the first, say,
1000 rows at a time. Display the first thousand and let the user
scroll the thousand (key to success is sort the record set in the most
likely order of use -- say, descending date order), then when the user
gets to row 950, have the recordset refreshed by purging records 1-800
and appending the second 1000 records. Etc.
5) If you DB is really going to be growing at a straight line forever,
you will (a) need to move off Access to an Enterprise RDBMS and (b)
redesign your DB, because even Oracle has physical limits on table
size, if you still want good performance.
 
Do you have indexes on the fields that you are using in searches and sorts?
Other than that there is not a lot you can do to speed up the data. Also, you
must realize that indexes represent a tradeoff - it takes longer to add, delete
and modify a record if indexes are involved. The indexes have to be updated at
the same time.

Other than that your choice is to archive some of the rows of data into a
separate table if it is not really needed.

One other thing that may help, is to compact your database. Doing this is (as I
recall) supposed to force the database to reexamine the query plan for all
queries and update them as appropriate.

Opening a query in design view and changing it, saving the change, and then
undoing the change and saving again, will also force the database engine to
reexamine the query plan and make optimizations to the query plan.
 
Back
Top