stretch73
asked on
SQL Server 2005 Index
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'
SELECT DISTINCT
BC.BuildingCode,
BC.BuildingName
FROM
t_ResourceLocationAssignme nt AS RLA INNER JOIN
t_CorporateDirectory_Build ingCode AS BC ON LEFT(RLA.mailCode,7) = BC.BuildingCode
WHERE
BC.Country = (CASE WHEN @Country IN('All','') THEN BC.Country ELSE @Country END)
AND
BC.State = (CASE WHEN @state IN('All','') THEN BC.State ELSE @State END)
AND
BC.City = (CASE WHEN @city IN('All','') THEN BC.City ELSE @City END)
AND
BC.StreetAddress = (CASE WHEN @street IN('All','') THEN BC.StreetAddress ELSE @street END)
AND
BC.BuildingName <> ''
ORDER BY
BC.BuildingName
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'
SELECT DISTINCT
BC.BuildingCode,
BC.BuildingName
FROM
t_ResourceLocationAssignme
t_CorporateDirectory_Build
WHERE
BC.Country = (CASE WHEN @Country IN('All','') THEN BC.Country ELSE @Country END)
AND
BC.State = (CASE WHEN @state IN('All','') THEN BC.State ELSE @State END)
AND
BC.City = (CASE WHEN @city IN('All','') THEN BC.City ELSE @City END)
AND
BC.StreetAddress = (CASE WHEN @street IN('All','') THEN BC.StreetAddress ELSE @street END)
AND
BC.BuildingName <> ''
ORDER BY
BC.BuildingName
ASKER
I have an index on building name. I'd really like to bring back records that don't have a ' ' value, is there a more efficient way of doing that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> I'd really like to bring back records that don't have a ' ' value, is there a more efficient way of doing that?
Nope, this search condition ideally would go for a table scan.
>> LEFT(RLA.mailCode,7) = BC.BuildingCode
Above condition would also force a table scan.
And both scenarios can be handled only by performing change in database design.
Nope, this search condition ideally would go for a table scan.
>> LEFT(RLA.mailCode,7) = BC.BuildingCode
Above condition would also force a table scan.
And both scenarios can be handled only by performing change in database design.
ASKER
I had to add a LEN() to this:
AND BC.BuildingName > ''
But this answer got me a significant performance improvement.
AND BC.BuildingName > ''
But this answer got me a significant performance improvement.
AND BC.BuildingName <> ''
ORDER BY BC.BuildingName
To increase performance of ORDER BY clause, creating an index on BuildingName would improve the performance a little bit.
But checking BuildingName <> '' would force a table scan thereby reducing the performance and inefficient usage of indexes..