Massive Large Query Issues

I have a query that brings together data from 12 queries into a "shadow" flat
table to reduce form load times for certain forms. Recently, it's begun
running out of system resources. I think it's because all 12 queries (and
some of the subqueries...) call an Access (2007) user function I wrote:
______________________________
Public Function WeekNumber(ArgumentDate As Date, Optional WeekEndDate As
Date = #12/30/2000#) As Long
' Company's default week end day of week is Saturday, hence the default
12/30/00, which was a Saturday
' NOTE: This will not work well with dates prior to 12/30/00! Don't use
it!
' Modified 4/28/09 to correct wrong results for Saturday Argument Dates

WeekNumber = CLng((ArgumentDate - 1 - WeekEndDate) \ 7)
End Function
__________________________________

Anyway, I would guess I need to somehow stop all these myriad queries and
subqueries from calling this routine literally 300,000 times or more each
time I run this shadow-table create query.

What's the best way to do this?

I could create a reference table that has two columns: ReferenceDate,
WeekNbr
If I did that, could I join columns that are dates which may have
fractional (time) components? How do I do that? Do I trim the time first? How?

I could go back to the queries and replace many of those references with
the formula shown in the function above... I think I've done that in some
cases...

Of course, I'm not asking the fundamental question: Is there something
else that could be causing a query to run out of system resources (after
running for 45 minutes?)

Thanks for your advice!


--
Jim

Jim

I certainly don't have enough information to be certain about this, ...

.... but if you are using "12 queries", is there a chance you are working
with something like "one query per month"? What's the significance of "12"?
Without some idea of how your data is structured, it's difficult to offer
any specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"JimS" wrote in message
...
I have a query that brings together data from 12 queries into a "shadow"
flat
table to reduce form load times for certain forms. Recently, it's begun
running out of system resources. I think it's because all 12 queries (and
some of the subqueries...) call an Access (2007) user function I wrote:
______________________________
Public Function WeekNumber(ArgumentDate As Date, Optional WeekEndDate As
Date = #12/30/2000#) As Long
' Company's default week end day of week is Saturday, hence the default
12/30/00, which was a Saturday
' NOTE: This will not work well with dates prior to 12/30/00! Don't use
it!
' Modified 4/28/09 to correct wrong results for Saturday Argument Dates

WeekNumber = CLng((ArgumentDate - 1 - WeekEndDate) \ 7)
End Function
__________________________________

Anyway, I would guess I need to somehow stop all these myriad queries and
subqueries from calling this routine literally 300,000 times or more each
time I run this shadow-table create query.

What's the best way to do this?

I could create a reference table that has two columns: ReferenceDate,
WeekNbr
If I did that, could I join columns that are dates which may have
fractional (time) components? How do I do that? Do I trim the time first?
How?

I could go back to the queries and replace many of those references with
the formula shown in the function above... I think I've done that in some
cases...

Of course, I'm not asking the fundamental question: Is there something
else that could be causing a query to run out of system resources (after
running for 45 minutes?)

Thanks for your advice!


--
Jim

Sorry, Jeff. It's not doing that.

It is gathering spending and budget data for several different classes of
spending (captured in different ways -- time sheets, imports, POs, etc.) and
several different budget periods (each budget has two separate "spend curves"
with pro-forma spend patterns that create weekly budgets by project...) It's
a very difficult thing to describe.

The queries aggregate spending and budgets by week, then marry them together
in a non-normalized flat table for use with pivot table forms. I run these
queries (two of them) each night. They run for a couple of hours unattended,
and create the shadow tables for use the next day.

WeekNumber was a perfect candidate for a public function subroutine since
its calculation is obscure but well-defined. I didn't want anyone working for
me to calculate it differently, or have a typo cause a miscalculation, so I
set up the function subroutine. All told, the weeknumber function is probably
called nearly a million times to assign week numbers to all the aggregated
expenses and then combine them into a table that has about 200,000 rows when
it's done.

If only I could use SQL Server...

I'm not sure it's the culprit, though. Have you experienced running out of
system resources for other reasons?
--
Jim


"Jeff Boyce" wrote:

Jim

I certainly don't have enough information to be certain about this, ...

.... but if you are using "12 queries", is there a chance you are working
with something like "one query per month"? What's the significance of "12"?
Without some idea of how your data is structured, it's difficult to offer
any specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"JimS" wrote in message
...
I have a query that brings together data from 12 queries into a "shadow"
flat
table to reduce form load times for certain forms. Recently, it's begun
running out of system resources. I think it's because all 12 queries (and
some of the subqueries...) call an Access (2007) user function I wrote:
______________________________
Public Function WeekNumber(ArgumentDate As Date, Optional WeekEndDate As
Date = #12/30/2000#) As Long
' Company's default week end day of week is Saturday, hence the default
12/30/00, which was a Saturday
' NOTE: This will not work well with dates prior to 12/30/00! Don't use
it!
' Modified 4/28/09 to correct wrong results for Saturday Argument Dates

WeekNumber = CLng((ArgumentDate - 1 - WeekEndDate) \ 7)
End Function
__________________________________

Anyway, I would guess I need to somehow stop all these myriad queries and
subqueries from calling this routine literally 300,000 times or more each
time I run this shadow-table create query.

What's the best way to do this?

I could create a reference table that has two columns: ReferenceDate,
WeekNbr
If I did that, could I join columns that are dates which may have
fractional (time) components? How do I do that? Do I trim the time first?
How?

I could go back to the queries and replace many of those references with
the formula shown in the function above... I think I've done that in some
cases...

Of course, I'm not asking the fundamental question: Is there something
else that could be causing a query to run out of system resources (after
running for 45 minutes?)

Thanks for your advice!


--
Jim

Jim

Thanks for the clarification... one down ...g

Queries that run "for a couple hours" seem highly unusual. Typically, a
query will take longer to run when the table(s) it uses don't have indexing.
Can you confirm that the table(s) involved in one of these long-running
queries are indexed on all fields used as: search criteria, sort order, and
join fields?

Queries that run long may use inefficient SQL -- please post the SQL
statement for a couple of those.

Next, queries that run long may be doing so because the data is far away and
'dribbles in' -- is this a local database or a split front-end/back-end? If
split, where's the back-end? How are you connecting?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"JimS" wrote in message
...
Sorry, Jeff. It's not doing that.

It is gathering spending and budget data for several different classes of
spending (captured in different ways -- time sheets, imports, POs, etc.)
and
several different budget periods (each budget has two separate "spend
curves"
with pro-forma spend patterns that create weekly budgets by project...)
It's
a very difficult thing to describe.

The queries aggregate spending and budgets by week, then marry them
together
in a non-normalized flat table for use with pivot table forms. I run these
queries (two of them) each night. They run for a couple of hours
unattended,
and create the shadow tables for use the next day.

WeekNumber was a perfect candidate for a public function subroutine since
its calculation is obscure but well-defined. I didn't want anyone working
for
me to calculate it differently, or have a typo cause a miscalculation, so
I
set up the function subroutine. All told, the weeknumber function is
probably
called nearly a million times to assign week numbers to all the aggregated
expenses and then combine them into a table that has about 200,000 rows
when
it's done.

If only I could use SQL Server...

I'm not sure it's the culprit, though. Have you experienced running out of
system resources for other reasons?
--
Jim


"Jeff Boyce" wrote:

Jim

I certainly don't have enough information to be certain about this, ...

.... but if you are using "12 queries", is there a chance you are working
with something like "one query per month"? What's the significance of
"12"?
Without some idea of how your data is structured, it's difficult to offer
any specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"JimS" wrote in message
...
I have a query that brings together data from 12 queries into a "shadow"
flat
table to reduce form load times for certain forms. Recently, it's begun
running out of system resources. I think it's because all 12 queries
(and
some of the subqueries...) call an Access (2007) user function I wrote:
______________________________
Public Function WeekNumber(ArgumentDate As Date, Optional WeekEndDate
As
Date = #12/30/2000#) As Long
' Company's default week end day of week is Saturday, hence the
default
12/30/00, which was a Saturday
' NOTE: This will not work well with dates prior to 12/30/00! Don't
use
it!
' Modified 4/28/09 to correct wrong results for Saturday Argument
Dates

WeekNumber = CLng((ArgumentDate - 1 - WeekEndDate) \ 7)
End Function
__________________________________

Anyway, I would guess I need to somehow stop all these myriad queries
and
subqueries from calling this routine literally 300,000 times or more
each
time I run this shadow-table create query.

What's the best way to do this?

I could create a reference table that has two columns: ReferenceDate,
WeekNbr
If I did that, could I join columns that are dates which may have
fractional (time) components? How do I do that? Do I trim the time
first?
How?

I could go back to the queries and replace many of those references
with
the formula shown in the function above... I think I've done that in
some
cases...

Of course, I'm not asking the fundamental question: Is there something
else that could be causing a query to run out of system resources
(after
running for 45 minutes?)

Thanks for your advice!


--
Jim

On Wed, 23 Sep 2009 12:24:01 -0700, JimS
wrote:

WeekNumber was a perfect candidate for a public function subroutine since
its calculation is obscure but well-defined. I didn't want anyone working for
me to calculate it differently, or have a typo cause a miscalculation, so I
set up the function subroutine. All told, the weeknumber function is probably
called nearly a million times to assign week numbers to all the aggregated
expenses and then combine them into a table that has about 200,000 rows when
it's done.

PMFJI but... it sounds like weeknumber is also a good candidate for a lookup
table, with a Date/Time field and that date's corresponding weeknumber; this
could be joined (by an indexed date field) to any of your queries which need
it. This would be much faster and less resource-intensive than calling a
user-defined function 200000 times!
--

John W. Vinson [MVP]