Optimizing Query - Order by inner joined value

[gnjunge]

Hi, I have a two medium sized tables, one containing 2.000.000 cities, and one containing localized names for part of these cities in sofar 3 languages ( about 3.000.000 records) When users look for a city the following query is basically being used:

SELECT TOP 10 COALESCE(Cities_Localized.[Name], Cities.[Name])
FROM Cities
LEFT JOIN Cities_Localized ON Cities.CityID = Cities_Localized.CityID AND
Cities_Localized.Language = 'en-US'
WHERE Cities.[Name] LIKE 'T%' OR --where T is the first letter of some city
Cities_Localized.[Name] LIKE 'T%'
ORDER BY COALESCE(Cities_Localized.[Name], Cities.[Name])

This is of course an abstracted version, but it deals with the basic problem. This query takes a long time. Especially the sorting. Does anyone have a few tips on how to make this query faster? Thanks, Gidon

[Michael Potter]

COALESCE is built to handle numerous elements. You are only using two so ISNULL(Cities_Localized.[Name], Cities.[Name]) might be a bit faster (maybe not?). I assume you already have an index on [Name] in both tables to handle the LIKE comparisons. The next thing I would try is a temporary table so that the NULL testing is only done once. I am assuming you are using SQL server.

DECLARE @tmp TABLE
(
     CityName NVARCHAR(200)
)
  
INSERT INTO @tmp (CityName)
SELECT TOP 10 
    ISNULL(Cities_Localized.[Name], Cities.[Name])
FROM 
    Cities
LEFT JOIN 
    Cities_Localized 
    ON (Cities.CityID = Cities_Localized.CityID) AND 
       (Cities_Localized.Language  = 'en-US') 
WHERE 
    Cities.[Name] LIKE 'T%' OR 
    Cities_Localized.[Name] LIKE 'T%'
  
SELECT 
    CityName 
FROM
    @tmp
ORDER BY
    CityName

You can also try a UNION query and see if it is any faster.

SELECT 
    [Name] AS CityName
FROM
    Cities
WHERE 
    [Name] LIKE 'T%'
  
UNION 
  
SELECT 
    [Name]
FROM
    Cities_Localized 
WHERE
    Cities_Localized.Language  = 'en-US' AND
    Cities_Localized.[Name] LIKE 'T%'
  
ORDER BY
    CityName

If none of those resulted in a fast enough query, I would develop a table that contained all city names (normal and localized) with a Foreign Key back to the City table.

[gnjunge]

Hi Michael, Thanks for your answer, I tried all of your queries, and here are the results: - ISNULL vs COALESCE - no measurable difference - Temp table solution. Worked fast, but the problem is the temp table is filled with 10 records starting with a T, but they can be Tabasco, but also Tu.... The ORDER BY is perfromed on this 'random' choosen top 10 cities that start with a T. So it doesn't work. The order has to be performed on the main SELECT query anyway. - UNION solution - worked very very fast. Made me very happy, but misses a feature of the join, namely: when the city Paris is translated to Dutch as Parijs (note the extra J), both records will show up by with a search of LIKE 'Pari%', while I want that when a record is translated, only the translated record should show up (the join takes care of that in the original select). I hope you can help me with the pitfalls in the solutions, because they do have a clear speed advantage. Otherwise i will just have to go for the last solution. "develop a table that contained all city names (normal and localized) with a Foreign Key back to the City table"

[andyharman]

Try

--Get top 10 city names that do NOT have a localised version.
select top 10 C.CityId, C.Name
  into #tmp
  from Cities C
  left outer join Cities_Localization CL
    on CL.CityID = C.CityID
    and CL.Language = 'en-US'
  where C.Name like 'T%'
  and CL.CityID is null
  order by C.Name

--Add the top 10 localised city names.
insert into #tmp (CityID, Name)
  select top 10 CL.CityID, CL.Name
  from Cities_Localization CL
  where CL.Name like 'T%'
  and CL.Language = 'en-US'
  order by CL.Name

--Return top 10 cities (localised or not).
select top 10 #tmp.Name
  from #tmp
  order by Name

For the best performance, you probably need the following indexes in place: Table: Cities Non-clustered index on Name, CityID (to make name-search as fast as possible). Table: Cities_Localization Clustered index on CityID, Language (to make checking for localised versions as fast as possible). Non-clustered index on Name (to make searching for localised names as fast as possible). Hope this helps. Andy

[gnjunge]

Very nice, very fast. Just one more question, as i said in my initial post, the query was just an abstraction of the bigger whole. Now in the bigger whole, i don't select the top 10, but i do paging (using SQL 2005's ROW_NUMBER OVER (ORDER BY LocalizedCityName ASC) Is there a way - withouth filling the #tmp table with all the records that match 'T%' and then on that add the row number - to use your design and use paging?

[andyharman]

I hate to admit it but I'm having trouble thinking of a tidy way to solve that. Question: Did my suggested indexes have much of an effect on your original select statement? My next suggestion would be to maintain a denormalised table (i.e. using triggers) that coalesces the Cities and Cities_Locatization tables together. The performance would be really quick - but its not very subtle. There may possibly be a way of getting "indexed views" to solve this. I had a quick look, but they don't allow outer joins or union joins - so any solution would be a little bodgy. Sorry I couldn't be of more help. Regards Andy

[gnjunge]

I was affraid so. So I guess the solution is putting the Cities and Cities_Loc. together. Well the good thing is that new cities are not being added by users. We have once every while an update from "localizers/dataentry" and after checking we update the production tables. So no need to use triggers. It's a shame there is no subtle solution... Thanks for your help. Gidon

[andyharman]

Have you considered using a textbox to input part of the name then Ajax to dynamically pull matching names from the database?

[gnjunge]

That comment made me think. Because that's exactly the purpose of the stored procedure. But I designed the stored procedure in such a generic way that i can also use it for datasets where paging has to be used. (input of the sproc is : CityName, PageIndex, PageSize, TotalRecords OUTPUT) But actually it will mostly be used by the Ajax thing, so than i don't need paging (it always returns the top x records from page 1), and can use your fast solution. If page 2 is wanted, i use the slower solution i have right now. Thanks for that comment!!!