We have a table that contains address information. It has a primary key, called building code, and then the country, state, city, street and building name. I'm using this table to populate a series of drop down menus dynamically when a user selects the parent drop down. So if they select country, states are populated, selects state, cities are populated, etc. When a user selects a country, the state, city, and street queries run pretty quickly but it takes almost 15 seconds to load building name. I'm trying to figure out how to properly index this table to optimize the query. We're planning on breaking the table out into separate entities but I'm just trying to see if there's a quick fix for the time being.
Here's the query I'm using
DECLARE @searchType VARCHAR(15)
DECLARE @country VARCHAR(100)
DECLARE @state VARCHAR(100)
DECLARE @city VARCHAR(100)
DECLARE @street VARCHAR(100)
SET @searchType = 'building'
SET @Country = 'UNITED STATES OF AMERICA'
SET @State = 'All'
SET @city = 'All'
SET @street = 'All'
t_ResourceLocationAssignment AS RLA INNER JOIN
t_CorporateDirectory_BuildingCode AS BC ON LEFT(RLA.mailCode,7) = BC.BuildingCode
BC.Country = (CASE WHEN @Country IN('All','') THEN BC.Country ELSE @Country END)
BC.State = (CASE WHEN @state IN('All','') THEN BC.State ELSE @State END)
BC.City = (CASE WHEN @city IN('All','') THEN BC.City ELSE @City END)
BC.StreetAddress = (CASE WHEN @street IN('All','') THEN BC.StreetAddress ELSE @street END)
BC.BuildingName <> ''