Big tables (1 Viewer)

[Shas91]

Hi I have a question regardig big databases. I have worked with mine since 2008 and there is a lots of data stored and that ofcourse takes time to handle. I want to devide this tables in two. One part that just include the last three years and one for the rest. 99% of the time You reach the data You need within this three Years.

Is there any suggestion how to do this...

My thought is to dived tableBig in two... Table_1 that includes all data older than three Years and table_2 they rest. Table_2 is ofcourse the worktable where all new data is stored...

A query combines the table_2 with table_3 if You want to reach al data ....

But perhaeps ther is a better way ?

 

[Ranman256]

We keep 2 years, all other data is moved to: tData_Archive

 

[The_Doc_Man]

What you describe (split table by date, search recent data first) might help. It is not an unreasonable approach.

BUT the bigger question is, when you do these searches, do the tables have indexes on the fields in question? Having indexes will improve the speed of any searches. The only "ugly" search is one that has to visit every record because there is no index. (This is called a "relation scan" and is to be abhorred.)

For a search for a single value in a single field through a table of size N records and the comparison for a single record is time T:

Without an index, estimated search time is ( N/2 ) * T

WITH an index, estimated search time value is Log(base2)(N) * T

If you have a table of 32768 entries, No index = 16384*T; with index is 15 * T, which is about 1000 times faster.

Note that other factors can make the "with index" formula slightly more complex, such as having a non-unique value in the search or having other fields participating in the search.

 

[arnelgp]

doc, N is defined and a constant 2, what is the value of T, when there is/ there is not an Index?
i am always entertained by your ecclesiastic explanation!

 

[NauticalGent]

I've always avoided archiving data, especially if you ever need to use it again. Are your tables normalized properly? Docs advice about indexing is spot on, it makes a HUGE difference in performance. How many records are we talking about?

 

[The_Doc_Man]

doc, N is defined and a constant 2, what is the value of T, when there is/ there is not an Index?
i am always entertained by your ecclesiastic explanation!

T is the amount of time that the computer takes to compare two arbitrary strings to see if they match. It is a function of the string size. But more important, this component of the operation would be the same whether comparing a target value vs. an index value, or a target value vs. a table value. I.e. how long to do a single comparison. And this part depends on string size, memory speed, CPU speed, and perhaps a few other factors.

T should be essentially constant for a given search case, but will vary based on the size of the fields in question.

Stated another way, the speed of the search can be broken down according to the number of comparisons (N) times the time for a single comparison - and the latter is T. Does that help, Arnel?

 

[Shas91]

Well I need to store this data so just get rid of it is no option. If some one for an example clams a warranty I need this to ensure that everything’s is in order...

My skill of programming during the Years have of course improved but this main table is one of the very first I did. Unfortunately its to much data in this file... Today I have construct it in another way but change it now should cost a enormous amount of work.

Index... I dont think I have, I need to check this out more. What I do now is that the program speeds up drastic if I reduce the data in two tables

split table by date, and search recent data first seems like a god Idea...

I will do some test between the work and I'm keep You update... now its time to go home

// Shas

 

[gemma-the-husky]

if you have queries that can't use indexes - say they evaluate whether to include data based on a status code, and other things as well - then every row might need to be checked to decide whether to include that row or not.

Sometimes you can see that access ought to run the query in a certain order so that it works faster, but you can't persuade it to that .... in those cases, purging /archiving some old data is likely to produce a speed improvement. If all the fields are indexed, the improvement may not be so marked. Access may already be managing the data efficiently. Access can manage thousands, hundreds of thousands of records very quickly, providing it can use the indexes in an efficient way.

@Shas91
In your case your queries will use active tables. If you copy data into an inactive table (or even into the same named table in a different database) then in order to use the inactive table, you either need to duplicate every affected query to have a version that uses the inactive data - or you can re-attach the tables so that the table being used is from the old data. When you do this, you and your staff need to remember that they are using archive data, and they don't inadvertently process it as they would live data. In practice it might not be that easy to use archived data.

 

[Isaac]

I've worked in companies that did archive some medical claim data, when it was to a certain 'age'. I don't even know if 'archive' is the right word, but they segregated it - data < a certain year was on one SQL Server, data > a certain year was on another one.

If you wanted to create an interface for a user with the option to search older data, or a report as such, then you unioned as appropriate... else you didn't.

I think it's a reasonable approach if your data is pushing Access's limits, but I agree with everyone else.....Check, first, to be sure you're not choosing this solution only because you didn't have good indexes to begin with, which might alleviate the problem and allow you to continue offering the most robust possible availability to your end users. Doc is right about table scans, to be avoided. I like "abhorred". Sheer loathing!!