How does SQL do calculations? Is there a way to speed up my query?

I have a SQL query in a tool called Denodo Virtual data port, it uses a mix of different SQL syntaxes but most closely mirrors that of PostgreSQL. The query looks at two tables:

  1. A transaction table with billions of rows
  2. A FX table with 200 or so rows

The query looks something like this:

SELECT date, country, type, SUM(FxTable.rate*localamount) FROM TransactionTable WHERE date BETWEEN '12/07/2020' AND '02/17/2021' LEFT OUTER JOIN FxTable ON FxTable.CurrencyCode = TransactionTable.CurrencyCode IS NOT NULL GROUP BY date, country, type 

This query takes over 8 hours to run. How does SQL handle this? Does it look at each row, match the currency, apply the exchange rate, output the result and then move to the next row? Or does it sum all the same currencies and THEN apply the exchange rate?

I wonder if grouping the currencies together and then applying the Fx rate would be the quicker of the options? Can this be done?

The query without the Fx calculation takes 25 min to run.

submitted by /u/cartwheeleris
[link] [comments]


Go to Source of this post
Author Of this post: /u/cartwheeleris
Title Of post: How does SQL do calculations? Is there a way to speed up my query?
Author Link: {authorlink}