How to Increase SQL Query Performance on where clauses searching concatenated values

Question

I have a query that runs extremely well (fast) when run, but as soon as I try to search by a concatenated value, the performance dives.

What are my options for keeping the performance high? I'm aware that the obvious solution is to not search by a concatenated string, but there will be situations that I cannot help but do so. How do I deal with those situations.

Example 1: Runs Fast

Select * From
(
    With Exmp1 AS 
    (
    Select ID, RCD From Table1 a where EFFDT = (Select Max(b.EFFDT) 
    FROM Table1 b 
    Where a.ID = b.ID and a.RCD = b.RCD) and status = 'A'
    )

    Select USERNAME, RCD
    From MainTable MT Inner Join Exmp1 E1 ON MT.ID = E1.ID 

)
Where USERNAME = 'test1'

Example 2: Runs Slow

Select * From
(
    With Exmp1 AS 
    (
    Select ID, RCD From Table1 a where EFFDT = (Select Max(b.EFFDT) 
    FROM Table1 b 
    Where a.ID = b.ID and a.RCD = b.RCD) and status = 'A'
    )

    Select USERNAME || '@domain.com', RCD
    From MainTable MT Inner Join Exmp1 E1 ON MT.ID = E1.ID 

)
Where USERNAME = 'test1@domain.com'

If you are wondering why I have the whole query wrapped in a select, the application that consumes this query has an expectation that the query begins with 'Select'. I already tried re-writing it without the "with" clause but I get the same results.

Select USERNAME || '@domain.com', RCD
From MainTable MT Inner Join 
(Select ID, RCD 
 From Table1 a 
 Where EFFDT = (Select Max(b.EFFDT) FROM Table1 b Where a.ID = b.ID and a.RCD = b.RCD) and status = 'A'
) E1 ON MT.ID = E1.ID 
Where USERNAME|| '@domain.com' = 'test1@domain.com'

============ Edited for Additional Info:

Table1 has four columns: ID, RCD, EFFDT, STATUS. It's important that I only get the most recent records (EFFDT) and records that have status value of 'A'.

Main Table has two columns: ID, USERNAME. I am unable to change this table or create additional tables, database views. I have to use this data as it exists today and only interact in the form of SQL Queries.

The desired result is USERNAME@domain.com and RCD for a given USERNAME@domain.com.

Be mindful that this is just a specific example, and general help on the subject is appreciated.

Answer 1

When you concatenate the string you are preventing the SQL optimizer to use the existing index on MainTable (USERNAME). That forces the engine to follow a different [slower] path; probably a HEAP [TABLE] SCAN. As simple as that.

If you really need to provide the full email address I would compute the concatenation in the last step and not before, essentially going back to your first option. For example:

Select USERNAME || '@domain.com', RCD From
(
    With Exmp1 AS 
    (
    Select ID, RCD From Table1 a where EFFDT = (Select Max(b.EFFDT) 
    FROM Table1 b 
    Where a.ID = b.ID and a.RCD = b.RCD) and status = 'A'
    )

    Select USERNAME, RCD
    From MainTable MT Inner Join Exmp1 E1 ON MT.ID = E1.ID 

)
Where USERNAME = 'test1'

EDIT:

Taking the idea one step further you can rephrase the whole query and find out which optimizations are easily visible once the query is simplified:

• The thing is the column MT.USERNAME is probably much more selective than a.STATUS, so you should filter by it first.
• Then, to make the correlated subquery fast, you probably want to use a "covering index" on it, so I suggest adding ix2 as shown below.

For example:

Select
  MT.USERNAME || '@domain.com', a.RCD 
From MainTable MT
join Table1 a on a.ID = MT.ID
where MT.USERNAME = 'test1'
  and a.status = 'A'
  and a.EFFDT = (
    Select Max(b.EFFDT) FROM Table1 b Where a.ID = b.ID and a.RCD = b.RCD
  )

Now, in order for this query to be real fast you'll need the following indexes. It seems you already have the first one:

create index ix1 on MainTable (USERNAME); -- You already have this one
create index ix2 on Table1 (ID, RCD, EFFDT);

SECOND EDIT: If you really want to search using the full username you can add an index on an expression. Take your "Example 2" and change the WHERE condition as shown below:

Select * From
(
    With Exmp1 AS 
    (
    Select ID, RCD From Table1 a where EFFDT = (Select Max(b.EFFDT) 
    FROM Table1 b 
    Where a.ID = b.ID and a.RCD = b.RCD) and status = 'A'
    )

    Select USERNAME || '@domain.com', RCD
    From MainTable MT Inner Join Exmp1 E1 ON MT.ID = E1.ID 

)
Where USERNAME || '@domain.com' = 'test1@domain.com' -- changed here

Then add the following index:

create index ix3 on MainTable (USERNAME || '@domain.com');

This should make the query fast, since the filtering preficate will be an exact match with the index.