SQL Server Performance Forum – Threads Archive

"Application" variable…!?!

I have an app that uses exchangerates for different currencies that get updated once every day at 3pm from the european central bank. The exchangerates are stored in a table that is properly indexed and everything and this all works fine. However, since the exchangerates only change once a day and remain the same all through to the next day, it would have been alot more effective to have some kind of global constants to store these rates. The rates are looked up for every single transaction in the database and I find this to be somewhat of an "overhead"… Is there such a thing as global constants or something close that will do this for me? —
Frettmaestro
"Real programmers don’t document, if it was hard to write it should be hard to understand"

Short answer no you could however get the client application to cache the exchange rates, but depending on the number of rates involved, the in-memory representation and searching may effectively make your application act like a SQL table, which is likely to be slower than SQLServer itself… <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Cheers Twan

This could maybe have been acomplished but the problem is that I don’t know the which currency to use at the time of initializing the transaction from my app. There can be an "unlimited" amount of transactions from any given initialization from the app (usually not more than 2-3 but still) and I find the correct rates in the SP. I have thaught about creating another table with only 6 records (there are only 6 different currencies in the system) that holds the current updated rates, but I doubt I will see any significant gains comparing to a table with less than 2000 rows. Hmm… —
Frettmaestro
"Real programmers don’t document, if it was hard to write it should be hard to understand"

Having worked for a credit union for 6 years, I think I understand what you want and yes, your hmmm maybe it would work solution does work quite well.
You need to keep a table of historical exchange rates, and thats the way to think of them, historical (including the current one) with a day parameter. Then keep a table that is only the currency and the to/from exchange rates. Performance will go up (although you’ll have some recoding to use this new table name and remove the date check). Your historical rate table will continue to grow and grow and grow at a rate of 6 currencies a day (for now). Your current exchange table will stay the same size. This gives the ability to do historical reporting/calculations when its necessary but makes the current transactions work faster. Chris

i don’t agreed that table lookup is excessive overhead
every time you issues a RPC to the database, thats 240K cpu-cycles (Xeon)
a clustered index seek is only 40K
then the rest of you query is probable several hundred K, so why bother?

]]>