Optimized SQL query when there's no physical connection between tables

Question

I need help with writing more optimized SQL query when there is no logical connection between tables. I wrote some subqueries but I'm not satisfied with it because I'm repeating almost the same subquery twice.
The scheme goes like this:

create table rate (
  rate_date date,
  value decimal(10,2),
  multiplier integer,
  name varchar(3)
  );

create table amount (
  amount decimal(10,2),
  amount_year date,
  rate_name varchar(3)
  );

I want every amount in AMOUNT table to be calculated with multiplier and rate value from RATE table like this amount / multiplier * rate. Common things for both tables are name (some code identifier) and date. Those should be used to get the data together. Only the newest rates for amount_year are taken into account.
You can check this fiddle if you're willing to help. That's my try.

select am.amount / 
    (
      select ra.multiplier 
      from rate ra
      where ra.rate_date = (select max(rate_date) 
                            from rate 
                            where year(rate_date) = year(am.amount_year) and name = am.rate_name) 
        and ra.name = am.rate_name
    ) * (
      select ra.value 
      from rate ra
      where ra.rate_date = (select max(rate_date) 
                            from rate 
                            where year(rate_date) = year(am.amount_year) and name = am.rate_name) 
        and ra.name = am.rate_name
    ) as calculated_amount
    from amount am;

What do you mean by "no physical connection between tables"? They're on different database servers? — Barmar, Oct 26 '15 at 20:45
@Barmar no foreign keys, no indexes etc. Assume that they may be on different schema. — shx, Oct 26 '15 at 20:51

score 2 · Answer 1 · answered Oct 26 '15 at 20:56

Your subqueries are the same, so you can do the work in one:

select am.amount / 
       (select ra.multiplier * ra.value
        from rate ra
        where ra.rate_date = (select max(rate_date) 
                              from rate 
                              where year(rate_date) = year(am.amount_year) and name = am.rate_name)  and ra.name = am.rate_name
                             )
       ) as calculated_amount
from amount am;

Or this can be phrased as:

select am.amount / 
       (select ra.multiplier * ra.value
        from rate ra
        where year(ra.rate_date) = year(am.amount_year) and name = am.rate_name
        order by ra.rate_date desc
        limit 1
       ) as calculated_amount
from amount am;

You can do this as a join with a where clause if you like:

select (am.amount / ra.multiplier * ra.value) as calculated_amount
from amount am join
     rate ra
     on ra.name = am.name and
        year(rate_date) = year(am.amount_year)
where ra.date = (select max(r2.rate_date)
                 from rate r2
                 where year(r2.rate_date) = year(r.rate_date) and
                       r2.name = ra.name
                );

For performance, start with an index on rate(name, rate_date, multiplier, value). for either query.

For better performance, you'll probably need to store "year" as a separate column in the table.

score 1 · Accepted Answer · answered Oct 26 '15 at 20:58

You can use a single subquery to get the newest multipler and value for each year. Then join that with the amount table to do the division.

SELECT am.amount / (r2.multiplier * r2.value) AS calculated_amount
FROM amount AS am
JOIN (SELECT YEAR(rate_date) AS year, MAX(rate_date) AS maxdate
      FROM rate
      GROUP BY year) AS r1 ON YEAR(am.rate_date) = r1.year
JOIN rate AS r2 ON r1.maxdate = r2.rate_date

Optimized SQL query when there's no physical connection between tables

2 Answers2