Hello.

I have some SQL statements that are working but I think there has to be a
more efficient way to accomplish what I'm trying to do.

I have two different columns in a table (SuperMatch) that need to find a
corresponding value from another table (ProviderSpecialty), using the values
from SuperMatch.

Here's the SELECT statement:
SELECT
-- Here's my first column
CASE
WHEN RTrim(PS1.FIC_Specialty_Code) <> '' THEN RTrim(PS1.FIC_Specialty_Code)
ELSE
    SM.Credentials1
END,
-- Here's my second column
CASE
WHEN RTrim(PS2.FIC_Specialty_Code) <> '' THEN RTrim(PS2.FIC_Specialty_Code)
ELSE
    SM.Credentials2
END
FROM SuperMatch As SM
LEFT OUTER JOIN ProviderSpecialty As PS1
    ON RTrim(SM.Network_Verbose_Specialties1) =
         Rtrim(PS1.IC_Specialty_Verbose)
LEFT OUTER JOIN ProviderSpecialty As PS2
    ON RTrim(SM.Network_Verbose_Specialties2) =
         Rtrim(PS2.IC_Specialty_Verbose)

TIA,
Rita

You're taking a performance hit by doing string comparisons and RTRIM each row.

Ideas to improve performance:

Idea 1:
Index SM.Network_Verbose_Specialties1
Index SM.Network_Verbose_Specialties2
Index IC_Specialty_Verbose

Idea 2:
Use int (integer) columns to join tables with rather than strings.  If
changing the tables is not an option, go to idea 3.

SuperMatch table
Network_Verbose_Specialties1RowId int with index to ProviderSpecialty
Network_Verbose_Specialties2RowId int with index to ProviderSpecialty

ProviderSpecialty table
IC_Specialty_VerboseRowId int, primary key, clustered

SELECT
CASE
WHEN SM.Network_Verbose_Specialties1RowId is null THEN SM.Credentials1
ELSE
       RTrim(PS1.FIC_Specialty_Code)
END,
-- Here's my second column
CASE
WHEN SM.Network_Verbose_Specialties2RowId is null THEN SM.Credentials2
  ELSE
        RTrim(PS2.FIC_Specialty_Code)
END
FROM SuperMatch As SM
LEFT OUTER JOIN ProviderSpecialty As PS1
    ON SM.Network_Verbose_Specialties1RowId = PS1.IC_Specialty_VerboseRowId
LEFT OUTER JOIN ProviderSpecialty As PS2
    ON SM.Network_Verbose_Specialties2RowId = PS2.IC_Specialty_VerboseRowId

Idea 3:
Use varchar or nvarchar data types.
Run an update query to remove trailing spaces in both tables.
Remove the RTRIM functions in your join.
Of course, your application must not submit spaces to SQL Server.  Or, have
your application execute stored procedures for all inserts and updates to
ensure all data is trimmed before being stored in the database.

Hope that helps,
Joe

Show quoteHide quote

Best Data Type for a Tracking ID?
Any harm in adding a primary key...
Collation difference between SQL 6.5 server and SQL 2000 server
How to enfore a primary key range ???
Creating Clustred/Index Keys