Stacking queries and Calculations

[DawnTreader]

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.

 

[Jeff Boyce]

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.

 

[KARL DEWEY]

Why can you not do it all in one query?

 

[DawnTreader]

Because of joins

Why can you not do it all in one query?