Data structure

[Phantazmo]

Folks,

I currently have a employee management database that stores employee hours. It is spread across two tables currently, timecard and timecarddetail. One-to-many relationship. Timecard detail can conceivably contain records-to-limitation number of records for each Timecard. Unfortunately, over the years, the database has grown dramatically, around 300000 records in the Timecarddetail table alone.

Now, whenever I query the data to obtain historical versus current performance, the queries are taking a heck of a long time.

My question is, should I redesign the timecard structure to be based upon inidividual employees (ie. store timecard data in tables for each employee), or continue with the same structure and move towards an enterprise level DBMS? (The current database is quite a bit more involved than straight time management, but the timecard data is one of the bigger performance issues).

Thanks in advance,

p.

 

[BNPMike]

It's quite likely the delay is caused by your query processing ie within Access rather than the retrieval from Jet. You can test this by writing a query that simply gets the data without actually doing any calculations on it ie the bare fields. And no calculation in the WHERE clause etc. If this is very fast you'll know it's not Jet.

Making queries run fast is one of the arts of IT. There are many organisations with truly gargantuan amounts of data, so all sorts of strategies are employed - partitioning, staging tables, index manipulation etc.

If you use a faster database, a faster server etc etc, it'll probably go faster but you can get greater magnitudes of improvement by other tuning approaches.

Post some more details here - SQL plus a description of the two tables involved. Are there just those two? How often do you compact? What indexes have you got?

 

[willir]

Phantazmo

(Cool handle)

Access is not really suited to large database if you were to use it the way that makes intuitive snes (huhh???)

When you design a form based on a table, Access will retrieve ALL records for the table. This is a pain because a) if the database is on a network, the entire table is copied across the LAN; b) you need space on yur local drive to view the information. A real double Yuck!

What to do.
- Make sure your tables are indexed in the way you retrieve data. For example, if the field EmployeeID is used to retrieve data, then make sure EmployeeID is indexed. (In table design mode, "View" -> "indexes")
- Minimize the data copied over the LAN. A possible no-brainer would be to only load the TimeCardDetail records for the specific header record, TimeCard. To do this, for the OnCurrent event, create the RecordSource and Requery the RecordSource for the TimeCardDetail table for the specific TimeCard master data being displayed.

I am going to assume you know a bit about coding since you have a database that has grown quite large. (Post back if you need more info).

strSQL = "SELECT * FROM TimeCardDetail WHERE ... = " & Me.Primary_Foreign_Key_Used_On_Time_Card_form
Me.TimeCardDetailForm.From.RecordSource = strSQL
Me.TimeCardDetailForm.Requery

You can make the record source even smaller by further restricting the number of records.

...And you can use the same approach for the TimeCard table. Be restrictive in your RecordSource for the TimeCard form. For example, retrieve only the current year and offer the option to retrieve past years through a GUI interface.

- Latly, you can archive your older data -- even to another table name within the database.

Richard

 

[SQLSister]

HOw often do you need the older timecard records? You could create archive tables (idential to the main tables except do not use any autonumber fields) to store those older than say a year and once a month copy the oldest records to the archive, then delete them from the active tables. Then create aform to access older records from the archive if you have some reason (such as a court case) to refer to them. This way the data is stillavailble for the rare instances it is needded but isn't clogging up the data you frequently access.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[Phantazmo]

It's quite likely the delay is caused by your query processing ie within Access rather than the retrieval from Jet. You can test this by writing a query that simply gets the data without actually doing any calculations on it ie the bare fields. And no calculation in the WHERE clause etc. If this is very fast you'll know it's not Jet.

Thank you BNPMike for the troubleshooting technique. The query in question would take the entire timecarddata table and was calculating Cost Of Employee by obtaining the Employee Rate, using a function which authenticates the user each and every time a line of cost was calculated. By generating a temporary table of rates first, then calculating the cost, reduced the time for the query to return the required data.

Are there just those two? How often do you compact? What indexes have you got

The database is really quite elaborate. I built it about 5 years ago for a machine shop. It tracks current production jobs, machine costs, generates machining quotes, purchase orders, delivery receipts, schedules machining jobs based opon real and determined values. It is something that I should probably be moving to a DBMS with a little more jam as the shop is starting to get huge.

p.

 

[Phantazmo]

(Cool handle)

Thank you. Its from a White Zombie song.

You can make the record source even smaller by further restricting the number of records.

Now, thats a really useful hint, and something I really didn't know. Particularly when the cost accountant is doing to indepth analysis of a specific project.

Thanks Willir.

 

[Phantazmo]

Thank you SQLsister,

You could create archive tables

I must have been reading your mind. I just finished that little project. I submitted all data greater than 2 years into the archive table.

p.

 

[willir]

Phantazmo

calculating Cost Of Employee by obtaining the Employee Rate, using a function which authenticates the user each and every time a line of cost was calculated. By generating a temporary table of rates first, then calculating the cost, reduced the time for the query to return the required data

A TEMP table is a good start. Are you able to use a small enough record set to use an array instead. Verify the employee once, and then work out the line costs. You may be able to achieve the correct level break by using an ORDER BY. If that does not work, perhaps stringing the EmployeeID numbers together and use the InStr function to test instead of reading from the table.

Although it breaks the rules of normalization (3rd form), it is sometimes more efficient to store the calculated value on the table. This works provided the value will not change, especially if there are a lot of calculations.

Richard

 

[Phantazmo]

Hi Willir,

Unfortunately since there is more than one person entering the timecard data, there is almost always an entry error or two per week which don't get resolved until after the fact, a regeneration the calculated values would have to take place. I suppose creating a function or macro that regenerates the calculated table after an timecard entry change has been made could be a possible solution.

Thanks again Willir.

p.