This is a real doozy...

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

Guest

My company uses a tool called Primavera as a project scheduling/project
management tool. The software has a sqlserver back end and in that db there
is a table that contains records for every person who is assigend to a
project, what task they are assigned to and how many hours they will spend
over the life of each task to which they are assigned. It looks essentially
like this:

tblTaskRsrc

TaskRsrcID RsrcID ProjID TaskID BegDate EndDate Hours
TR1 R1 P1 T1 12/1/05 1/31/06 300
TR2 R1 P2 T2 1/1/06 1/15/06 80
TR3 R2 P2 T2 1/1/06 1/15/06 80

Hopefully that gives you an Idea.

What I need to give my users is how many hours per month are planned by
project and/or resource for some set reporting timeframe. The timeframes are
up to 2 years at a pop and there are thousands of records in tblTaskRsrsc.

The way I do this now is find out how many working days are between the
begin and end dates and then divide the number of hours by days to get the
per day hours. Once I have that, I bump the resultant table against another
table that contains one row for every working day in my timeframe so that
what I end up with is a row for every taskrsrc/day in my timeframe.

As you can imagine, this is EXTREMELY slow and the resulting tables are
huge. Does anyone have any thoughts on a more efficient way of doing this
kind of thing?

Any help is greatly appreciated...

Thanks,

Mike
 
Any idea what specifically is taking the time? Do you have appropriate
indexes on your tables?

In the event that it's the calculation that's taking the time, how are you
calculating the number of working days?
 
I believe each of the tables/fields are indexed appropriately. That does
bring up an interesting thought though. In DB2 I can check an indexed field
for values that I know to be true IE: a 5 digit alphanumeris I could say
between "00000" and "zzzzz"
This would speed up any query I ran. Is the same true in Access?

I have tried the working days calculation in two different ways. In one I
use a table that contains every working date from way in the past to way in
the future. The other method I use is a function that checks each date in a
range to see if it is a working day. I got that from one of the MVPs. Both
methods seem to be equally slow but not as slow as this overall process. What
I need to end up with in the long run is a table that contains each
taskrsrc_id, the number of hours per day on task and the working date. Like
this:

taskrsrc_id Daily Work_date
TR1 8 12/1/05
TR1 8 12/2/05
TR1 8 12/5/05

I hope this makes sense. I know the number of workday calculation is slow,
no matter which method I use but it's the piece where I create the table
above that really kills the process.

Thanks,

mpj
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office


Douglas J. Steele said:
Any idea what specifically is taking the time? Do you have appropriate
indexes on your tables?

In the event that it's the calculation that's taking the time, how are you
calculating the number of working days?
Click to expand...
 
Ok, I've been struggling with this all day again today... Help!

I guess it would help if I can clearly explain what I am starting with and
what I ultimately need to end up with. So here goes...

I have a table (taskrsrc) it contains about 300,000 records each of which
identifies a particular resource's assignment to a particular task which is
associated with a particular project. The pertinent information in the table
is the rowid aka taskrsrcid, the resource id, the project id the number of
hours associated with that row and the begin and end dates associated with
that row.

I also have a table that contains the date of every working day between now
and the year 2030.

What I need to get are essentially two reports. One is a list of all
projects and the number of hours by month for the remainder of the current
year and all of next year.

The other report is the same except instead of being by project, the report
is by resource group which I can Identify from another table.

I think in order to get to these reports I need to change the data from the
format it is in into a table that contains one row for each taskrsrc row for
each working day that falls between the begin and end date for that row.

Am I missing some step that would make my life easier?

Thanks,

Michael
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office


MJatAflac said:
I believe each of the tables/fields are indexed appropriately. That does
bring up an interesting thought though. In DB2 I can check an indexed field
for values that I know to be true IE: a 5 digit alphanumeris I could say
between "00000" and "zzzzz"
This would speed up any query I ran. Is the same true in Access?

I have tried the working days calculation in two different ways. In one I
use a table that contains every working date from way in the past to way in
the future. The other method I use is a function that checks each date in a
range to see if it is a working day. I got that from one of the MVPs. Both
methods seem to be equally slow but not as slow as this overall process. What
I need to end up with in the long run is a table that contains each
taskrsrc_id, the number of hours per day on task and the working date. Like
this:

taskrsrc_id Daily Work_date
TR1 8 12/1/05
TR1 8 12/2/05
TR1 8 12/5/05

I hope this makes sense. I know the number of workday calculation is slow,
no matter which method I use but it's the piece where I create the table
above that really kills the process.

Thanks,

mpj
Click to expand...
 
Take a look at my September, 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access". I've got a one-liner there to calculate the
number of working days between two dates.

You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

I don't see what adding a field that you're not using (whether or not it's
indexed) would speed up the query. I guess it's been too long since I used
DB2! <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



MJatAflac said:
I believe each of the tables/fields are indexed appropriately. That does
bring up an interesting thought though. In DB2 I can check an indexed
field
for values that I know to be true IE: a 5 digit alphanumeris I could say
between "00000" and "zzzzz"
This would speed up any query I ran. Is the same true in Access?

I have tried the working days calculation in two different ways. In one I
use a table that contains every working date from way in the past to way
in
the future. The other method I use is a function that checks each date in
a
range to see if it is a working day. I got that from one of the MVPs. Both
methods seem to be equally slow but not as slow as this overall process.
What
I need to end up with in the long run is a table that contains each
taskrsrc_id, the number of hours per day on task and the working date.
Like
this:

taskrsrc_id Daily Work_date
TR1 8 12/1/05
TR1 8 12/2/05
TR1 8 12/5/05

I hope this makes sense. I know the number of workday calculation is slow,
no matter which method I use but it's the piece where I create the table
above that really kills the process.

Thanks,

mpj
Click to expand...
 
MJatAflac said:
Ok, I've been struggling with this all day again today... Help!

I guess it would help if I can clearly explain what I am starting with and
what I ultimately need to end up with. So here goes...

I have a table (taskrsrc) it contains about 300,000 records each of which
identifies a particular resource's assignment to a particular task which is
associated with a particular project. The pertinent information in the table
is the rowid aka taskrsrcid, the resource id, the project id the number of
hours associated with that row and the begin and end dates associated with
that row.

I also have a table that contains the date of every working day between now
and the year 2030.

What I need to get are essentially two reports. One is a list of all
projects and the number of hours by month for the remainder of the current
year and all of next year.

The other report is the same except instead of being by project, the report
is by resource group which I can Identify from another table.

I think in order to get to these reports I need to change the data from the
format it is in into a table that contains one row for each taskrsrc row for
each working day that falls between the begin and end date for that row.

Am I missing some step that would make my life easier?

Thanks,

Michael
I believe each of the tables/fields are indexed appropriately. That does
bring up an interesting thought though. In DB2 I can check an indexed field
Click to expand...
[quoted text clipped - 69 lines]
Click to expand...

Michael,

Currently, it doesn't sound like a path to least resistance exists for you.
However, if you have permissions, adding fields to the table such as
HoursMonthly or HoursDaily, which could be calculated prior to committing the
record would greatly reduce this task.

If you're asking for a solution to your current problem, I personally don't
see a simple solution. However, if you see this task as a consistent future
headache, adding fields would be a priority for me.
 
Back
Top