Why does my query run so slow? (1 Viewer)

[OliWatkins]

I am making a small database program to run our club. The club members pay in one of 4 currencies when they visit our club and use our facilities. Because of the volatility of exchange rates, we set them manually and change them when needed.

I want to apply an exchange rate to a transaction based on the member’s start date.

I have done this using the below SQL. I then use this query in another query of the transactions to find how much this is in our local currency.

The query works, BUT it runs very slowly and there are only 1000 records so far. Where am I going wrong?

Thank you!

SELECT custdates.CustID, custdates.CustDateID, ExchangeRates.xcurrency, custdates.startdate, ExchangeRates.datetimeentered, ExchangeRates.Rate, ExchangeRates.ID
FROM custdates, ExchangeRates, (SELECT DISTINCT xcurrency FROM ExchangeRates)  AS C
WHERE (((ExchangeRates.datetimeentered)=(SELECT Max(datetimeentered) FROM ExchangeRates WHERE datetimeentered<startdate and xcurrency =c.xcurrency)))
ORDER BY custdates.CustDateID, ExchangeRates.xcurrency;

 

[theDBguy]

Hi. Just a guess but you might have better performance if you use a JOIN instead of using a Cartesian Query.

 

[GPGeorge]

I am making a small database program to run our club. The club members pay in one of 4 currencies when they visit our club and use our facilities. Because of the volatility of exchange rates, we set them manually and change them when needed.

I want to apply an exchange rate to a transaction based on the member’s start date.

I have done this using the below SQL. I then use this query in another query of the transactions to find how much this is in our local currency.

The query works, BUT it runs very slowly and there are only 1000 records so far. Where am I going wrong?

Thank you!

SELECT custdates.CustID, custdates.CustDateID, ExchangeRates.xcurrency, custdates.startdate, ExchangeRates.datetimeentered, ExchangeRates.Rate, ExchangeRates.ID
FROM custdates, ExchangeRates, (SELECT DISTINCT xcurrency FROM ExchangeRates)  AS C
WHERE (((ExchangeRates.datetimeentered)=(SELECT Max(datetimeentered) FROM ExchangeRates WHERE datetimeentered<startdate and xcurrency =c.xcurrency)))
ORDER BY custdates.CustDateID, ExchangeRates.xcurrency;

In addition to designation joins, you need to ensure that all of the fields used are properly indexed.

 

[CJ_London]

that can be the problem when creating a 'view' of all possible scenarios

better to include criteria to limit it in some way - a specific currency and/or custdate for example

Not sure why you need your 'C' query - wouldn't that be defined from one of the other tables?

 

[Pat Hartman]

How is it fair to set the exchange rate for a member to the rate in force on the date he joined?

Transactions should be priced a the exchange rate on the day they are recorded. AND for simplicity you should save the currency paid with and amt and the converted value to the common currency. That way, you only need to deal with conversions when the data is entered. all the rest of your forms/reports/queries use only the converted amount.