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
|
|||
|
|||
Stacking queries and Calculations
Hello All
I have a general question about how best to do something. I have a lot of queries in my app that do calculations at some point in the stacks of queries to get results i need for various functions in the app. is it better to pull fields and do calculations later? meaning if i have a stack of 3 queries should i just pull fields in the first query and then have the second query pull the information from the first and then in the third do all my calculations? or would it be better to do a calculation right away and then just pull that through my data sets? the reason i ask is that i have done it both ways in various situations and sometimes i have done calcs at each step. it makes me wonder if there is a more efficient way that would make my queries and app faster. any and all help appreciated. |
#2
|
|||
|
|||
Stacking queries and Calculations
That's going to be a tough one to predict ... there are so many factors.
For instance, if you are using an Access front-end against SQL-Server back-end data, and if, in your query, you use a "local" function (local to Access), SQL-Server isn't going to know how to process the SQL of that query without having to translate ... i.e., slower performance. And do your tables have appropriate indexing? That could be a bigger factor than how many fields you pull. And how many fields and tables? With what you've described so far, I can only offer an unqualified "perhaps" g. And the bottom line may actually be you telling us what worked better in your situation. You may have to try/test both ways to find the fastest... Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "DawnTreader" wrote in message ... Hello All I have a general question about how best to do something. I have a lot of queries in my app that do calculations at some point in the stacks of queries to get results i need for various functions in the app. is it better to pull fields and do calculations later? meaning if i have a stack of 3 queries should i just pull fields in the first query and then have the second query pull the information from the first and then in the third do all my calculations? or would it be better to do a calculation right away and then just pull that through my data sets? the reason i ask is that i have done it both ways in various situations and sometimes i have done calcs at each step. it makes me wonder if there is a more efficient way that would make my queries and app faster. any and all help appreciated. |
#3
|
|||
|
|||
Stacking queries and Calculations
Why can you not do it all in one query?
-- Build a little, test a little. "DawnTreader" wrote: Hello All I have a general question about how best to do something. I have a lot of queries in my app that do calculations at some point in the stacks of queries to get results i need for various functions in the app. is it better to pull fields and do calculations later? meaning if i have a stack of 3 queries should i just pull fields in the first query and then have the second query pull the information from the first and then in the third do all my calculations? or would it be better to do a calculation right away and then just pull that through my data sets? the reason i ask is that i have done it both ways in various situations and sometimes i have done calcs at each step. it makes me wonder if there is a more efficient way that would make my queries and app faster. any and all help appreciated. |
#4
|
|||
|
|||
Stacking queries and Calculations
Because of joins
"KARL DEWEY" wrote: Why can you not do it all in one query? -- Build a little, test a little. "DawnTreader" wrote: Hello All I have a general question about how best to do something. I have a lot of queries in my app that do calculations at some point in the stacks of queries to get results i need for various functions in the app. is it better to pull fields and do calculations later? meaning if i have a stack of 3 queries should i just pull fields in the first query and then have the second query pull the information from the first and then in the third do all my calculations? or would it be better to do a calculation right away and then just pull that through my data sets? the reason i ask is that i have done it both ways in various situations and sometimes i have done calcs at each step. it makes me wonder if there is a more efficient way that would make my queries and app faster. any and all help appreciated. |
Thread Tools | |
Display Modes | |
|
|