When your (ex-)boss writes SQL



  • Also, when he has never bothered to learn DECODE() and does everything with CASE statements. You might also detect correctly that he is pretty weak on Boolean logic...

    This was a subquery inside a much larger query. I present it with no changes other than formatting.

    select	o.row_id app_id, c.row_id cust_id, c.fst_name applicant_fst_name, c.last_name applicant_last_name,
    	sum (case when income_flg = 'Y' and fie.x_afg_sub_type_cd ='Salary - Gross' and fie.freq_cd = 'Annually'
    		then fie.incm_exp_amt
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Salary - Gross' and fie.freq_cd = 'Quaterly'
    		then fie.incm_exp_amt * 4
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Salary - Gross' and fie.freq_cd = 'Monthly'
    		then fie.incm_exp_amt * 12
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Salary - Gross' and fie.freq_cd = '4weekly'
    		then fie.incm_exp_amt * 13
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Salary - Gross' and fie.freq_cd = 'Fortnightly'
    		then fie.incm_exp_amt * 26
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Salary - Gross' and fie.freq_cd = 'Weekly'
    		then fie.incm_exp_amt * 52
    		else 0 end) gross_sal,
    	sum (case when income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Salary - Net' and fie.freq_cd = 'Annually'
    		then fie.incm_exp_amt
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Salary - Net' and fie.freq_cd = 'Quaterly'
    		then fie.incm_exp_amt * 4
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Salary - Net' and fie.freq_cd = 'Monthly'
    		then fie.incm_exp_amt * 12
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Salary - Net' and fie.freq_cd = '4weekly'
    		then fie.incm_exp_amt * 13
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Salary - Net' and fie.freq_cd = 'Fortnightly'
    		then fie.incm_exp_amt * 26 
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Salary - Net' and fie.freq_cd = 'Weekly'
    		then fie.incm_exp_amt * 52
    		else 0 end) net_sal,
    	sum (case when income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Overtime - Gross' and fie.freq_cd = 'Annually'
    		then fie.incm_exp_amt
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Overtime - Gross' and fie.freq_cd = 'Quaterly'
    		then fie.incm_exp_amt * 4
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Overtime - Gross' and fie.freq_cd = 'Monthly'
    		then fie.incm_exp_amt * 12 
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Overtime - Gross' and fie.freq_cd = '4weekly'
    		then fie.incm_exp_amt * 13
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Overtime - Gross' and fie.freq_cd = 'Fortnightly'
    		then fie.incm_exp_amt * 26
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Overtime - Gross' and fie.freq_cd = 'Weekly'
    		then fie.incm_exp_amt * 52
    		else 0 end) gross_ot,
    	sum (case when income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Overtime - Net' and fie.freq_cd = 'Annually'
    		then fie.incm_exp_amt
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Overtime - Net' and fie.freq_cd = 'Quaterly'
    		then fie.incm_exp_amt * 4
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Overtime - Net' and fie.freq_cd = 'Monthly'
    		then fie.incm_exp_amt * 12
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Overtime - Net' and fie.freq_cd = '4weekly'
    		then fie.incm_exp_amt * 13
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Overtime - Net' and fie.freq_cd = 'Fortnightly'
    		then fie.incm_exp_amt * 26
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd = 'Overtime - Net' and fie.freq_cd = 'Weekly'
    		then fie.incm_exp_amt * 52
    		else 0 end) net_ot,
    	sum (case when income_flg = 'Y' and fie.x_afg_sub_type_cd not in ('Salary - Gross', 'Salary - Net') and fie.freq_cd = 'Annually'
    		then fie.incm_exp_amt
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd not in ('Salary - Gross', 'Salary - Net') and fie.freq_cd = 'Quaterly'
    		then fie.incm_exp_amt * 4 
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd not in ('Salary - Gross', 'Salary - Net') and fie.freq_cd = 'Monthly'
    		then fie.incm_exp_amt * 12
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd not in ('Salary - Gross', 'Salary - Net') and fie.freq_cd = '4weekly'
    		then fie.incm_exp_amt * 13
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd not in ('Salary - Gross', 'Salary - Net') and fie.freq_cd = 'Fortnightly'
    		then fie.incm_exp_amt * 26
    		when fie.income_flg = 'Y' and fie.x_afg_sub_type_cd not in ('Salary - Gross', 'Salary - Net') and fie.freq_cd = 'Weekly'
    		then fie.incm_exp_amt * 52
    		else 0 end) other_inc,
    	sum (case when income_flg = 'N' and fie.freq_cd = 'Annually' 
    		then fie.incm_exp_amt
    		when fie.income_flg = 'N' and fie.freq_cd = 'Quaterly'
    		then fie.incm_exp_amt * 4
    		when fie.income_flg = 'N' and fie.freq_cd = 'Monthly'
    		then fie.incm_exp_amt * 12
    		when fie.income_flg = 'N' and fie.freq_cd = '4weekly'
    		then fie.incm_exp_amt * 13
    		when fie.income_flg = 'N' and fie.freq_cd = 'Fortnightly'
    		then fie.incm_exp_amt * 26
    		when fie.income_flg = 'N' and fie.freq_cd = 'Weekly'
    		then abs (fie.incm_exp_amt) * 52
    		else 0 end) expenses,
    	ea.todo_cd scenario, ea.row_id scenario_id
    from	s_fn_incm_exp fie, s_contact c, s_opty o, s_evt_act ea
    where	fie.pr_con_id = c.row_id and fie.activity_id = ea.row_id and ea.opty_id = o.row_id
    group by o.row_id, c.row_id, c.fst_name, c.last_name, ea.todo_cd, ea.row_id

    For bonus points, the table which stores the list of values "Quarterly", "Monthly", and so on also has a column called WEIGHTING_FACTOR that contains the appropriate multipliers already, so to get an annual amount you just have to join to the list of values and multiply by that column.

    For even more bonus points, note that "Quarterly" is consistently misspelled in the query, so quarterly income and expenses aren't getting picked up at all. And taking the absolute value of the expense amount if it's a weekly expense but the normal value for any other frequency seems unlikely to be a good thing.



  •  What's wrong with using CASE? 



  • @renewest said:

     What's wrong with using CASE? 

    Nothing per se.  However it does have a bit of a performance impact.  Basically this query could have been much smaller and thus easier to maintain.  CASE adds complexity and makes the query harder to follow.

  • ♿ (Parody)

    @galgorah said:

    @renewest said:

     What's wrong with using CASE? 

    Nothing per se.  However it does have a bit of a performance impact.  Basically this query could have been much smaller and thus easier to maintain.  CASE adds complexity and makes the query harder to follow.

    Yep. In some places, CASE is really required, but if you can, you should use DECODE. If you don't need either one, and you use CASE anyways, then you are TRWTF, like the OP's ex-boss. When you find yourself trying to use it as much as this query does, you should realize that there's probably a better way.



  • @boomzilla said:

    @galgorah said:

    @renewest said:

     What's wrong with using CASE? 

    Nothing per se.  However it does have a bit of a performance impact.  Basically this query could have been much smaller and thus easier to maintain.  CASE adds complexity and makes the query harder to follow.
    Yep. In some places, CASE is really required, but if you can, you should use DECODE. If you don't need either one, and you use CASE anyways, then you are TRWTF, like the OP's ex-boss. When you find yourself trying to use it as much as this query does, you should realize that there's probably a better way.
    If this is t-sql then there is no decode.


  • TRWTF is the misspelling of 'Quaterly' ... repeatedly ... amirite?

     

    [Granted, not maintaining the spelling would break things horribly ...]



  • @zelmak said:

    TRWTF is the misspelling of 'Quaterly' ... repeatedly ... amirite?

    This is, of course, due to a spelling error in the underlying application that is normally working with the field fie.freq_cd -- you have to keep compatibility somehow (thus perpetuating an error instead of fixing it)


  • @boomzilla said:

    Yep. In some places, CASE is really required, but if you can, you should use DECODE.

    Except T-SQL doesn't include DECODE, so... well, I was going to say it's an Oracle-ism, but I don't know that for sure. So I'll just stop at: but one very very good RDMS doesn't implement DECODE, leaving CASE as the only way of handling this.

    Edit: there's this tendency for the OP to mention a very minor WTF in the opening (he used CASE instead of DECODE) and completely gloss-over the much, much bigger WTF further down (both CASE and DECODE are unnecessary, because you can get the same values with a join and some math.)



  • @zelmak said:

    TRWTF is the misspelling of 'Quaterly' ... repeatedly ... amirite?

     

    [Granted, not maintaining the spelling would break things horribly ...]

     

    Once upon a time, there was evidently somebody in a position of authority who typed with an accent.

     



  • @da Doctah said:

    @zelmak said:

    TRWTF is the misspelling of 'Quaterly' ... repeatedly ... amirite?

     

    [Granted, not maintaining the spelling would break things horribly ...]

     

    Once upon a time, there was evidently somebody in a position of authority who typed with an accent.

     

     

    Or watched Quartermain too often.

     



  • @galgorah said:

    @renewest said:

     What's wrong with using CASE? 

    Nothing per se.  However it does have a bit of a performance impact.  Basically this query could have been much smaller and thus easier to maintain.  CASE adds complexity and makes the query harder to follow.

    This is not true. There is no performance gain at all, and if someone had a lot of time to spend on writing a proper benchmark, my guess is that the CASE would run a little bit faster because there is no conversion. Also CASE is more flexible and can be used in any context. Oracle fully supports both constructs but has been promoting CASE for many years now. Anyone saying that using either one of those is a WTF is wrong.



    It's like COALESCE vs ISNULL in T-SQL. Both have pros and cons, and besides the conversion there is no real difference.



  • @zelmak said:

    TRWTF is the misspelling of 'Quaterly' ... repeatedly ... amirite?

    I've seen that before, when someone forgot to enable Spell-check on the clipboard



  • This post illustrates a problem with software development: focusing on the code instead of focusing on the behavior of the system at runtime.



    What is a good idea in an imperative language (such as C#) is not always a good idea in a declarative language (such as SQL). When you work with a database engine, the important thing is to write code that will maximize performance and minimize contention; the focus should not be on code reuse or clever tricks. If a value is static (such as "12" for the number of months), then hard-coding it instead of storing it in a reference table is a good thing because at runtime, in production, the I/O will not be impacted. One has to weigh the pros and cons but the more dynamic a piece of SQL code is, the worst it will perform in production.



    So sometimes it is much better to do a large CASE or a bunch of IF because the boolean processing will save I/O and will also allow the database query optimizer to do its job properly, and possibly to reuse execution plans. As for managing hard-coded numbers in SQL code, well it sucks but there is pretty good software on the market to manage this kind of thing.


  • ♿ (Parody)

    @thistooshallpass said:

    There is no performance gain at all, and if someone had a lot of time to spend on writing a proper benchmark, my guess is that the CASE would run a little bit faster because there is no conversion. Also CASE is more flexible and can be used in any context. Oracle fully supports both constructs but has been promoting CASE for many years now. Anyone saying that using either one of those is a WTF is wrong.

    My preference is really because I find DECODE much more readable and concise, at least in the places where it's sensible to use it. I've used CASE, though for both constructs, I try to find a better way, which usually starts with a better (for the purposes it will be used) DB design.



  • @Scarlet Manuka said:

    [code]from s_fn_incm_exp fie, s_contact c, s_opty o, s_evt_act ea[/code]
    You don't think using the proprietary JOIN syntax that was deprecated in the mid-nineties is a bigger WTF?



  • @Jaime said:

    @Scarlet Manuka said:

    <font face="Lucida Console" size="2">from s_fn_incm_exp fie, s_contact c, s_opty o, s_evt_act ea</font>
    You don't think using the proprietary JOIN syntax that was deprecated in the mid-nineties is a bigger WTF?


    That syntax was awesome for outer joins, it made the code look like it could be expanded with a mouse click:


    SELECT product, name
    FROM purchase, catalog
    WHERE id = sku (+);
    

  • ♿ (Parody)

    @Jaime said:

    @Scarlet Manuka said:
    <font face="Lucida Console" size="2">from s_fn_incm_exp fie, s_contact c, s_opty o, s_evt_act ea</font>

    You don't think using the proprietary JOIN syntax that was deprecated in the mid-nineties is a bigger WTF?

    I hate those joins. A lot of "old school" Oracle types still use them. Hooray for unintended cartesian joins! The bigger WTF is how many ANSI join bugs remain in Oracle.



  • @thistooshallpass said:

    @galgorah said:

    @renewest said:

     What's wrong with using CASE? 

    Nothing per se.  However it does have a bit of a performance impact.  Basically this query could have been much smaller and thus easier to maintain.  CASE adds complexity and makes the query harder to follow.
    This is not true. There is no performance gain at all, and if someone had a lot of time to spend on writing a proper benchmark, my guess is that the CASE would run a little bit faster because there is no conversion. Also CASE is more flexible and can be used in any context. Oracle fully supports both constructs but has been promoting CASE for many years now. Anyone saying that using either one of those is a WTF is wrong.

    It's like COALESCE vs ISNULL in T-SQL. Both have pros and cons, and besides the conversion there is no real difference.

    Perhaps you should read the op before posting.  Had you done so, you would have known that I was not comparing the speed of decode and case.

    There is already a column on the table that provides the data the case was meant to find.  this WOULD speed up the query since it would eliminate the need for the case logic entirely.



  • @galgorah said:

    @thistooshallpass said:

    @galgorah said:

    @renewest said:

     What's wrong with using CASE? 

    Nothing per se.  However it does have a bit of a performance impact.  Basically this query could have been much smaller and thus easier to maintain.  CASE adds complexity and makes the query harder to follow.
    This is not true. There is no performance gain at all, and if someone had a lot of time to spend on writing a proper benchmark, my guess is that the CASE would run a little bit faster because there is no conversion. Also CASE is more flexible and can be used in any context. Oracle fully supports both constructs but has been promoting CASE for many years now. Anyone saying that using either one of those is a WTF is wrong.

    It's like COALESCE vs ISNULL in T-SQL. Both have pros and cons, and besides the conversion there is no real difference.

    Perhaps you should read the op before posting.  Had you done so, you would have known that I was not comparing the speed of decode and case.

    There is already a column on the table that provides the data the case was meant to find.  this WOULD speed up the query since it would eliminate the need for the case logic entirely.

    I did read it, and between the "CASE vs DECODE" comment in the op and the question about what is wrong with CASE, your reply looks a lot like a comment about CASE vs DECODE.



    This being said, having the database contain numbers that can be calculated from the content of other columns is a bad idea, because not only does it impact the performance by adding redundant information in the storage, it also open the doors to erroneous calculations. When you have a quantity and a unit price, adding a total column is a serious WTF, unless this is done in a very specific context, such as a denormalized data warehouse where you want to do large aggregations.



  • @thistooshallpass said:

    I did read it, and between the "CASE vs DECODE" comment in the op and the question about what is wrong with CASE, your reply looks a lot like a comment about CASE vs DECODE.

    This being said, having the database contain numbers that can be calculated from the content of other columns is a bad idea, because not only does it impact the performance by adding redundant information in the storage, it also open the doors to erroneous calculations. When you have a quantity and a unit price, adding a total column is a serious WTF, unless this is done in a very specific context, such as a denormalized data warehouse where you want to do large aggregations.

    I do agree that a computed column isn't always a good idea.  But depending on the design of the database structures and the purpose of the db, It may be a good solution. I'm in charge of improving db performance for a system that manages the garment lifecycle for 70+ plants globally.  When I started here back in february, one of the major issues was that we had several queries that would process large quantities of data, multiple times an hour.  Bad design I know, thats being worked on.  We added a computed column to handle a bit value determining if a garment was of one set of types or another.  This brought query execution time down considerably and stopped the deadlocks.  I should also add that we could have just used (nolock) but that just kind of hides the issue and would have allowed "dirty data" which would have been less than acceptable given the purpose of the query.

    Unfortunatly Query tuning is very much dependent on schema.  You have to take the purpose and structure into account.  What works to improve performance on one database may be the wrong approach on another.    

     



  • Some clarifications: Yes, this is Oracle, so DECODE() is available, otherwise I wouldn't have mentioned it.
    @TheRider said:

    @zelmak said:
    TRWTF is the misspelling of 'Quaterly' ... repeatedly ... amirite?
    This is, of course, due to a spelling error in the underlying application that is normally working with the field fie.freq_cd -- you have to keep compatibility somehow (thus perpetuating an error instead of fixing it)

    Nope, it's just a bug in my boss's query (replicated through the magic of copy and paste). The value in the app is spelled correctly. I did note in the OP that this was an error in the query, though admittedly at the end.

    @renewest said:

    What's wrong with using CASE?
    There's nothing wrong with using CASE when it's appropriate. But when you're comparing one value against a predefined list, that's what DECODE is for. Additionally, as I mentioned, there's no point repeating the same two conditions over and over in every sub-case. If you had to do it all with CASE statements, it should look like this:
    sum (case when income_flg = 'Y' and fie.x_afg_sub_type_cd ='Salary - Gross' then fie.incm_exp_amt * 
    	case when fie.freq_cd = 'Annually' then 1 when fie.freq_cd = 'Quarterly' then 4 
    	when fie.freq_cd = 'Monthly' then 12 when fie.freq_cd = '4weekly' then 13 
    	when fie.freq_cd = 'Fortnightly' then 26 when fie.freq_cd = 'Weekly' then 52 else 0 end end) gross_sal

    Assuming you didn't know about the database table, the most sensible syntax would have been to combine CASE and DECODE:

    sum (case when income_flg = 'Y' and fie.x_afg_sub_type_cd ='Salary - Gross' 
    	then fie.incm_exp_amt * decode(fie.freq_cd, 'Annually', 1, 'Quarterly', 4, 'Monthly', 12, 
    	'4weekly', 13, 'Fortnightly', 26, 'Weekly', 52, 0) end) gross_sal
    Actually, even better in both of the above would be to replace the table aliased as fie with a subquery along the lines of
    select income_flg, incm_exp_amt, x_afg_sub_type_cd, {logic goes here} as multiplier from {table}
    so that you only need to do the conversion once.

    However, joining to the existing database table would have been better since

    • If new frequencies are added the logic only has to be maintained in one place
    • It would have avoided the bug introduced by spelling "Quarterly" incorrectly
    • It would make the query easier to read and mantain
    • There would be no measurable performance impact
    (For the last, we're talking about looking up and caching about a dozen values based on an index, in a query which is aggregating income information for hundreds of thousands of customers.)

    @Jaime said:

    @Scarlet Manuka said:
    <FONT size=2 face="Lucida Console">from s_fn_incm_exp fie, s_contact c, s_opty o, s_evt_act ea</FONT>
    You don't think using the proprietary JOIN syntax that was deprecated in the mid-nineties is a bigger WTF?
    I've given up on trying to win that fight at my workplace. I don't know why, but every new employee we get seems to be familiar with that join syntax and unfamiliar with ANSI join syntax. I talk to them about the benefits of using ANSI joins but they just shrug and go back to using the old-style joins. I have managed to convert the guy who works most closely with me, so at least I don't have to run screaming every time I review his queries.

    @thistooshallpass said:

    This being said, having the database contain numbers that can be calculated from the content of other columns is a bad idea, because not only does it impact the performance by adding redundant information in the storage, it also open the doors to erroneous calculations. When you have a quantity and a unit price, adding a total column is a serious WTF, unless this is done in a very specific context, such as a denormalized data warehouse where you want to do large aggregations.
    I hardly think that's a comparable situation. Total price can be derived very simply from quantity and unit price, whereas I can't see any way to implement this period logic with anything simpler than a DECODE or CASE - which, importantly, would need to be updated every time the list of values changed. In such a situation it's far better to have the conversion factor on the same table as the list of values, so that you don't have to update every query which uses them - assuming you even know where all the queries are that access that data in the first place.

    Incidentally, we do in fact have to populate separate unit price, quantity and total columns in Oracle EBS's interface tables whenever we're passing billing information to EBS. I don't know if that's another example of Oracle being TRWTF or if it's just Oracle's usual over-generalisation (maybe so you can do things like "$4.99 each, or 5 for $20"? I don't know).



  • @galgorah said:

    @boomzilla said:

    @galgorah said:

    @renewest said:

     What's wrong with using CASE? 

    Nothing per se.  However it does have a bit of a performance impact.  Basically this query could have been much smaller and thus easier to maintain.  CASE adds complexity and makes the query harder to follow.
    Yep. In some places, CASE is really required, but if you can, you should use DECODE. If you don't need either one, and you use CASE anyways, then you are TRWTF, like the OP's ex-boss. When you find yourself trying to use it as much as this query does, you should realize that there's probably a better way.
    If this is t-sql then there is no decode.
    Coalesce then?

    EDIT: Bah, beaten to it.



  • @Scarlet Manuka said:

  • There would be no measurable performance impact

  • (For the last, we're talking about looking up and caching about a dozen values based on an index, in a query which is aggregating income information for hundreds of thousands of customers.)

    So basically you would do 12x100000 lookups, versus 0 with a CASE. And that's for each concurrent user of that query. Yeah, I don't see why there should be a problem.



    As for caching... you do know that this does require system resources (mostly RAM), that could have been used for something useful? Of course when the server is slow, just add RAM... Typical.



  • @thistooshallpass said:

    @Scarlet Manuka said:
  • There would be no measurable performance impact
    • (For the last, we're talking about looking up and caching about a dozen values based on an index, in a query which is aggregating income information for hundreds of thousands of customers.)
      So basically you would do 12x100000 lookups, versus 0 with a CASE. And that's for each concurrent user of that query. Yeah, I don't see why there should be a problem.

      As for caching... you do know that this does require system resources (mostly RAM), that could have been used for something useful? Of course when the server is slow, just add RAM... Typical.
      No, the 12 values would be looked up once, read and cached in memory, and then hash joined in as required, usually two or three per customer. And my point is that the cost of hash joining in that value is trivial compared to the cost of looking up all the other stuff we're looking up per customer. That applies to the RAM cost almost as much as it does to the I/O cost.

      In addition, this query is a manual one being run by one person. If it gets deployed as a standard report (I don't know if this is planned) there wouldn't be more than a few people using it at most, and probably not at the same time. If I was in charge of database resourcing, which I'm not, I'd be more worried about the stuff that's accessible to thousands of people. Either way, if your database is so close to the edge that adding one very small join to an existing query can cripple it, I'd suggest it probably does need more resources anyway (what are you running it on, your laptop?). I only came across this one because a different subquery of the same parent query, which was being run across a database link, had somehow spawned itself multiple times with extremely weird optimiser hints which were in fact killing performance (running for hours or days instead of 20 seconds), and when it got up to a dozen or so concurrent copies of itself it started affecting database performance for a small number of other queries.

      But why take my word for it, when we can do an unscientific test? I created a version with the join in place instead of the sub-cases (and no other changes), and ran both approximately simultaneously; then I did it again to get a reading based on data already being cached, since data caching could have affected the first run in either direction. First run, the query using the join beat the query using sub-cases by 31 seconds (2:53 vs 3:24). On the second run, the query using the join won by 34 seconds (2:44 vs 3:18). So no, I don't see the performance problem with using the join. If it takes a little more RAM, that's a fair price to pay for a 15-17% decrease in run time.


    Log in to reply