If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Massive Large Query Issues
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 |
#3
|
|||
|
|||
Massive Large Query Issues
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 |
#4
|
|||
|
|||
Massive Large Query Issues
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 |
#5
|
|||
|
|||
Massive Large Query Issues
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] |
Thread Tools | |
Display Modes | |
|
|