SQL using a different approach then CASE statements

Question

SELECT
    CASE 
        WHEN symbol - 'D' THEN 'Domain'
        WHEN symbol LIKE 'A%' THEN 'Alpha'
        WHEN symbol = 'C' THEN 'Charlie'
    END
FROM
    table

I would like to avoid case statements, since its severely impacting query runtime. I have about 8 million records in the database, and evaluating each case statement is wreaking havoc on server memory.

Any ideas?

Answer 1

You could create another table MAP (table name, column, value, match) and record all such cases. Initially check the table for a match (query MAP for symbol in you example) and if no match is found execute the CASE statement and insert the value in the MAP table.

This may not be a real performance improvement but you could export MAP to a data structure and use it in your SQL scripts. Or you could update the internal map data structure from MAP table. The internal map may grow as your data but I think this would improve the performance.