Well, actually Ade, you are resisitng rewriting the query because it used to work but consider this,
As Products evolve they often try to make things quicker. This is especially true of database systems and query processors. It can very easily happen that a change to a query processor will cause some queries to run faster, others to be unchanged, and a few to run slower.
Let us consider one kind of change to the query processor which might make your query break in a newer version. Take the part Duane raises, using a calculated field in other calculations, something which I, like Duane, instinctively mistrust. The newer version of the query processor *might* be expanding the construct in place before working on the best plan to execute the query. This might sound like a crazy inefficient thing to do but it might make the query capable of being processed in parallel whereas the way you wrote it has to be done in several serial steps. The query processor has to first create those calculated fields and *then* do the calculations based on those results. This can have a massive negative effect on the processing time for the query. A change like this in the query processor can make some people grin about the improvements in speed in the new version but it might cause others to scream.
In the case of your query you even have multplie levels of calculations based on previous calculations and you have a whole bunch of IIF() statements, and you have fields being either implicitly converted to strings and concatenated or implicitly converted to numbers and added together ([High]+[Low] and [High] & [Low]). I would have taken a bet on this thing failing at some point, sorry.
This is simply one of those facts in life, things change and sometimes you just have to accept this and make changes yourself.
In the case of your Query my advice would be to rewrite it into a sub query where the first level of calculations are made just based on the root table and then in the next level of query add in the referenced table(s) and IIF() sums.
Good luck with it.
Andrew
__._,_.___
Posted by: yahoo@craven.de
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (9) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? The Yahoo Mail app is fast, beautiful and intuitive. Try it today!
.
__,_._,___
Tidak ada komentar:
Posting Komentar